Bash & SqlPlus | Why setting whenever SQLERROR EXIT SQL.SQLCODE returns values up to 256 ?
A short article about whenever SQLERROR EXIT SQL.SQLCODE and what means 256
Scenario
Have you ever been in a script (either of type shell or bash) that is executing a sql statement in DB like the example presented bellow:
EX_VARIABLE="24/22/2021"
SQL_MSG=`sqlplus -s <user>/<pwd>@<db> <<-EOF
whenever SQLERROR EXIT SQL.SQLCODE ROLLBACK;
whenever OSERROR EXIT SQL.SQLCODE ROLLBACK;
select to_date('${EX_VARIABLE}','dd/mm/yyyy') from dual;
EOF`
echo $?;
and wondered why the hell is my echo $? giving me exit value 51, who gives me this value ?
Normally the error that we provoked, is on this line of code: select to_date(‘${EX_VARIABLE}’,’dd/mm/yyyy’) from dual; and if you run it it’s: ORA-01843: not a valid month
What is happening ?
The error code 1843 can’t be returned by SqlPlus because will only return exit codes between 0 and 255
so actually if you do 1843–256–256–256–256–256–256–256 = 51 the first number that is lower than 256 is 51 so this is the error that it will return.
Why is happening ?
SQL.SQLCODE is a variable in SQL*Plus that contains the error code of the most recent SQL statement executed. When an error occurs during SQL statement execution, SQL.SQLCODE is set to the corresponding Oracle error code.
By using the SQLERROR EXIT SQL.SQLCODE; command, you instruct SQLPlus to exit with the same exit code as the Oracle error code. Therefore, SQL*Plus maps the Oracle error code to a corresponding exit code between 1 and 255, using a specific algorithm.
And still 256 ?
In Unix-based systems, including Linux and macOS, an exit code is an 8-bit value that is returned by a program or script when it finishes executing. This convention allows for a maximum of 256 possible exit codes, ranging from 0 to 255.
In SQL*Plus, the exit code is used to indicate whether the program executed successfully or encountered an error. A return code of 0 indicates success, while a non-zero code indicates an error.
While Oracle error codes are limited to a maximum value of 9999, SQLPlus maps these error codes to a corresponding exit code between 1 and 255.
If you liked the article please take a minute to offer me a clap 👏 or even buy me a coffee https://www.buymeacoffee.com/stefansplace (;
