Oracle 11g - Spool file - enforcing not quoted columns

Question

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

Answer 1

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

• kevinskio: OP's version is 11. That's a question in itself...
• Balazs Papp: @kevinsky OP can download and install a 12.2 client with SQL*Plus, just as SQLcl was downloaded and installed. The version of the database is irrelevant, as long as the client is compatible with it.