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