Oracle – export select statement spooling

15 Jan

Example:

set heading off
  set arraysize 1
  set newpage 0
  set pages 0
  set feedback off
  set echo off
  set verify off

spool 'c:\temp\NameList.csv'
/
select FIRSTNAME','LASTNAME' from nametable
spool off

 

More about different set parameters:

SET TERM      OFF 
-- TERM = ON will display on terminal screen (OFF = show in LOG only)
SET ECHO      ON 
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.
SET TRIMOUT   ON 
-- TRIMOUT = ON will remove trailing spaces from output
SET TRIMSPOOL ON 
-- TRIMSPOOL = ON will remove trailing spaces from spooled output
SET HEADING   OFF 
-- HEADING = OFF will hide column headings
SET FEEDBACK  OFF 
-- FEEDBACK = ON will count rows returned
SET PAUSE     OFF 
-- PAUSE = ON .. press return at end of each page
SET PAGESIZE  0   
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)
SET LINESIZE  80 
-- LINESIZE = width of page (80 is typical)
SET VERIFY    OFF 
-- VERIFY = ON will show before and after substitution variables
-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM GLOBAL_NAME;
SPOOL OFF

 

Read more:

http://stackoverflow.com/questions/15907015/how-to-properly-export-results-of-select-statement-into-csv-file-using-spool-in

http://ora.u440.com/sqlplus/set.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: