9

I have a KornShell (ksh) script that logins into SQL*Plus and executing a script. Within the shell script I would like to capture the status code of the SQL statement that was executed. Currently there is an error with SQL and I am unable to capture it by checking $?. How would I capture the success or error code from the sql statement and pass it to the shell script.

Snippet of ksh script:

sqlplus $JDBC_FBUID_U/$JDBC_FBPWD_U@$JDBC_FBDB @${FBC_HOME}/FBCS003.sql ${outputfile}
if [ $? != 0 ]
then
  msg_txt="The execution of Sql script /tmp/FBCS003.sql failed.  Please investigate."
  echo ${msg_txt}
  echo ${msg_txt} | mailx -r ${fromemail} -s "FBCB003: The execution of Sql script /tmp/FBCS003.sql failed." ${toemail}
  epage -n ${pagerdef} ${pagernum} "FBCB003: ${msg_txt}"
  exit 1
fi

SQL script FBCS003.sql

-- Set SQLPlus variables.
SET NEWPAGE 0
SET WRAP OFF
SET LINESIZE 9999
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET PAGESIZE 0
SET COLSEP |
SET TRIMSPOOL ON
SET TIMING ON

-- Open output file
-- The file path and name are passed from the calling script FBCS003.
spool &1

-- Main Select Statement
select
ct.fiscal_yr_no,
ct.acct_per_no,
ct.bus_unit_id,
ct.btch_file_seq_no,
ct.comm_tran_srce_cd,
ct.rec_no,
ct.rev_gl_acct_no,
ct.gl_prod_cd,
ct.prod_desc,
ct.paid_ir_no,
ct.srce_ir_no,
ct.ir_no_house_acct_rsn_txt,
ct.vndr_acct_ty_id,
ct.clnt_na,
ct.issr_na,
ct.clnt_na,
ct.issr_na,
ct.trd_da,
ct.setl_da,
ct.ord_ty_cd,
ct.actv_ty_cd,
ct.prin_amt,
ct.grs_comm_amt,
ct.net_comm_amt,
ct.vndr_prod_ty_cd,
ct.vndr_stmt_id
from fin.comm_tran ct
where ct.bus_unit_id = 'EJL'
and ct.vndr_acct_ty_id in
('11111111','222222222')
-- Execute sql statement.
/

-- Close output file
spool off

-- Exit SQL
exit
/
javaPlease42
  • 4,699
  • 7
  • 36
  • 65
AieshaDot
  • 785
  • 3
  • 7
  • 13

2 Answers2

14

Have you tried using

whenever sqlerror exit sql.sqlcode

in your sql script? (also see this link)

ChristopheD
  • 112,638
  • 29
  • 165
  • 179
  • 3
    I could be wrong, but this won't work for connection errors or other errors that would stop sqlplus from executing the sql code. I'm working this right now, and the best solution I've found is to grep -c "ERROR" ${LOG_FILE} >/dev/NULL the log file we are piping the sqlplus output to for the string ERROR and than checking the return code from that. Is there a better solution? – Casey Apr 08 '10 at 15:06
  • connection error appears to cause sqlplus to exit with errorlevel 1 already – Roger Perkins Jun 10 '16 at 16:21
2

Exit from the sql file with

exit sql.sqlcode;

capture it in shell with $?

CoolBeans
  • 20,654
  • 10
  • 86
  • 101
apllom
  • 53
  • 3