DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: convert schema to flatfiles

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    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..

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    see [url]http://govt.oracle.com/~tkyte/flat/index.html[/url]
    Jeff Hunter

  3. #3
    Join Date
    Nov 2000
    Posts
    344
    Check out EZSQL. [url]http://www.ezsql.net.[/url]

    It can do this. Even if you don't want to buy, you have over
    a month for a trial period - you can do this before the trial runs out.

    -John

  4. #4
    Join Date
    Nov 2000
    Posts
    245
    check this:

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5636[/url]
    [url]http://www.dbasupport.com/forums/showthread.php?threadid=7875[/url]

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    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..

  6. #6
    Join Date
    Nov 2000
    Posts
    212
    export tables, do schema changes, import tables into tmp tables(i.e. into tmp schema), process those tables with convert routines. More, genereate convert routines or use some comm. tools to do that.

  7. #7
    Join Date
    Mar 2001
    Posts
    4
    Hi

    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

    Regards
    Alkesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width