Handling INVALID_TRANSACTION exception in PL/SQL with Example Code

PL/SQL Exception Handling: Exercise-12 with Solution

Handle the INVALID_TRANSACTION exception when a transaction is in an invalid state.

Sample Solution:

PL/SQL Code:

DECLARE
v_balance NUMBER := 1000;
v_invalid_transaction EXCEPTION;
  PRAGMA EXCEPTION_INIT(v_invalid_transaction, -1555); 
BEGIN
  BEGIN
v_balance := v_balance - 1500;
        IF v_balance< 0 THEN
      RAISE v_invalid_transaction;
    END IF;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Transaction committed successfully.');
  EXCEPTION
    WHEN v_invalid_transaction THEN
      DBMS_OUTPUT.PUT_LINE('Invalid transaction state. Rolling back...');
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
      ROLLBACK;
       DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');
  END;
EXCEPTION
  WHEN v_invalid_transaction THEN
    DBMS_OUTPUT.PUT_LINE('Invalid transaction state outside the transaction block.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Sample Output:

Invalid transaction state. Rolling back...
Transaction rolled back.

Explanation:

The said code in Oracle's PL/SQL that demonstrates the handling of the INVALID_TRANSACTION exception and raised when a transaction enters an invalid state, such as an attempt to commit a transaction with a negative balance.

A variable v_balance is initialized with the value 1000. An exception v_invalid_transaction is defined using the PRAGMA EXCEPTION_INIT to associate it with the Oracle error code -1555, which indicates an invalid transaction state.

Inside the nested BEGIN block, when it attempts to deduct 1500 from the balance (v_balance). If the resulting balance is negative (v_balance< 0), it raises the v_invalid_transaction exception.

Then it displays a message indicating an invalid transaction state, rolls back the transaction, and displays a message confirming the rollback.

The WHEN OTHERS exception raised, displays an error message with SQLERRM, rolls back the transaction, and confirms the rollback.

Flowchart:

Previous: Handling CASE_NOT_FOUND exception in PL/SQL with example code.
Next: Handling SUBSCRIPT_BEYOND_COUNT exception in PL/SQL.