Oracle Sqlplus Error Handling Unix
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about
Sqlplus Whenever
Stack Overflow the company Business Learn more about hiring developers or posting ads with sqlplus return codes us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a sqlplus error codes community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up unix script - unable to trap or interpret error code within sqlplus properly up vote
Sqlplus Whenever Oserror
0 down vote favorite 1 I am trying to run a shell script (ksh) that runs oracle sqlplus to run a sql script in the background. Within the sql script I am using WHENEVER SQLERROR EXIT SQL.SQLCODE to trap the error code so I can check it and exit my program, else continue to next sql statement. I'm not sure if my problem is in my shell script trapping the
Capture Sql Error In Shell Script
error or the use of WHENEVER? I am producing/invoking the ORA- error, but return code still shows as Return_code=0, (pass) and continues to run the next script. It should fail and exit the program. Can someone help me configure this script properly? My if-then-else logic maybe flawed too. Thanks. Here's my sql (whenever.sql) script to invoke error: WHENEVER SQLERROR EXIT SQL.SQLCODE begin SELECT COLUMN_DOES_NOT_EXIST FROM DUAL; END; / Here's my script: KEY=$BASEDIR/.keyinfo; LOG=$BASEDIR/run_tst.log; # Check before we run if [ -f "$KEY" ] then IFS=" " set -A arr $(cat $KEY) echo "Running Test ===>$TIMESTAMP" >> $LOG 2>&1 /bin/sqlplus ${arr[0]}/${arr[1]} @whenever.sql & pid1=$! echo "Waiting for PID:$pid1" >> $LOG 2>&1 wait $pid1 ret=$? echo "Return_code=$?" >> $LOG 2>&1 if [ $ret !=0 ] #if not success then exit $ret echo "Error found...Return_code=$?" >> $LOG 2>&1 echo "Error found...exiting program ===>$TIMESTAMP" >> $LOG 2>&1 exit 1 else /bin/sqlplus ${arr[0]}/${arr[1]} @tst2.sql fi else echo "key not found. Exiting. ==>$TIMESTAMP" $LOG 2>&1 fi exit 0 Results (Showing 0, since there is error, should be something else other than 0). Running Test ===>20130825-09:25 Waiting for PID:6383 Return_code=0 I also tried WHENENVER SQLERROR EXIT 1 and still getting same result of Return_code=0 Output from testing: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit P
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the sqlplus exception handling workings and policies of this site About Us Learn more about Stack
Whenever Sqlerror Exit 1 Shell Script
Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs whenever sqlerror exit sql.sqlcode shell script Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join http://stackoverflow.com/questions/18430979/unix-script-unable-to-trap-or-interpret-error-code-within-sqlplus-properly them; it only takes a minute: Sign up How do I capture a SQLPlus exit code within a shell script? up vote 8 down vote favorite 2 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 http://stackoverflow.com/questions/1402610/how-do-i-capture-a-sqlplus-exit-code-within-a-shell-script 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_co
Last updated: January 03, 2005 - 11:19 pm UTC Category: Database � Version: Oracle 8i Whilst you are here, check out some content from the AskTom team: On Top-n and Pagination Queries Latest Followup You Asked Hi Tom, In order to pass an Oracle https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:444389210579 error to Unix, I use the "whenerror..." statement. This works fine for some kind of errors (like division by zero, invalid SP name, ...), BUT NOT FOR ALL. In particular, when the error ORA-01652 (segments too small) is raised in Oracle, it is not passed to Unix. Is there a way to raise this error at the Unix level ? Thanks, Thierry. and we said... It is working however the status returned to Unix is limited to a single 1 shell script number (in the range of 0..255). If you run a shell script like this: #!/bin/csh -v sqlplus -s / <