Monday, August 25, 2008

Spool data from sql*plus to a .csv file

We need to login the sql*plus, create an empty 'test.xls' file in the 'c:' directory and then type the below statements in sql*plus.

SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 32000
SET PAGESIZE 0
SET TERMOUT OFF
SET TRIMSPOOL ON

spool c:\test.csv
PROMPT employeename,emp_id
select employeename||','||emp_id
from employees;

SPOOL OFF

SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET PAGESIZE 24
SET TERMOUT ON
SET TRIMSPOOL OFF

to spool data into .csv file