Hi: I have this requirement to convert the entire schema into flatfiles. Schema consists of 80 tables and the table that has the maximum number of columns is about 40. What is the best way to do this? I don't want to write 80 scripts to do the same. Data in tables ranges from 500 rows to 2.5 million rows. Thanks..
Thanks for all replies.. BUt I am noty trying to convert 1 table.. Say all 80 tables in the schema into 80 individual flatfiles. This is not 1 time deal to simply use TOAD or EZSQL or whatever but will be used on and on.. I can use the DBMS_util procedure to convert them into a flatfile and store the data, but if i convert a table say emp.. i need to declare all the columns of the emp table as variables. But if i am doing for 80 tables i cannot declare every column possible as a variable right?? My dbms_util proc will be taking the schemaname as input and does its work till all the tables in the schema are done.. But variables...Thats the problem..
The reason simply does not end with keeping them as flatfiles but when the schema has new structures for the table, i will be using sql loader to convert them into oracle tables.. (2nd part is taken care of)
Any clues or direction? Thanks..
U can write pl/sql procedurw which willtake a table name and get all the column definition from dba_tab_columns dictionary view and then write a select statement to dump the information into a flat file.
U can put this script in a loop which will take each and every table u need to dump.
I hope it helps