Oracle 11g – Spool file – enforcing not quoted columns

oracleoracle-11g

I am trying to spool large datasets (200k+ row) to csv format and need the columns not to be quoted. This is possible via the export settings of the SQL Developer GUI, but I am trying to do this via a SQL script. The resulting csv file is then read by another application. To demonstrate this I run the below script.

set ECHO      off
set FEEDBACK  off
set LINESIZE  60
set RECSEP    off
set TRIMSPOOL on
set VERIFY    off
set PAGESIZE  0
set SQLFORMAT csv
spool y:\aa.csv
SELECT * FROM PRODUCT_COMPONENT_VERSION;
spool off

This gives the ouput

"PRODUCT","VERSION","STATUS"
"NLSRTL ","11.2.0.4.0","Production"
"Oracle Database 11g Enterprise Edition ","11.2.0.4.0","64bit Production"
"PL/SQL ","11.2.0.4.0","Production"
"TNS for Linux: ","11.2.0.4.0","Production"

I need the output as below (NO QUOTED STRINGS)

PRODUCT,VERSION,STATUS
NLSRTL ,11.2.0.4.0,Production
Oracle Database 11g Enterprise Edition ,11.2.0.4.0,64bit Production
PL/SQL ,11.2.0.4.0,Production
TNS for Linux: ,11.2.0.4.0,Production

I would rather not post-process the file (powershell, perl, etc) after generation and before loading as it just adds unnecessary steps to a process.

How can I do a spool file where I can specify the left and right enclosures even setting them to NULL.

Regards,
Tim

Best Answer

set SQLFORMAT is a SQLcl option.

I am not aware of any option in SQLcl that removes the quotes.

SQL*Plus starting with version 12.2 supports csv output with the SET MARKUP option:

SQL> set markup csv on
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

"PRODUCT","VERSION","VERSION_FULL","STATUS"
"Oracle Database 19c Enterprise Edition ","19.0.0.0.0","19.3.0.0.0","Production"

You can simply disable quotes if needed:

SQL> set markup csv on quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT,VERSION,VERSION_FULL,STATUS
Oracle Database 19c Enterprise Edition ,19.0.0.0.0,19.3.0.0.0,Production

Setting a custom delimiter:

SQL> set markup csv on delimiter | quote off
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT|VERSION|VERSION_FULL|STATUS
Oracle Database 19c Enterprise Edition |19.0.0.0.0|19.3.0.0.0|Production
Related Question