PL/SQL to PL/iSQL Migration Guide: Transaction Control in Exception Blocks

[rophy]

PL/SQL to PL/iSQL Migration Guide: Transaction Control in Exception Blocks

This guide documents the limitations when migrating Oracle PL/SQL code that uses COMMIT/ROLLBACK inside EXCEPTION blocks, and provides refactoring patterns.

The Limitation

IvorySQL (like PostgreSQL) does not allow COMMIT or ROLLBACK inside blocks with EXCEPTION handlers:

-- This FAILS in IvorySQL
BEGIN
  INSERT INTO audit_log VALUES ('starting');
  COMMIT;
  process_data();
EXCEPTION
  WHEN OTHERS THEN
    log_error(SQLERRM);
END;
-- ERROR: cannot commit while a subtransaction is active

Why This Happens

IvorySQL uses subtransactions to implement exception block semantics. When you declare an EXCEPTION handler, a subtransaction (implicit savepoint) is created at block entry. This enables automatic rollback of all statements in the block if an error occurs.

The trade-off is that COMMIT and ROLLBACK cannot execute while inside a subtransaction, because doing so would leave the transaction state inconsistent.

Oracle vs IvorySQL Behavior

Behavior	Oracle	IvorySQL
Exception block atomicity	Statement-level (only failed statement rolls back)	Block-level (entire block rolls back)
COMMIT in exception block	Allowed	Not allowed
ROLLBACK in exception block	Allowed	Not allowed
Uncommitted work after exception	Preserved	Rolled back

---

Refactoring Patterns

Pattern 1: Move COMMIT Outside the Exception Block

The simplest refactoring is to capture the error state and move transaction control outside the exception block.

Oracle (original):

BEGIN
  INSERT INTO t1 VALUES (1);
  COMMIT;
  INSERT INTO t2 VALUES (2);
EXCEPTION
  WHEN OTHERS THEN handle_error();
END;

IvorySQL (refactored):

DECLARE
  v_error_occurred BOOLEAN := FALSE;
  v_error_msg TEXT;
BEGIN
  BEGIN
    INSERT INTO t1 VALUES (1);
    INSERT INTO t2 VALUES (2);
  EXCEPTION
    WHEN OTHERS THEN
      v_error_occurred := TRUE;
      v_error_msg := SQLERRM;
  END;

  IF v_error_occurred THEN
    ROLLBACK;
    -- Handle error here (e.g., log it)
  ELSE
    COMMIT;
  END IF;
END;

---

Pattern 2: Restructure Loops with Exception Handling

When processing rows in a loop with per-iteration commits, restructure so the COMMIT is outside the exception block.

Oracle (original):

FOR rec IN (SELECT * FROM source_data) LOOP
  BEGIN
    INSERT INTO target VALUES (rec.id, rec.data);
    COMMIT;  -- Commit each row
  EXCEPTION
    WHEN OTHERS THEN
      log_error(rec.id, SQLERRM);
  END;
END LOOP;

IvorySQL (refactored):

FOR rec IN (SELECT * FROM source_data) LOOP
  DECLARE
    v_error BOOLEAN := FALSE;
    v_errmsg TEXT;
  BEGIN
    BEGIN
      INSERT INTO target VALUES (rec.id, rec.data);
    EXCEPTION
      WHEN OTHERS THEN
        v_error := TRUE;
        v_errmsg := SQLERRM;
    END;

    IF v_error THEN
      ROLLBACK;
      INSERT INTO error_log VALUES (rec.id, v_errmsg);
    END IF;
    COMMIT;  -- Now outside exception block
  END;
END LOOP;

---

Pattern 3: Use Autonomous Transactions for Logging

When you need to persist error logs even if the main transaction rolls back, use a separate transaction context.

Oracle (original):

BEGIN
  do_work();
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log VALUES (SQLERRM);  -- Must survive rollback
    COMMIT;
    RAISE;
END;

IvorySQL (refactored):

Option A: Handle logging at the application layer

-- Main code - let the exception propagate
BEGIN
  do_work();
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;  -- Re-raise; application layer handles logging
END;

Option B: Use dblink for true autonomous transaction (if available)

-- Requires dblink extension and proper setup
CREATE OR REPLACE PROCEDURE log_error_autonomous(p_msg TEXT) AS
BEGIN
  PERFORM dblink_exec('dbname=' || current_database(),
    'INSERT INTO error_log VALUES (' || quote_literal(p_msg) || ')');
END;

---

Pattern 4: Split into Multiple Procedures

For complex logic, split the code into separate procedures where the outer procedure handles transaction control.

Oracle (original):

CREATE PROCEDURE process_all AS
BEGIN
  FOR i IN 1..100 LOOP
    BEGIN
      process_one(i);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN log_error(i);
    END;
  END LOOP;
END;

IvorySQL (refactored):

-- Inner procedure - does the work, no transaction control
CREATE OR REPLACE PROCEDURE process_one_impl(p_id INT) AS
BEGIN
  -- Business logic here
  process_one(p_id);
END;

-- Outer procedure - handles transactions and exceptions
CREATE OR REPLACE PROCEDURE process_all AS
  v_error BOOLEAN;
  v_errmsg TEXT;
BEGIN
  FOR i IN 1..100 LOOP
    v_error := FALSE;

    BEGIN
      BEGIN
        process_one_impl(i);
      EXCEPTION
        WHEN OTHERS THEN
          v_error := TRUE;
          v_errmsg := SQLERRM;
      END;

      IF v_error THEN
        ROLLBACK;
        INSERT INTO error_log VALUES (i, v_errmsg, CURRENT_TIMESTAMP);
      END IF;
      COMMIT;
    END;
  END LOOP;
END;

---

Pattern 5: Batch Processing with Savepoints

For batch operations where you want partial commits, use explicit savepoints outside exception blocks.

Oracle (original):

BEGIN
  FOR i IN 1..1000 LOOP
    BEGIN
      INSERT INTO target SELECT * FROM source WHERE id = i;
      IF MOD(i, 100) = 0 THEN
        COMMIT;  -- Commit every 100 rows
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        log_error(i);
    END;
  END LOOP;
  COMMIT;
END;

IvorySQL (refactored):

DECLARE
  v_batch_start INT := 1;
  v_error BOOLEAN;
  v_errmsg TEXT;
  v_error_id INT;
BEGIN
  FOR i IN 1..1000 LOOP
    v_error := FALSE;

    BEGIN
      BEGIN
        INSERT INTO target SELECT * FROM source WHERE id = i;
      EXCEPTION
        WHEN OTHERS THEN
          v_error := TRUE;
          v_errmsg := SQLERRM;
          v_error_id := i;
      END;

      IF v_error THEN
        -- Rollback current batch and log error
        ROLLBACK;
        INSERT INTO error_log VALUES (v_error_id, v_errmsg);
        COMMIT;
        v_batch_start := i + 1;
      ELSIF MOD(i, 100) = 0 THEN
        COMMIT;
        v_batch_start := i + 1;
      END IF;
    END;
  END LOOP;

  COMMIT;  -- Final commit for remaining rows
END;

---

Quick Reference

Oracle Pattern	IvorySQL Solution
COMMIT inside EXCEPTION block	Move COMMIT after END of exception block
ROLLBACK inside EXCEPTION block	Capture error, ROLLBACK after END
Loop with per-iteration COMMIT + exception	Nest exception block inside, COMMIT outside
Autonomous transaction for logging	Use application-layer logging or dblink
Multiple operations with partial commit	Use explicit savepoints, restructure exception handling

---

Common Migration Mistakes

Mistake 1: Nested Exception Blocks Don't Help

-- This still FAILS - the outer exception block creates a subtransaction
BEGIN
  BEGIN
    INSERT INTO t1 VALUES (1);
    COMMIT;  -- ERROR: still inside outer block's subtransaction
  END;
EXCEPTION
  WHEN OTHERS THEN NULL;
END;

Mistake 2: Forgetting to Handle Rollback State

-- After ROLLBACK, the transaction is in a clean state
-- Make sure subsequent operations are valid
BEGIN
  BEGIN
    INSERT INTO t1 VALUES (1);
  EXCEPTION
    WHEN OTHERS THEN
      v_error := TRUE;
  END;

  IF v_error THEN
    ROLLBACK;
    -- Can now do new operations in a fresh transaction
    INSERT INTO error_log VALUES ('error occurred');
    COMMIT;
  END IF;
END;

---

Background: Why This Limitation Exists

PostgreSQL (and IvorySQL) uses subtransactions to implement exception block semantics because:

1. Automatic cleanup: When an error occurs, all resources (locks, cursors, memory) allocated within the block are automatically cleaned up
2. Consistent state: The database returns to a known-good state at the block entry point
3. Snapshot isolation: Each block can have its own view of the data

Oracle uses a different approach with statement-level rollback, where only the failing statement is rolled back. This is a fundamental architectural difference.

Industry Context

This limitation exists in all PostgreSQL-based databases, including:

• PostgreSQL (native PL/pgSQL)
• EDB Postgres Advanced Server
• IvorySQL

EDB provides a parameter edb_stmt_level_tx that changes the rollback behavior to statement-level (like Oracle), but still does not allow COMMIT inside exception blocks.

---

Related Documentation

• IvorySQL GitHub Issue #1128 - Tracking this limitation
• PostgreSQL Documentation: Transaction Management
• EDB Documentation: Transaction Control