Oracle ORA-06545 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06545: PL/SQL Unhandled Exception — Causes, Fixes & Prevention

ORA-06545 occurs when a PL/SQL block or subprogram (procedure, function, or package) raises an exception that is not caught by any EXCEPTION handler, causing it to propagate unhandled back to the caller. It almost always appears alongside ORA-06512 stack trace entries, which pinpoint the exact line where the error originated. In short: your PL/SQL code threw an error and nothing caught it.

Top 3 Causes

1. Missing or Incomplete EXCEPTION Block

The most common cause. A PL/SQL block has no EXCEPTION section at all, or handles only specific exceptions while leaving others unhandled. Without a WHEN OTHERS catch-all, any unexpected runtime error will escape and trigger ORA-06545.

-- BAD: No exception handling
CREATE OR REPLACE PROCEDURE bad_proc AS
  v_sal NUMBER;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = 9999; -- Raises NO_DATA_FOUND
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
END;
/

-- GOOD: Proper exception handling
CREATE OR REPLACE PROCEDURE good_proc AS
  v_sal NUMBER;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = 9999;
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    RAISE;
END;
/

2. Unhandled User-Defined Exceptions

Developers raise custom exceptions using RAISE or RAISE_APPLICATION_ERROR, but the calling code doesn't have a matching WHEN clause to catch them. This is especially common with package-level exceptions that the caller doesn't reference by the correct package.exception_name syntax.

-- Package declaring a custom exception
CREATE OR REPLACE PACKAGE hr_pkg AS
  e_invalid_salary EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_salary, -20100);
  PROCEDURE update_salary(p_empno NUMBER, p_sal NUMBER);
END hr_pkg;
/

CREATE OR REPLACE PACKAGE BODY hr_pkg AS
  PROCEDURE update_salary(p_empno NUMBER, p_sal NUMBER) AS
  BEGIN
    IF p_sal < 0 THEN
      RAISE_APPLICATION_ERROR(-20100, 'Salary cannot be negative.');
    END IF;
    UPDATE emp SET sal = p_sal WHERE empno = p_empno;
    COMMIT;
  END;
END hr_pkg;
/

-- Caller must reference the package exception explicitly
BEGIN
  hr_pkg.update_salary(7369, -500);
EXCEPTION
  WHEN hr_pkg.e_invalid_salary THEN
    DBMS_OUTPUT.PUT_LINE('Salary error: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('General error: ' || SQLERRM);
END;
/

3. Unhandled Exceptions in Dynamic SQL or Autonomous Transactions

EXECUTE IMMEDIATE blocks and PRAGMA AUTONOMOUS_TRANSACTION procedures are self-contained execution contexts. If an exception is raised inside them without a local handler, it propagates directly to the caller as ORA-06545. Autonomous transactions are especially dangerous — an unhandled exception forces an automatic rollback of the autonomous transaction.

-- Autonomous transaction with proper exception handling
CREATE OR REPLACE PROCEDURE log_event(p_msg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO event_log (msg, log_date) VALUES (p_msg, SYSDATE);
  COMMIT; -- Required: autonomous transactions must commit or rollback
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Without this, ORA-06545 is raised
    DBMS_OUTPUT.PUT_LINE('Logging failed: ' || SQLERRM);
END;
/

-- Dynamic SQL with exception handling
CREATE OR REPLACE PROCEDURE count_rows(p_table VARCHAR2) AS
  v_sql   VARCHAR2(200);
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table);
  EXECUTE IMMEDIATE v_sql INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Dynamic SQL error: ' || SQLERRM);
    RAISE;
END;
/

Quick Fix Solutions

Always add a WHEN OTHERS handler to every PL/SQL block — even if it just logs and re-raises.
Use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in your OTHERS handler to get the exact line number where the original error occurred.
Check ORA-06512 entries in the error stack — they tell you the exact procedure name and line number to investigate.

-- Recommended OTHERS handler pattern
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(
      'Error: ' || SQLERRM || CHR(10) ||
      'Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    RAISE;
END;

Prevention Tips

Enable PL/SQL compile warnings with ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL' during development to catch potential issues at compile time before they hit production.
Adopt a team-wide exception handling standard: every procedure must include an EXCEPTION section with at minimum a WHEN OTHERS handler that logs to an error table using an autonomous transaction logger. Enforce this through code reviews and static analysis tools integrated into your CI/CD pipeline.

Related Oracle Errors

Error Code	Description
ORA-06512	Stack trace companion to ORA-06545; shows exact line numbers
ORA-01403	NO_DATA_FOUND — most frequent trigger of ORA-06545
ORA-01422	TOO_MANY_ROWS — another common unhandled exception source
ORA-06500	PL/SQL storage error — unhandled memory errors escalate to ORA-06545
ORA-20000–20999	User-defined errors via RAISE_APPLICATION_ERROR

📖 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.

DEV Community