The Java Developers Almanac 1.4


Order this book from Amazon.

   
Home > List of Packages > java.sql  [73 examples] > Importing and Exporting  [4 examples]

e293. Exporting an Oracle Table to a Flat File

This example saves the output from a SELECT statement to a flat file named outfile.txt.

To save to a file, turn on the spooler by typing 'spool outfile.txt' at the SQL prompt, execute the SELECT statement, then turn the spooler off by typing 'spool off' at the SQL prompt:

    SQL> spool outfile.txt
    SQL> select * from oracle_2_table;
    SQL> spool off
You can also create a SQL script to turn on and off the spooler and format the output the way you want:
    SQL> get_data.sql
The contents of get_data.sql are:
    set term off;      // Suppress the display so that you can spool output without seeing the output on the screen.
    set heading off;   // Turn off heading
    set linesize 400;  // Set the number of characters per line to 400.  Adjust this number to 0 or something for your real data.
    set feedback off;  // Turn off feedback
    spool outfile.txt; // Spool the output to a file named outfile.txt
    select col1 ||','|| col2
    from oracle_2_table;    // Select each column from table oracle_2_table, delimiting by a comma
    spool off;

 Related Examples
e292. Loading a Flat File to a MySQL Table
e294. Loading a Flat File to an Oracle Table
e295. Exporting a MySQL Table to a Flat File

See also: Batching    Connections    Database Meta Data    Deleting Data    Drivers    Inserting and Updating Data    Oracle OBJECTs    Oracle VARRAYs    Procedures and Functions    Retrieving Data    Scrollable Result Sets    Tables    Updatable Result Sets   


© 2002 Addison-Wesley.