python-oracledb User-Defined Exception

Question

I am executing a PL/SQL procedure from Python, and the procedure returns a user-defined exception. However, I am not getting the exception code and message that are defined in the PL/SQL package. How do I get the user-defined exception code and message?

SQL code:

PROCESSING_ERROR        EXCEPTION;

PROCEDURE my_procedure(
    exception_code OUT NUMBER,
    exception_message OUT VARCHAR2)
IS

    row_count number;

BEGIN

    --Initialize the return parms.
    exception_code := 0;
    exception_message := NULL;

    select count(*) from my_table into row_count;

    if row_count > 0 then
        raise processing_error;
    end if;

EXCEPTION
    -- An business rule or schema rule error was detected.
    WHEN PROCESSING_ERROR THEN
      dbms_output.put_line('Business rule or schema rule erorrs.');
      exception_code := -20001;
      exception_message := 'Data violated business logic';
      
    -- Local error - capture the Oracle codes and pass back to the caller.
    WHEN OTHERS THEN
        exception_code := SQLCODE;
        exception_message := 'my_procedure: ' || SUBSTR(SQLERRM, 1, 255);
END my_procedure;

Python code:

import oracledb


exception_code = cursor.var(int)
exception_message = cursor.var(str)

try:
    cursor.callproc('my_package.my_procedure', [exception_code, exception_message])
except oracledb.Error as e:
    err, = e.args
    print(exception_code, exception_message )
    print(err.code, err.message)

Output:

<oracledb.Var of type DB_TYPE_NUMBER with value 1> <oracledb.Var of type DB_TYPE_VARCHAR with value 'User-Defined Exception'>
(1, 'User-Defined Exception')

I would expect an output like:

(-20001, 'Data violated business logic')

Answer 1

Your package is not propagating an exception outside of the package. You raise the exception and then catch it in the EXCEPTION handler section of the block and instead set the OUT variables.

If you want to raise an error then:

CREATE PACKAGE test_package IS
  PROCESSING_ERROR EXCEPTION;
  PRAGMA EXCEPTION_INIT(PROCESSING_ERROR, -20001);

  PROCEDURE my_procedure(
    exception_code OUT NUMBER,
    exception_message OUT VARCHAR2
  );
END;
/

CREATE PACKAGE BODY test_package IS
  PROCEDURE my_procedure(
    exception_code OUT NUMBER,
    exception_message OUT VARCHAR2
  )
  IS
  BEGIN
    RAISE PROCESSING_ERROR;
  END;
END;
/

Then if you call the procedure then the exception raised is:

ORA-20001: 

This has the correct number but no message.

If you want the message then use RAISE_APPLICATION_ERROR (which doesn't need you to declare the exception first):

CREATE OR REPLACE PACKAGE BODY test_package IS
  PROCEDURE my_procedure(
    exception_code OUT NUMBER,
    exception_message OUT VARCHAR2
  )
  IS
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'Data violated business logic');
  END;
END;
/

Then the exception raised is:

ORA-20001: Data violated business logic

If you do that AND do not catch the exception then your code should work.

---

Your code would be:

CREATE PACKAGE BODY my_package IS
  PROCESSING_ERROR EXCEPTION;

  PROCEDURE my_procedure(
    exception_code OUT NUMBER,
    exception_message OUT VARCHAR2
  )
  IS
    row_count number;
  BEGIN
    --Initialize the return parms.
    exception_code := 0;
    exception_message := NULL;

    SELECT count(*)
    INTO row_count   -- INTO goes before FROM
    FROM my_table;

    IF row_count > 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Data violated business logic');
    END IF;
  END my_procedure;
END my_package;