Now we can spool spool CSV or JSON from Oracle Database. Prior to 12.2 , we can spool as text or html, but from 12.2 onward, the SET MARKUP command now has a CSV option to output data in CSV format.

Syntax

CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}]

Traditionally you wiill get formatted output (without CSV)  as below.

SQL> select EMPLOYEE_ID, first_name from hr.employees where rownum < 10;

EMPLOYEE_ID FIRST_NAME
----------- --------------------
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
105 David
106 Valli
107 Diana
108 Nancy

9 rows selected.

Now run it with the new CSV Format.

SQL> SET MARKUP CSV ON
SQL> select EMPLOYEE_ID, first_name from hr.employees where rownum < 10;

"EMPLOYEE_ID","FIRST_NAME"
100,"Steven"
101,"Neena"
102,"Lex"
103,"Alexander"
104,"Bruce"
105,"David"
106,"Valli"
107,"Diana"
108,"Nancy"

9 rows selected.

The following example illustrates how to extract all records from the Employee table of the database, with text strings unquoted and with different delimiter.

SQL> SET MARKUP CSV ON QUOTE OFF
SQL> SET MARKUP CSV ON DELIMITER |
SQL> select EMPLOYEE_ID, first_name from hr.employees where rownum < 10;

EMPLOYEE_ID|FIRST_NAME
100|Steven
101|Neena
102|Lex
103|Alexander
104|Bruce
105|David
106|Valli
107|Diana
108|Nancy

9 rows selected.

So Now in 12.2 you can spool as csv and open in excel sheet. You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.