1

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

2

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
  • OP's version is 11. That's a question in itself... – kevinsky Apr 30 '19 at 16:57
  • @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. – Balazs Papp Apr 30 '19 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.