Wednesday, June 24, 2009

Convert all column data type from VARCHAR2 to NVARCHAR2 and vice versa in Oracle 11g

I found a problem about the encoding and some data size issues. So I need to convert all column in all table in my database from VARCHAR2 to NVARCHAR2 with the same size. Definitely I can convert the data type column by column manually but it takes time and not a smart way. So this is the way.

I logged in as SYS and run this sql statement.

select 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MODIFY('||COLUMN_NAME||' NVARCHAR2('||DATA_LENGTH||'));' AS GEN_SQL_STATEMENTfrom   SYS.DBA_tab_columnswhere  owner = 'ownername' and DATA_TYPE = 'VARCHAR2';

The result is the rows of ALTER statement filled with the table name, column name and new data type.
Then I copied all rows and concatenate them with some editor. and then run them as a sql script.

If you want to convert from NVARCHAR2 to VARCHAR2, just switch 'VARCHAR2' and NVARCHAR2' in above sql statement. You can modify above script as well to suit your propose.

Finally, you cannot convert the column which is not empty otherwise the error will occur.