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

[jane zhang]

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

[Andreas D. Skjervold]

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

[jane zhang]

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]

Aaahhhh

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

Andreas