CSV output from Oracle

Prior to 12c R2 , producing CSV ouptut from a table required bit of work . 
With 12c R2 , the process is quick and easy. The good old SQLPlus has got the long awaited option . 

With the SET option  , we should be spooled to a file quicker. 

Here is an example. 

SQL> select * from emp;

ENAME                        ID
-------------------- ----------
Zahir                        10
Hameed                       20
Farook                       30
Basheer                      30


SQL> set markup csv on

SQL> select * from emp;

"ENAME","ID"
"Zahir",10
"Hameed",20
"Farook",30
"Basheer",30


By default , the delimiter for the character values will be enclosed in quotes. 
We can turn it off by the quote off. 

SQL> set markup csv on quote off
SQL> select * from emp;

ENAME,ID
Zahir,10
Hameed,20
Farook,30
Basheer,30



Sometimes , it is beneficial to a different delimiter. 
In the following example , we use '|' as the delimiter . 

SQL> set markup csv on delim '|' quote on
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


In this way , the CSV ouput from oracle tables/views is a loooooot easier. 

Comments welcome. 


Update : 
To make the example complete . We need to use spool on/off to produce a file in our directory.  In the example below , I have spool the results to a file emp.csv . We can few other options to not to display ( supress ) the header , query . To make it simpler , I have just simply spooled the file . 

Thanks very much  to my good friend , Soma for pointing out this .  


SQL> spool  emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30


SQL> spool off
SQL> host dir
 Volume in drive D has no label.
 Volume Serial Number is AXCA-3871

 Directory of D:\test

01/17/2018  03:26 PM              .
01/17/2018  03:26 PM              ..
01/17/2018  03:27 PM               123 emp.csv
               1 File(s)            123 bytes
               2 Dir(s)  710,110,654,464 bytes free

SQL> ho type emp.csv
SQL> select * from emp;

"ENAME"|"ID"
"Zahir"|10
"Hameed"|20
"Farook"|30
"Basheer"|30

Auto restart of PDB in multi tenant ( CDB) database

Most of the time , when we start/restart  the container database ( CDB) , we would like one ot more pluggable database ( PDBs) to start with the CDB.

In 12c release 1 , the option was to use the trigger (AFTER STARTUP ON DATABASE )  to open the pdbs .  In 12c release 2 , the other option was to save the current state  , so that upon restart of the CDB , the PDB initializes to the saved state ( in our example , READ_WRITE). 


Here is an example. 

As it can be seen below , the PDB is not in 'OPEN' state once the CDB is restarted. 


SQL> col name format a20

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0335E+10 bytes
Fixed Size                 12468584 bytes
Variable Size            2751467160 bytes
Database Buffers         7549747200 bytes
Redo Buffers               21082112 bytes
Database mounted.
Database opened.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              MOUNTED


Now , let us open the PDB , confirm its status and save the state. 
The view 'dba_pdb_saved_states' shows the saved state of all PDBs.

SQL> alter pluggable database PDB_SIS open ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY
PDB_SIS              READ WRITE

SQL> alter pluggable database PDB_SIS save state ;

Pluggable database altered.

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE
-------------------- ----------
PDB$SEED             READ ONLY

PDB_SIS              READ WRITE

SQL> col con_name format a20
SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------

PDB_SIS               OPEN

Now , let us bounce the CDB and look at the status of the PDB. Now , the PDBs  re-initalized to the saved state. 

SQL>  shutdown abort;

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1.0335E+10 bytes

Fixed Size                 12468584 bytes

Variable Size            2751467160 bytes

Database Buffers         7549747200 bytes

Redo Buffers               21082112 bytes

Database mounted.

Database opened.

SQL> select con_name , state from dba_pdb_saved_states;

CON_NAME             STATE

-------------------- --------------

PDB_SIS               OPEN

SQL> select name , open_mode  from v$pdbs;

NAME                 OPEN_MODE

-------------------- ----------

PDB$SEED             READ ONLY

PDB_SIS               READ WRITE

Thanks !