Issue: COMMIT/ROLLBACK Not Supported in PL/iSQL Procedures
Problem
PL/iSQL procedures do not support COMMIT/ROLLBACK, while PL/pgSQL procedures do. This is a critical Oracle compatibility gap.
Steps to Reproduce
1. Test in IvorySQL Port 5432 (PostgreSQL Mode) - ✅ WORKS
Connect:
psql -h localhost -p 5432 -d postgres
Run this code:
CREATE TABLE test_tx (id INT, msg TEXT);
CREATE PROCEDURE test_rollback(p_id INT) AS $$
BEGIN
INSERT INTO test_tx VALUES (p_id, 'should rollback');
ROLLBACK;
INSERT INTO test_tx VALUES (p_id + 1, 'after rollback');
COMMIT;
END;
$$;
CALL test_rollback(10);
SELECT * FROM test_tx ORDER BY id;Expected Result: ✅ WORKS
id | msg
----+----------------
11 | after rollback
- First insert (id=10) was rolled back
- Second insert (id=11) was committed
- No errors
2. Test in IvorySQL Port 1521 (Oracle Mode) - ❌ FAILS
Connect:
psql -h localhost -p 1521 -d postgres
Run this code:
CREATE TABLE test_tx (id INT, msg TEXT);
CREATE PROCEDURE test_rollback(p_id INT) AS $$
BEGIN
INSERT INTO test_tx VALUES (p_id, 'should rollback');
ROLLBACK;
INSERT INTO test_tx VALUES (p_id + 1, 'after rollback');
COMMIT;
END;
$$;
CALL test_rollback(10);Expected Result: ❌ FAILS
ERROR: invalid transaction termination
CONTEXT: PL/iSQL function test_rollback(pg_catalog.int4) line 4 at ROLLBACK
3. Test in Oracle Database - ✅ WORKS
Connect:
docker exec -it ivorysql-oracle-1 sqlplus / as sysdba
Run this code:
CREATE TABLE test_tx (id NUMBER, msg VARCHAR2(100));
CREATE OR REPLACE PROCEDURE test_rollback(p_id NUMBER) AS
BEGIN
INSERT INTO test_tx VALUES (p_id, 'should rollback');
ROLLBACK;
INSERT INTO test_tx VALUES (p_id + 1, 'after rollback');
COMMIT;
END;
/
CALL test_rollback(10);
SELECT * FROM test_tx ORDER BY id;Expected Result: ✅ WORKS
ID MSG
---------- ----------------
11 after rollback
- Same behavior as PostgreSQL PL/pgSQL
- ROLLBACK and COMMIT work correctly
Comparison Summary
| Database |
Port |
Language |
COMMIT/ROLLBACK in Procedures |
Result |
| IvorySQL |
5432 |
PL/pgSQL |
✅ Supported |
Works |
| IvorySQL |
1521 |
PL/iSQL |
❌ Not Supported |
Error |
| Oracle |
1521 |
PL/SQL |
✅ Supported |
Works |
References
Issue: COMMIT/ROLLBACK Not Supported in PL/iSQL Procedures
Problem
PL/iSQL procedures do not support COMMIT/ROLLBACK, while PL/pgSQL procedures do. This is a critical Oracle compatibility gap.
Steps to Reproduce
1. Test in IvorySQL Port 5432 (PostgreSQL Mode) - ✅ WORKS
Connect:
Run this code:
Expected Result: ✅ WORKS
2. Test in IvorySQL Port 1521 (Oracle Mode) - ❌ FAILS
Connect:
Run this code:
Expected Result: ❌ FAILS
3. Test in Oracle Database - ✅ WORKS
Connect:
docker exec -it ivorysql-oracle-1 sqlplus / as sysdbaRun this code:
Expected Result: ✅ WORKS
Comparison Summary
References