How to exit sqlplus when error happens

Question

I would like to catch any sql error that could happen so I wrote this in the ksh :

$ORACLE_HOME/bin/sqlplus -s u/p <<EOF
    WHENEVER OSERROR EXIT 68;
    WHENEVER SQLERROR EXIT SQL.SQLCODE;

    CREATE TABLE new_table
    AS (SELECT * FROM wrong_old_table);
    COMMIT;
EOF

I put a wrong name for the old table to see what happens. I expect to have the sqlcode only like I ask in WHENEVER SQLERROR but I have this :

 AS (SELECT * FROM wrong_old_table)
                      *
 ERROR at line 2:
 ORA-00942: table or view does not exist

I changed the code :

 $ORACLE_HOME/bin/sqlplus -s u/p <<EOF
    WHENEVER OSERROR EXIT 68;
    WHENEVER SQLERROR EXIT SQL.SQLCODE;

    BEGIN
       CREATE TABLE new_table
       AS (SELECT * FROM wrong_old_table);
       COMMIT;
    END;
 EOF

 sql_code=$?

 echo "code=$sql_code"

Even though there is an error, the code equals 0. Where is the sql error code ?

Hi! You have the PL/SQL block in your script but it is not executed since you have missed the final "/" in the next line after "END;". That's why your errorlevel is 0. — anilech
– anilech, Commented Jul 24, 2020 at 11:33
@mlwacosmos Do not include solution to question please (post a separate answer instead). — user4157124
– user4157124, Commented Jan 17 at 2:12

Bob Jarvis - Слава Україні · Accepted Answer · 2019-05-15 11:38:10Z

14

I'm not sure what you're doing, but this type of code works fine for me. For your first example:

SQL> whenever sqlerror exit sql.sqlcode;
SQL> create table new_table as select * from wrong_old_table;
create table new_table as select * from wrong_old_table
                                        *
ERROR at line 1:
ORA-00942: table or view does not exist

$ echo $?
174

Note that the exit code does not correspond exactly to the SQLCODE because the SQLCODE is always negative while the exit code in most shells are limited to values from +0 to +255; thus, it appears that what you get as an exit code is the low order byte of the two's complement of the SQLCODE (+). In practical terms, this means you get a non-zero value which is predictable and repeatable, but which isn't going to match the SQLCODE exactly, and where multiple SQLCODE values may produce the same exit code value.

Best of luck.

(+) If you care: in the example above the SQLCODE is -942. In hexadecimal this is 0xFFFFFC52. Inverting all the bits of this number gives you the one's complement, which is 0x3AD. Adding one to this value gives you the two's complement value, which is 0x3AE. The low-order byte of this is 0xAE, which is 174 in base 10. I've repeated this for SQLCODE values of -904, which gives an exit code of 136, and -6550, which gives an exit code of 150.

edited May 15, 2019 at 11:38

answered Nov 21, 2016 at 12:55

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

racherb Over a year ago

942 mod 256 = 174

Johan Boulé Over a year ago

Bash seems to be doing a modulo, which means for example that exiting with 256 or 512 is silently interpreted as zero!

Christian Bongiorno Over a year ago

literally, just change his first line to whenever sqlerror exit 1 and you'll get a legit error. I mean, you just need to know there was an error, right? Not what kind?

Perry · Accepted Answer · 2023-09-03 03:38:56Z

1

test.sh

function checkError {
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<EOF
WHENEVER OSERROR EXIT 68;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
select * from dual;

exit
EOF

}


####### MAIN #########

export ORACLE_SID='cat /etc/oratab | grep -v ^''#'' | grep -v ^$ | awk -F ":" ' { print $1 }' | grep -v ASM | uniq'
export ORACLE_HOME='grep $ORACLE_SID /etc/oratab | awk -F: ''{print $2}'''
export PATH=$ORACLE_HOME/bin:$PATH

checkError

exitcode=$?
echo "here is the error status $?"
echo ""
echo ""
echo "Here is the exit code $exitcode"

if [ $exitcode -ne 0 ]
then
echo "There was an issue with checkError. Aborting here..."
exit 3
else
echo "checkError successful.. Proceeding.."
fi

exit 0

## End of Code ##

Now run this code in your environment and you will see it.

edited Sep 3, 2023 at 3:38

Perry

3,90326 gold badges40 silver badges51 bronze badges

answered Aug 29, 2023 at 17:37

Nirmal Arri

111 bronze badge

Collectives™ on Stack Overflow

How to exit sqlplus when error happens

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related