DBA Hub

📋Steps in this guide1/2

spool CSV in Oracle 12.2 DBACLASS

spool CSV in Oracle 12.2, 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 opti

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

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.
2

Section 2

Now run it with the new CSV Format. The following example illustrates how to extract all records from the Employee table of the database, with text strings unquoted and with different delimiter. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
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.

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.

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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!