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;

run the sql file on prompt :

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>sqlplus -s user/password @c:\tes.sql

We first set any settings we need at the start for the script these are as follows:

set echo off [Won’t display any SQL commands that are run]
set verify off [If turned on, it prints out each defined variable twice]
set termout on [Supresses the output of SQL commands but not the commands themselves]
set heading off [Sets whether column headings are outputted or not]
set pages 50000 [50000 lines without a page break, basically sets the height of the page]
set feedback off [If on it will feedback to user e.g. table created, row deleted etc..]
set newpage none [Removes any leading/ blank lines at the start of a page]
set linesize 200 [Sets the width of a line]

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s