​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
A sample SQLPLUS report that creates a CSV output file


SQLPLUS can be a great tool when a you need to produce a quick report from your Oracle database. As an example suppose you want to produce a comma separated values (CSV) output file. At first glance you might start by prepending/appending the comma character to the database fields in your select statement such as:-

SELECT ','||field1||',' ,'                 ,'||field2 etc ...

However as the example script below shows this can be done more easily using some of the built-in SQLPLUS commands. It also demonstrates some other commands that you might find useful and you may like to use this as a skeleton script upon which to base other reports.

/*
* create a CSV report on employee details
*
*/
set pagesize 0    /* Use this to suppress page headers, titles and all formatting */
set verify off         /* Don't list the SQL text before/after any variable substitution */
set lines 700       /* Set line size, make this as big as you like because the next line */
set trimspool on    /* deletes any blank spaces at the end of each spooled line */
set feedback off    /* Don't display number of lines returned by the query */
set termout  off      /* Don't display any SELECT output to your screen */
set colsep ','          /* Separate each column by a comma character (CSV output) */
column emp_date format A8 /* Make sure the date field is the width you want itto be otherwise  Oracle will make this column very wide when output */

column dcol new_value mydate noprint
select 'c:\user\tom\myreport_' || to_char(sysdate,'yyyymmdd') || '.csv' dcol from dual;
spool &mydate

/* Above lines show how to output to a file with the date in it*/
select employee_id,
employee_name,
employee_address1,
employee_address2,
employee_address3,
employee_address4,
to_char(emp_startdate,'YYYYMMDD') emp_date,
emp_sal
from employee
/
spool off
exit