How To Export Data From SQL Plus to CSV

dbtut

5 years ago

With Oracle 12.2.0.1, SQL * Plus is capable of displaying the result of a query as CSV.

Its use is as follows.

SQL> SET MARK[UP] CSV {ON|OFF} DELIMI[TER] character QUOTE {ON|OFF}

1	SQL> SET MARK[UP] CSV {ON\|OFF} DELIMI[TER] character QUOTE {ON\|OFF}

Options

DELIMI[TER] : The character used to separate column values. Only single characters are allowed. The default value is “,” (comma).

QUOTE : If this is ON, the column values that are text are shown in “” (double quotation marks). It can take ON or OFF values. The default value is OFF.

Generate CSV output in double quotes

SQL> SET MARKUP CSV ON QUOTE ON
SQL> SELECT * FROM PERSON;

"ID","NAME","SURNAME"
1,"AHMET","DURUOZ"
2,"ALI","VELI"
3,"VELI","ALI"

SQL> SET MARKUP CSV ON QUOTE ON

SQL> SELECT * FROM PERSON;

"ID","NAME","SURNAME"

1,"AHMET","DURUOZ"

2,"ALI","VELI"

3,"VELI","ALI"

Generate CSV output without double quotes

SQL> SET MARKUP CSV ON QUOTE OFF
SQL> SELECT * FROM PERSON;

ID,NAME,SURNAME
1,AHMET,DURUOZ
2,ALI,VELI
3,VELI,ALI

SQL> SET MARKUP CSV ON QUOTE OFF

SQL> SELECT * FROM PERSON;

ID,NAME,SURNAME

1,AHMET,DURUOZ

2,ALI,VELI

3,VELI,ALI

Generate CSV output using “| (pipe)” as delimiter

SQL> SET MARKUP CSV ON DELIMITER |
SQL> SELECT * FROM PERSON;

ID|NAME|SURNAME
1|AHMET|DURUOZ
2|ALI|VELI
3|VELI|ALI

SQL> SET MARKUP CSV ON DELIMITER |

SQL> SELECT * FROM PERSON;

ID|NAME|SURNAME

1|AHMET|DURUOZ

2|ALI|VELI

3|VELI|ALI

By using the -M parameter from the command line as follows, the output can be created in csv format by running the script.

[oracle@orcldb ~]$ sqlplus -S -M "csv on" / as sysdba @/home/oracle/person.sql | sed '/^$/d'
"ID","NAME","SURNAME"
1,"AHMET","DURUOZ"
2,"ALI","VELI"
3,"VELI","ALI"

[oracle@orcldb ~]$ sqlplus -S -M "csv on" / as sysdba @/home/oracle/person.sql | sed '/^$/d'

"ID","NAME","SURNAME"

1,"AHMET","DURUOZ"

2,"ALI","VELI"

3,"VELI","ALI"

With the command “sed ‘/^$/d'”, we ensure that the space that appears at the beginning and end of the output is not displayed. Otherwise, it will be displayed with spaces as follows.

[oracle@orcldb ~]$ sqlplus -S -M "csv on" / as sysdba @/home/oracle/person.sql

"ID","NAME","SURNAME"
1,"AHMET","DURUOZ"
2,"ALI","VELI"
3,"VELI","ALI"

[oracle@orcldb ~]$

[oracle@orcldb ~]$ sqlplus -S -M "csv on" / as sysdba @/home/oracle/person.sql

"ID","NAME","SURNAME"

1,"AHMET","DURUOZ"

2,"ALI","VELI"

3,"VELI","ALI"

[oracle@orcldb ~]$