Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Catching oracle exception in shell script

FirstName_LastNameApr 27 2015 — edited Apr 27 2015

Hi,

I'm trying to catch an oracle exception in a shell script. Below is my shell script -

#!/bin/sh

var=115326311

logfile=shell_log.txt

sqlplus -s  mbl/sop01  <<EOF

WHENEVER SQLERROR EXIT SQL.SQLCODE;

@plsql.sql $var;

EOF

return_type=$?

if [ $return_type != 0 ]

then

echo 'Error with code';

echo "$return_type";

exit 0;

fi

and this is the sql script -

set serveroutput on;

DECLARE

--set serveroutput on;

--v_company_id NUMBER := 'company_id';

v_name VARCHAR2(100);

BEGIN

--set serveroutput on;

dbms_output.ENABLE();

SELECT ename INTO v_name FROM emp WHERE emp_ID = &1;

DBMS_OUTPUT.PUT_LINE('Inside SQL File'||' || '|| v_name);

END;

/

exit

I'm basically passing an employee number that does not exist and hence the sql file would throw and no_data_found exception, which i'm trying to catch in the shell script. When I try to execute the shell script I get the below error --

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 8


Error with code

123

he sql file is aborting and the error there is just thrown to the command line, which I don't want. I want it to be captured in the shell script and perform my own logic. Also, it says the error code is 123 - I'm not sure from where this is being picked up, shouldn't it actually be the ORA-1403 code ?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 25 2015
Added on Apr 27 2015
1 comment
3,395 views