Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: Help a newbie - dump a table in to a file

    Hi there

    Oracle is still a blackbox for me and totally else then MySQL that i have been working with until now.

    I'm trying to dump the contents of a table in to a plain text file over the sqlplus command line interface. Can somebody maybe tell me the commands to do that or give me some hints?

    Thanks for your kind help.

    Chris Armstrong

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If your using sqlplus then do the following

    set pages 9999
    set linesize 255
    spool testfile.csv
    select col1 || ',' || col2 || ',' || col3 ...
    from table;
    spool off

    replace col1 etc with your column names

    The || does string concantenation and ',' sticks commas between the fields. If you find lots of spaces stick trim(col1) for all the columns

    If you dont need it in csv form the just do select col1, col2 etc and it will write it to the file as it appears on screen.

    Alan

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Just posting additional info concerning Chris' method. This is what the Oracle documentation says about "Creating a flat file". Pay attention to the SET COLSEP instruction, which allows you to define a separator.

    <<<ORACLE DOCUMENTATION>>
    Creating a Flat File
    ============
    When moving data between different software products, it is sometimes necessary to use a "flat" file (an operating system file with no escape characters, headings, or extra characters embedded). For example, if you do not have Oracle Net, you need to create a flat file for use with SQL*Loader when moving data from Oracle8 to Oracle9i.

    To create a flat file with SQL*Plus, you first must enter the following SET commands:


    SET NEWPAGE 0
    SET SPACE 0
    SET LINESIZE 80
    SET PAGESIZE 0
    SET ECHO OFF
    SET FEEDBACK OFF
    SET VERIFY OFF
    SET HEADING OFF
    SET MARKUP HTML OFF SPOOL OFF

    After entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.

    The SET COLSEP command may be useful to delineate the columns. For more information, see the SET command.

    Sending Results to a File
    ================
    To store the results of a query in a file--and still display them on the screen--enter the SPOOL command in the following form:

    SPOOL file_name

    SQL*Plus stores all information displayed on the screen after you enter the SPOOL command in the file you specify.
    <<<END ORACLE DOCUMENTATION>>>

Posting Permissions

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