How to dump data from database table to csv file using SQL Plus & Shell Script

Sample script(gen_csv.sh) to dump data to csv file using SQL Plus. Below are the steps.

• Set Database Connection details
• Use Shell script and SQL Plus to generate a .sql file. This gives a lot of flexibility to dynamically generate the query. In below example, I am passing table name as parameter. But this can be extended to dynamically generate where clause etc. Even the table name, columns names etc can be defined as metadata in a text file. And this can be used to dynamically create the SQL query. 
• Use SQL Plus 'start' command to run the above .sql file

Contents of gen_csv.sh

 #!/bin/bash

# set oracle home to db client oracle home
ORACLE_HOME=/opt/oracledb/base/rdbms/12.1.0.2/;export ORACLE_HOME

# set output folder path - query results and generated SQLs are dumped to this folder
# if my env, scripts are under /opt/script, and output folder is /opt/script/output/
# please make sure the last "/" is present
OUTPUT_PATH=/opt/script/output/; export OUTPUT_PATH

PATH=$ORACLE_HOME/bin:$PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH

CON_STR=scott/tiger@//hostname-here:1521/DB-service-name-here ; export CON_STR

#set table name . You can pass multiple parameters like this to the script to make it generic. Even the table name and columns name can be declared in a text file to make it more generic.

tableName=$1;

#echo "source $tableName";


# generate SQL file

# using "here" string to pass content to SQL Plus and the output of SQL Plus is redirected to {tableName}_dump.sql. The SQL Plus 'prompt' command echo each line to this .sql file

${ORACLE_HOME}/bin/sqlplus -s $CON_STR << EOF  >  ${OUTPUT_PATH}${tableName}_dump.sql
set    wrap off
set    feedback off
set    pagesize 0
set    verify off


prompt    select   '"TABLE_NAME"'   
prompt    ||','||  '"TABLESPACE_NAME"' as  optional_header_row from  dual ;

prompt /

prompt select TABLE_NAME||chr(44)||
prompt        TABLESPACE_NAME
prompt from $tableName;

prompt /

prompt    exit
exit
EOF

#run the SQL file generated in previous step
# SQL Plus's start command is used to run the sql file generated in previous step

${ORACLE_HOME}/bin/sqlplus -s $CON_STR << EOF
set    wrap off
set    feedback off
set    pagesize 0
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set    verify off
set trimspool on
set linesize 5000
start ${OUTPUT_PATH}${tableName}_dump.sql
exit
EOF

• Finally run the above script and redirect the output to csv file. The table name is passed as argument to shell script for illustration purpose.

$ bash gen_csv.sh "USER_TABLES" > output/user_tables_dump.csv