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.
Thanks a lot for explanation it really helped..... just need to add this...you can change column data type in ORACLE 10gSQL> create table t(c varchar2(10)) 2 / Table created. SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- C VARCHAR2(10) SQL> insert into t values('karthick') 2 / 1 row created. SQL> commit 2 / Commit complete. SQL> alter table t modify c nvarchar2(10) 2 / Table altered. SQL> desc t Name Null? Type ----------------------------------------- -------- ---------------------------- C NVARCHAR2(10) SQL> select * from t 2 / C----------karthickSOURCE :http://forums.oracle.com/forums/thread.jspa?threadID=849987&tstart=0
ReplyDeleteThank you for your comment. :D
ReplyDelete