Databases
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!