shell获取sqlplus变量,如何在shell脚本中捕获SQLPlus退出代码？

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

/

解决方案

Have you tried using

whenever sqlerror exit sql.sqlcode

in your sql script? (also see this link)