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:
You can also create a SQL script to turn on and off the spooler
and format the output the way you want:
The contents of get_data.sql are:
SQL> spool outfile.txt
SQL> select * from oracle_2_table;
SQL> spool off
SQL> get_data.sql
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;
Post a comment