--==============================================================
http://www.quest-pipelines.com/newsletter-v6/0305_C.htm
--==============================================================

#!/bin/ksh 
#set -x
###############################################################################
# Program Name : table2csv  
# Program Type : Shell Script
# Author	   : Mike Selvaggio
#                Orsel Consulting Inc
# Description  : Create a text file, export ddl and sqldr ctl file for a table
# Usage        : generatecsv <Oracle SID> <Table Owner> <Table Name> <Output Dir>
#                Note: Output dir will be where all log and data files will go
# Exit Status  : 
#     0 - Successful 
#     8 - Error 
###############################################################################
# Modification History
# -----------------------------------------------------------------------------
# Name                 Date        Description
# -----------------    ---------- --------------------------------------------
# M Selvaggio          02/09/2005  Created 
###############################################################################
pipecreate()
{
if [ ! -p ${COMPFIL} ]
    then
    mkfifo -m 700 ${COMPFIL} 
    echo "Created compress pipe " >> ${LOGFIL} 2>&1
fi
}

pipeclean()
{
if [ -p ${COMPFIL} ]
  then
  rm ${COMPFIL} 
  echo "Removed compress pipe " >> ${LOGFIL} 2>&1
fi
}

dircreate()
{
if [ ! -d ${OUTDIR} ]
  then
   mkdir ${OUTDIR}
   echo "Created Output Directory " >> ${LOGFIL} 2>&1
fi
}

errchk()
{
status=$?
if [ $status -eq 0 ] ; then
  echo "$1 Complete return code $status \n" >> ${LOGFIL} 2>&1
else
  echo "Error in $1 return code $status \n" >> ${LOGFIL} 2>&1
  exit 8 
fi
}

###############################################################################
# MAIN starts here -  Set environment and check arguments 
###############################################################################
PROGNAME=`basename $0`
USAGE="usage: $PROGNAME <Oracle SID> <Table Owner> <Table Name> <Output Dir>"
status=0
if [ $# -ne 4 ]; then
   echo $USAGE
   exit 1  
else
   export ORACLE_SID="${1}"
   export OWNER="${2}"
   export TABLE="${3}"
   export OUTDIR="${4}/gencsv$$"
   dircreate
fi 

export DMPFIL=${OUTDIR}/ddl.dmp
export DDLFIL=${OUTDIR}/ddl.fil
export CTLFIL=${OUTDIR}/${OWNER}_${TABLE}.ctl
export LOGFIL=${OUTDIR}/${PROGNAME}.log
export FILE=${OUTDIR}/${OWNER}_${TABLE}.txt
export COMPFIL=${OUTDIR}/c$$.pipe
export TMPFIL=${OUTDIR}/gencsv.sql
export PATH=$PATH:/usr/local/bin
ORAENV_ASK="NO"
. /usr/local/bin/oraenv
ORAENV_ASK=""

###############################################################################
#  Create pipe and start logging  
###############################################################################
echo "====     Starting Process    ====" > ${LOGFIL} 2>&1
echo "Current Date time is `date`" >> ${LOGFIL} 2>&1
pipecreate
errchk "Pipe Create"

###############################################################################
#  Export Table DDL 
###############################################################################
echo "Exporting DDL... \n" >> ${LOGFIL} 2>&1
exp log=${OUTDIR}/${PROGNAME}_exp.log file=${DMPFIL} rows=n tables=${OWNER}.${TABLE} << !
/ as sysdba
!
errchk "Export"

###############################################################################
# Import dump file and create DDL script 
###############################################################################
echo "Creating DDL script ... \n" >> ${LOGFIL} 2>&1
imp log=${OUTDIR}/${PROGNAME}_imp.log file=${DMPFIL} full=y indexfile=${DDLFIL} << !
/ as sysdba
!
errchk "Import"

echo " Cleaning up DDL ... \n" >> ${LOGFIL} 2>&1
sed 's/REM //' ${DDLFIL} > ${OUTDIR}/${OWNER}_${TABLE}.ddl

errchk "DDL Clean"
rm ${DMPFIL}
rm ${DDLFIL} 
rm ${OUTDIR}/${PROGNAME}_imp.log
rm ${OUTDIR}/${PROGNAME}_exp.log

###############################################################################
# Execute SQLPlus to create CSV File
###############################################################################
echo "Start SQLPLUS to create CSV File ... \n" >> ${LOGFIL} 2>&1

cat ${COMPFIL} | compress > ${FILE}.Z &

sqlplus -s /nolog << ! >> ${LOGFIL} 2>&1
connect / as sysdba
whenever sqlerror exit -1
set pagesize 0
set verify   off
set feedback off
set linesize 130
set termout off
spool  ${TMPFIL}

select 'set linesize '||sum(data_length)
  from dba_tab_columns
   where table_name = upper('${TABLE}')
     and owner      = upper('${OWNER}');
select 'spool ${COMPFIL}'
  from dual;
select 'select '
  from dual;
select
  (case
     WHEN column_id = 1
     THEN
	(case
 	   when data_type like '%CHAR%'
	    then '''"''||'||column_name||'||''"'''
           when data_type = 'DATE'
	    then '''"''||to_char('||column_name||',''mmddyyyyhh24miss'')||''"'''
           else column_name
        end)
     ELSE (case
             when data_type like '%CHAR%'
              then '||'',"''||'||column_name||'||''"'''
	     when data_type = 'DATE'
	      then '||'',"''||to_char('||column_name||',''mmddyyyyhh24miss'')||''"'''
             else '||'',''||'||column_name
	   end)
   end)
  from sys.dba_tab_columns
 where table_name = upper('${TABLE}')
   and owner      = upper('${OWNER}')
 order by column_id;
select 'from ${OWNER}.${TABLE};'
      from dual;
select 'spool off'
from dual;
																spool off
@${TMPFIL}
!
errchk "CSV Create"
rm ${TMPFIL}
pipeclean
ROWCOUNT=`cat ${FILE}.Z  | uncompress | wc -l` 

###############################################################################
# Execute SQLPlus to create sqlldr control file
###############################################################################
echo "Start SQLPLUS to create sqldr ctl ... \n" >> ${LOGFIL} 2>&1

sqlplus -s /nolog << ! >> ${LOGFIL} 2>&1
connect / as sysdba
whenever sqlerror exit -1
set pagesize 0
set verify   off
set feedback off
set linesize 80 
spool ${CTLFIL}

select distinct
  'OPTIONS '||chr(10)||
  ' (SKIP=0,PARALLEL=TRUE) '||chr(10)||
  ' LOAD DATA '||chr(10)||
' INFILE '''||owner||'_'||table_name||'.txt'''||chr(10)|| 
  ' BADFILE '''||owner||'_'||table_name||'.bad'''||chr(10)||
  ' DISCARDFILE '''||owner||'_'||table_name||'.dsc'''||chr(10)||
' DISCARDMAX 0 '||chr(10)||
  ' INTO TABLE "'||table_name||'"'||chr(10)||
  'INSERT '||chr(10)||
  ' FIELDS TERMINATED BY '','''||chr(10)||
' OPTIONALLY ENCLOSED BY ''"'''||chr(10)||
  ' TRAILING NULLCOLS '
from dba_tab_columns
where table_name = upper('${TABLE}') 
  and owner      = upper('${OWNER}') 
group by owner, table_name;

select
   (case
  WHEN column_id = 1
  THEN '('||decode(data_type,'DATE',column_name||' "TO_DATE(:'||column_name||',''mmddyyyyhh24miss'')"',column_name)
  else ','||decode(data_type,'DATE',column_name||' "TO_DATE(:'||column_name||',''mmddyyyyhh24miss'')"',column_name)
    end)
from sys.dba_tab_columns
where table_name = upper('${TABLE}')
  and owner      = upper('${OWNER}')
order by column_id;

select ')'
 from dual;
spool off
!
errchk "Sqldr Ctl Create"

echo "\n===========================================================================" | tee -a ${LOGFIL}
echo " Output Durectory is ${OUTDIR} " | tee -a ${LOGFIL}
echo " Log File is ${LOGFIL} " | tee -a ${LOGFIL}
echo " Table DDL is ${DDLFIL} "  | tee -a ${LOGFIL}
echo " Sqloader CTL is ${CTLFIL} "   | tee -a ${LOGFIL}
echo " CSV file is ${FILE}.Z " | tee -a ${LOGFIL}
echo " Number of rows extracted is ${ROWCOUNT} " | tee -a ${LOGFIL}
echo "==========================================================================" | tee -a ${LOGFIL}

echo "\nCurrent Date time is `date`" >> ${LOGFIL} 2>&1
echo "====  Process End  ====" >> ${LOGFIL} 2>&1

exit $status
#### End Of Script ####