CODEWITHSUNDEEP
oracle-databaseexport-to-csv
mak101
mak101

Reputation: 147

How do I export oracle query result without enclosure

I have oracle 12c and trying to export query result to csv or text file but I dont want any enclosure of my data. I have tried SET SQLFORMAT csv which creates csv file but data comes in double quotes then I tried SET SQLFORMAT delimited | but that also comes with double quoted. I also tried SET MARKUP csv on delimeter | quote off it also gave me same result. I dont think MARKUP command works on 12c but it did not give me error. Here is my script:

SET SQLFORMAT  delimited | ;
spool 'C:\Temp\MyResults.csv'; 

select 1 AS Col1, 'Data Line 1' AS Col2 from dual UNION select 2 AS Col1, 'Data Line 2' AS Col2 from dual;

spool off;

This gives me result:

"COL1"|"COL2"
1|"Data Line 1"
2|"Data Line 2"

But I want without double quotes on string data. 

COL1|COL2
1|Data Line 1
2|Data Line 2

I would appreciate if someone can give me any poption.

Thanks.

Upvotes: 1

Views: 1518

Answers (1)

pifor
pifor

Reputation: 7882

I have the following working with Oracle 19 (client and server on Linux):

SQL> set markup CSV on quote off
SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)

SQL> select * from t where rownum=1;

OBJECT_ID,OBJECT_NAME
16,TS$


SQL> set markup csv on quote on
SQL> select * from t where rownum=1;

"OBJECT_ID","OBJECT_NAME"
16,"TS$"

SQL>

Upvotes: 2

Related Questions