A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
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;
/* Above lines show how to output to a file with the date in it*/