SQLPlus: Dump table data to csv file

Since this is quite a cumbersome operation (in contrast to for example with MySQL) I have put together a script to modify and use every time I need to dump some table data to a CSV file.
NOTE: This only works with text and simple number fields. More complex field types will just come out truncated or as gibberish.

SET echo off
SET feedback off
SET linesize 100
SET pagesize 0
SET sqlprompt ''
SET trimspool on
SPOOL filename.csv

SELECT field1 || ',' || field2 || ',' || field3 FROM tablename;

SPOOL off
exit

As you can see the trick is to turn off any extra output and to use the string concatenation operator|,|to insert commas (or any separator you want) between the fields.

The SPOOL command opens a file and dump all output made until the SPOOL off command

To run the script use:

sqlplus <user>/<password>@<SID> @scriptname.sql

Tested with SQL*Plus: Release 10.1.0.2.0

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

This site uses Akismet to reduce spam. Learn how your comment data is processed.