Correct way to handle exception handshake between python and oracle

Question

I am having the below proc that I am calling from Python , the issue is that I am not able to see the exception coming out of the block, for reproducing right now I have forced raise an exception but I am not able to see this in the Python side script

CREATE OR REPLACE FUNCTION mark_step
(
   batch_id   IN NUMBER
  ,request_id IN VARCHAR2
) RETURN NUMBER IS


BEGIN
   INSERT INTO temp_log VALUES ('entered');
   RAISE no_data_found;

   return 0;
EXCEPTION
   WHEN OTHERS THEN
      INSERT INTO temp_log VALUES ('exception');

END mark_step;

The client script calling this function

l_result = cur.callfunc('mark_step', cx_Oracle.STRING,
                                    [batch_id,request_id])



print(l_result)

Answer 1

The function gracefully handles the exception and Python script does not get any visibility of the function having failed add Raise on the Exception Block

EXCEPTION
   WHEN OTHERS THEN
      INSERT INTO temp_log VALUES ('exception');
      RAISE;
END mark_step;

On the Python side you could add try , except blocks to explicitly handle the exception in case you are not doing that already

try:   
   l_result = cur.callfunc('credit_ref_mig.mark_step', cx_Oracle.STRING,
                                       [batch_id,request_id])

   print(l_result)
except as e:
   print(e)