Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    INDIA
    Posts
    3

    Question Unanswered: Flat File creation

    Hi Everybudy,

    Please suggest me the exact sql to copy a Table Data into a Flat file??
    Table Name : ABC
    (Table is having many fileds with different data types)

    I want to save all the records of the table in a Flat File.
    Is it possible? If yes then how?

    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Flat File creation

    SQL>spool c:\temp_data.txt
    SQL>select * from <table_name>;
    SQL>set spool off
    If you try to open the file before setting spooling off, the file size will be zero. You can get data only after spooling is off.





    Originally posted by duaravi
    Hi Everybudy,

    Please suggest me the exact sql to copy a Table Data into a Flat file??
    Table Name : ABC
    (Table is having many fileds with different data types)

    I want to save all the records of the table in a Flat File.
    Is it possible? If yes then how?

    Thanks
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Location
    INDIA
    Posts
    3

    Re: Flat File creation

    Originally posted by cmasharma
    SQL>spool c:\temp_data.txt
    SQL>select * from <table_name>;
    SQL>set spool off
    If you try to open the file before setting spooling off, the file size will be zero. You can get data only after spooling is off.

    ---Ravi Dua
    Thanks for giving the suggestion, but the above sqls have following erros regarding my solution

    1. It is not able to spool large data in the text file.
    Becuase my tables also have CLOB data fields and table rows is around 1 Lac Records

    2. It is also spooling the statement itself and also the column names, which i don't want to include in the flat file.

    I want to create a flat file with various records and with special delimiters (default delimiters) between columns and rows

    Thanks

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Flat File creation

    For extracting date from clob type, refer this http://otn.oracle.com/oramag/code/tips2003/062203.html
    hope this helps.

    as regards, spool output, it can be formatted as you desire by making some settings before you start spool:

    set colsep *
    set heading off
    set pages 0
    set echo off
    set serveroutput off
    set linesize 500

    (500 to be replaced by max record length you have)






    Originally posted by duaravi
    ---Ravi Dua
    Thanks for giving the suggestion, but the above sqls have following erros regarding my solution

    1. It is not able to spool large data in the text file.
    Becuase my tables also have CLOB data fields and table rows is around 1 Lac Records

    2. It is also spooling the statement itself and also the column names, which i don't want to include in the flat file.

    I want to create a flat file with various records and with special delimiters (default delimiters) between columns and rows

    Thanks
    Oracle can do wonders !

Posting Permissions

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