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
0 Comments.