Oracle sqlplus query output into file


Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10gdatabase. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2″ and so on…

When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.

First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.

c:\edit tes.sql

set echo off
set termout off
set feedback off
set linesize 100
set pagesize 0
set sqlprompt ”
set trimspool on
set show off
spool c:\emp.csv

select * from mas_pasien;

spool off
exit;

Continue reading

Advertisements