CODEWITHSUNDEEP
oracle-databasecsvexport-to-csvsqlplus
idazuwaika
idazuwaika

Reputation: 3019

SQLPlus saving to CSV File without Formatting

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.

Upvotes: 0

Views: 8693

Answers (1)

Littlefoot
Littlefoot

Reputation: 142715

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

Upvotes: 0

Related Questions