0

I am trying to query an Oracle database using SQLPlus and save the result as CSV file. I only want the data, not the formatting provided by SQLPlus options such as numwidth, linesize and pagesize etc.

I do not want to do extra processing using bash or awk to remove the presentation formatting, nor do I want to do complicated query like SELECT field1 || ',' || field2 etc

Currently I am trying to use the approach published here, but I am still not clear on how to avoid the presentation noise when saving to CSV.

https://chartio.com/resources/tutorials/how-to-write-to-a-csv-file-using-oracle-sql-plus/

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 2
set numwidth 5

spool books.csv

SELECT
  title,
  primary_author
FROM
  books;

spool off

This was StackOverflow questions and answers 8 years ago, but I am hoping better approaches are available now.

How do I spool to a CSV formatted file using SQLPLUS?

Thanks.

idazuwaika
  • 2,749
  • 7
  • 38
  • 46
  • 2
    SQL\*Plus hasn't changed much... which noise, exactly, do you mean?What do you see now and what do you want to see? You're not going to be able to remove trailing whitespace within fields sadly without the 'complicated query approach'. Is switching from SQL\*Plus to [the SQLcl command line tool](http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html) an option? That has the advanced formatting features from SQL Developer... – Alex Poole Jan 02 '18 at 17:43
  • go get SQLcl, set sqlformat csv, and you're done. Since those 8 years we built a new modern CLI inteface to the database. try it out – thatjeffsmith Jan 03 '18 at 00:37

1 Answers1

0

I'd say that it is SET ECHO OFF that's bothering you. Try it.

Also, perhaps you'd want to bookmark the following set of SET commands.

SET ECHO OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 9999
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
SET LONG 10000
Littlefoot
  • 131,892
  • 15
  • 35
  • 57