| urgent [message #372890] |
Thu, 15 March 2001 13:54  |
Anna
Messages: 30
Registered: March 2001
|
Member |
|
|
|
can someone tell me how to generate a SQL script (non-dynamic) that will load all the data from a specific schema into a file and change all the datatypes of all the columns in a schema to varchar2 datatype. What script do I need for that?
|
|
|
|
| Re: urgent [message #372892 is a reply to message #372890] |
Thu, 15 March 2001 15:59  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
You need to handle data conversions manually if you pull the data out into insert statements. Most datatypes (number, date, char, varchar2) can be upgraded to the new varchar2 while still in the DB but not while in the tables (using alter table t1 modify(col1 varchar2(100));).
Try creating the new tables with the changed datatypes, and then "insert into table t1_new (select * from t1)"
If you have a time component of date fields though, try changing the NLS_DATE_FORMAT to set the format you want. (alter session set...).
Are you sure you want to do this?? you should have good reasons.
If you use a tool like MS Visual Studio 6(enterprise edition) you can easily change the datatype through the ERD tool and it will do all the conversion for you, restore constraints etc.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
