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.