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 ?