DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01086 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01086: Savepoint Never Established — Causes, Fixes & Prevention

ORA-01086 is thrown by Oracle when you attempt to execute a ROLLBACK TO SAVEPOINT command referencing a savepoint name that does not exist within the current transaction. Savepoints are transaction markers created with the SAVEPOINT statement, and they are automatically destroyed when a COMMIT or ROLLBACK is issued. This error typically signals a logic flaw in your transaction management code.

Top 3 Causes

1. Using ROLLBACK TO Without Declaring the Savepoint First

The most common cause: a ROLLBACK TO SAVEPOINT is executed before the corresponding SAVEPOINT statement has been run in the same transaction.

-- BAD: Triggers ORA-01086
BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    ROLLBACK TO SAVEPOINT before_update;  -- ORA-01086! Never declared.
END;
/

-- GOOD: Declare savepoint before using it
BEGIN
    SAVEPOINT before_update;  -- Declare first
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

    IF SQL%ROWCOUNT > 50 THEN
        ROLLBACK TO SAVEPOINT before_update;
        DBMS_OUTPUT.PUT_LINE('Too many rows affected. Rolled back.');
    ELSE
        COMMIT;
    END IF;
END;
/
Enter fullscreen mode Exit fullscreen mode

2. Referencing a Savepoint After COMMIT or ROLLBACK

Every COMMIT or ROLLBACK ends the current transaction and destroys all savepoints defined within it. Attempting to roll back to a savepoint from a previous transaction will always raise ORA-01086.

-- BAD: Savepoint is gone after COMMIT
BEGIN
    SAVEPOINT sp_step1;
    INSERT INTO audit_log (action) VALUES ('Step 1');
    COMMIT;  -- Transaction ends; sp_step1 is destroyed

    INSERT INTO audit_log (action) VALUES ('Step 2');
    ROLLBACK TO SAVEPOINT sp_step1;  -- ORA-01086! sp_step1 no longer exists.
END;
/

-- GOOD: Re-declare savepoints after each COMMIT
BEGIN
    SAVEPOINT sp_step1;
    INSERT INTO audit_log (action) VALUES ('Step 1');
    COMMIT;

    -- New transaction starts here — re-declare savepoint
    SAVEPOINT sp_step2;
    INSERT INTO audit_log (action) VALUES ('Step 2');
    ROLLBACK TO SAVEPOINT sp_step2;  -- Valid within this transaction
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Savepoint Name Typo or Mismatch

Although Oracle savepoint names are case-insensitive in plain SQL, dynamic code or copy-paste errors can easily produce name mismatches that result in ORA-01086.

-- BAD: Name mismatch (typo)
BEGIN
    SAVEPOINT my_save_point;
    UPDATE orders SET status = 'PENDING' WHERE order_id = 1001;
    ROLLBACK TO SAVEPOINT my_savepoint;  -- ORA-01086! Underscore placement differs.
END;
/

-- GOOD: Use package constants to avoid typos
CREATE OR REPLACE PACKAGE sp_names AS
    c_order_sp CONSTANT VARCHAR2(30) := 'SP_ORDER_PROCESS';
END sp_names;
/

BEGIN
    EXECUTE IMMEDIATE 'SAVEPOINT ' || sp_names.c_order_sp;
    UPDATE orders SET status = 'PENDING' WHERE order_id = 1001;
    EXECUTE IMMEDIATE 'ROLLBACK TO SAVEPOINT ' || sp_names.c_order_sp;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Handle ORA-01086 gracefully using PRAGMA EXCEPTION_INIT so your application can recover without crashing:

DECLARE
    e_no_savepoint EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_no_savepoint, -1086);
BEGIN
    SAVEPOINT sp_safe;
    DELETE FROM temp_staging WHERE processed = 'N';

    ROLLBACK TO SAVEPOINT sp_safe;  -- Valid rollback
    COMMIT;

EXCEPTION
    WHEN e_no_savepoint THEN
        DBMS_OUTPUT.PUT_LINE('Savepoint not found. Performing full rollback.');
        ROLLBACK;
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always declare savepoints at the start of logical transaction blocks. Treat SAVEPOINT declarations like variable declarations — put them at the top of each logical unit before any DML work begins. This makes the transaction flow easy to audit during code reviews.

Add a code review checklist item that verifies the sequence: SAVEPOINT → DML operations → ROLLBACK TO or COMMIT. Also, remind your team that any intermediate COMMIT inside a loop or procedure will silently invalidate all savepoints set before it, which is one of the most overlooked sources of ORA-01086 in batch processing jobs.

Related Errors

Error Code Description
ORA-02074 Cannot SAVEPOINT in a distributed transaction
ORA-01085 Preceding savepoint-related DML conflict
ORA-01012 Not logged on — indirect session/savepoint issue

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)