How to return code from anonymous PLSQL block to ksh variable

Question

Here is a ksh :

$ORACLE_HOME/bin/sqlplus id/psw@$ORACLE_SID @$P_SQL/mysql.sql $1

return_code=$?

echo return_code

Here is the sql :

set serverout on
var error number
...
declare
   error number(1) := 0;
begin
   .......
exception
   when too_many_rows then
      error := 2;
end;
/

exit

How to pass the PLSQL error value to SQLPLUS variable and then the SqlPlus variable to the ksh variable return_code.

Thank you

Answer 1

It is possible to pass a SQL error code from a PL/SQL script to the shell.

However, the shell will perform modulo 256 on the SQL error number. If you needed a specific number, you could use a user defined error with a custom error number.

SET SERVEROUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;

DECLARE
    l_result VARCHAR2(1);
BEGIN
    SELECT result
    INTO   l_result
    FROM   (SELECT 'A' result
            FROM   dual
            UNION
            SELECT 'B' result
            FROM   dual);
EXCEPTION
    WHEN too_many_rows THEN
      Raise_application_error(-20226, 'Custom error ' || MOD(20226, 256) || ' for too many rows');
END;
/

exit