sql from shell script

Question

Hi,

I have the below shell script in which sql file is called which has set of select and insert statements. Right now it's spooling output/error of the sql select /insert commands to the csv file. I want the output and error of the sql commands redirected to the shell script LOGFILE instead of spool file. How can i do it.

LOGPATH=${TEST_LOG}
LOGFILE=${SCRIPTNAME}.$(date '+%Y%m%d_%H%M%S').log

sql_test=${REPORT_HOME}/month_report.sql

exec > ${LOGPATH}/${LOGFILE} 2>&1

main "$@"

exit 0

main()
{   
    SPOOLTEST="${REPORT}/testreports/report_`date +%Y%m%d_%H%M%S`.csv"

    $ORACLE_HOME/bin/sqlplus -s << ENDSQL
                    ${DBLOGIN}@${DBNAME}
                    WHENEVER SQLERROR EXIT 1 ROLLBACK
                    WHENEVER OSERROR EXIT 1 ROLLBACK
                    SPOOL ${SPOOLTEST}
                    @${sql_test}
                    SPOOL OFF
    ENDSQL

    return
}

spool logs after a delay. after shell script logs i am able to find the spool logs. I tried the below one it's not working $ORACLE_HOME/bin/sqlplus -s << ENDSQL >> ${LOGPATH}/${LOGFILE} 2>&1

Answer 1

I have no installation of Oracle client on this machine, but would not replacing

SPOOL ${SPOOLTEST}

with

SPOOL ${LOGPATH}/${LOGFILE}

work ?

Answer 2

when i do the below in shell script it writes to the logfiles SET TRIMSPOOL ON

Answer 3

There is no easy way to do this - the shell can't "tell" which is an error, as SQL*Plus writes everything to STDOUT. It only writes to STDERR if there is a failure in SQL*Plus itself. You will have to do this using a language that can connect to the DB directly (e.g. Perl, Python, etc etc) and process the query results as a structured result set, and raise/catch exceptions for errors.