e293. exporting an oracle table to a flat file.txt

来自「这里面包含了一百多个JAVA源文件」· 文本 代码 · 共 21 行

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;