Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

how to modify a NOT NULL column's data type?

jane zhang
Regular Advisor

how to modify a NOT NULL column's data type?


Hi,
I have a table, when described I got the table definition,
Name Null? Type
------------------------------- -------- ----
LOT_NO NOT NULL VARCHAR2(20)
BI_WRO_HOURS NOT NULL VARCHAR2(10)

and this talbe have data already, I need to change the data type of BI_WRO_HOURS from VARCHAR2(10) to NUMBER,
what is the best way to achieve this? or do I have to do exporting and importing of the table, or drop the table and recreate one and insert the data?

Thanks,
Jane

3 REPLIES
Andreas D. Skjervold
Honored Contributor

Re: how to modify a NOT NULL column's data type?

Hi

Since you're changing the column datatype, I think you'll not be able to do the export/import, as this requires identical tables.

Instead unload you table data using a script to create a *.csv file and a SQL*Loader controlfile.

Then truncate your table, and change the column.

Then use SQL*Loader to load the previous unloaded data. (Remember to thes the procedure befor performing on real data...)

Run the attached script like this:
sqlplus -s user/passwd @unload.sql table_name

Then load using:
sqlldr user/passwd control=table_name.ctl

Have fun!

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
jane zhang
Regular Advisor

Re: how to modify a NOT NULL column's data type?

Andreas,
Thanks for the import and export reminder, I just create a temporary table and copy the data over, truncate the original table and modify the column.
Regards,
Andreas D. Skjervold
Honored Contributor

Re: how to modify a NOT NULL column's data type?

Aaahhhh

I'm so In Love with the SQL*Loader, that the easy way skipped my mind altogether.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!