CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
Amarjot Singh
Amarjot Singh

Reputation: 70

Correct way to handle exception handshake between python and oracle

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)

Upvotes: 1

Views: 187

Answers (2)

Popeye
Popeye

Reputation: 35900

I think there is no issue except missing return value in the exception block in your code.

The exception which is raised by your function is handled by the exception block contained in the function itself.

see the demo as follows:

Creating the table

SQL> CREATE TABLE TEMP_LOG (
  2      LOG_   VARCHAR2(50)
  3  );

Table created.

SQL>

Creating the function, see inline comments:

SQL> CREATE OR REPLACE FUNCTION MARK_STEP (
  2      BATCH_ID     IN           NUMBER,
  3      REQUEST_ID   IN           VARCHAR2
  4  ) RETURN NUMBER IS
  5  BEGIN
  6      INSERT INTO TEMP_LOG VALUES ( 'entered' );
  7      RAISE NO_DATA_FOUND; -- error raised by this statement will be handeled by following exception block
  8      RETURN 0;
  9  EXCEPTION
 10      WHEN OTHERS THEN
 11          INSERT INTO TEMP_LOG VALUES ( 'exception' );
 12          RETURN -1; -- this is misssing in your code
 13  END;
 14  /

Function created.

SQL>

Calling the function:

SQL> SET SERVEROUT ON
SQL>
SQL> DECLARE
  2      CNT   NUMBER;
  3  BEGIN
  4      CNT := MARK_STEP(1, 'v');
  5      DBMS_OUTPUT.PUT_LINE('result := ' || CNT);
  6      COMMIT;
  7  END;
  8  /
result := -1

PL/SQL procedure successfully completed.

SQL>

Checking the data in the temp_log table:

SQL> SELECT * FROM TEMP_LOG;

LOG_
--------------------------------------------------
entered
exception

SQL>

See both the sections are executed and the result of the function was also -1.

Now, If you want to raise the error to the external calling program then you can remove the entire exception block as follows:

SQL> CREATE OR REPLACE FUNCTION MARK_STEP (
  2      BATCH_ID     IN           NUMBER,
  3      REQUEST_ID   IN           VARCHAR2
  4  ) RETURN NUMBER IS
  5  BEGIN
  6      INSERT INTO TEMP_LOG VALUES ( 'entered' );
  7
  8      COMMIT; -- added commit to see if it is working or not
  9      RAISE NO_DATA_FOUND; -- error raised by this statement will be handeled by following exception block
 10      RETURN 0;
 11  --EXCEPTION
 12  --    WHEN OTHERS THEN
 13  --        INSERT INTO TEMP_LOG VALUES ( 'exception' );
 14  --        RETURN -1; -- this is misssing in your code
 15  END;
 16  /

Function created.

SQL>

Calling the updated function and checking the data in the temp_log table.

SQL> TRUNCATE TABLE TEMP_LOG;

Table truncated.

SQL> SET SERVEROUT ON
SQL>
SQL> DECLARE
  2      CNT   NUMBER;
  3  BEGIN
  4      CNT := MARK_STEP(1, 'v');
  5      DBMS_OUTPUT.PUT_LINE('result := ' || CNT);
  6      COMMIT;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TEJASH.MARK_STEP", line 9
ORA-06512: at line 4


SQL> SELECT * FROM TEMP_LOG;

LOG_
--------------------------------------------------
entered

SQL>

See, the error is thrown now and all the code above the raise exception worked as expected.

I hope, you find this useful.

Cheers!!

Upvotes: 1

Infinite
Infinite

Reputation: 764

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)

Upvotes: 1

Related Questions