Oracle ORA-04068 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04068: Existing State of Packages Has Been Discarded

ORA-04068 occurs when a session tries to call a PL/SQL package that has been recompiled or invalidated since the session last used it. Oracle discards the previous package state — including global variables and open cursors — and raises this error. Typically, the next call succeeds automatically, but unhandled exceptions can crash transactions in production systems.

Top 3 Causes

1. Package Recompilation During Active Sessions

When a package is recompiled (manually or due to a dependency change), any session that holds a reference to the old package state will receive ORA-04068 on its next call.

-- Check for INVALID objects after a deployment
SELECT object_name,
       object_type,
       status,
       last_ddl_time
  FROM dba_objects
 WHERE status = 'INVALID'
   AND object_type IN ('PACKAGE', 'PACKAGE BODY')
 ORDER BY last_ddl_time DESC;

-- Manually recompile a specific package
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;

2. DDL Changes on Dependent Objects

If a table, view, or synonym that a package depends on undergoes a DDL change (ALTER TABLE, CREATE OR REPLACE VIEW, etc.), Oracle automatically marks the package as INVALID, triggering ORA-04068 on the next call.

-- Identify package dependencies before making DDL changes
SELECT referenced_owner,
       referenced_name,
       referenced_type,
       name AS dependent_package
  FROM dba_dependencies
 WHERE type IN ('PACKAGE', 'PACKAGE BODY')
   AND referenced_name = 'MY_TABLE'  -- target object
 ORDER BY referenced_owner, name;

-- Recompile all invalid objects in a schema after DDL changes
EXEC UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

3. Stateful Package Global Variables

Packages that store session state in global variables lose all variable values when the package is invalidated and recompiled. Applications expecting preserved state will fail unless they handle reinitialization.

-- Example: Package with global state and safe initialization check
CREATE OR REPLACE PACKAGE BODY my_stateful_pkg AS

  -- Package initialization block (runs on first call after recompile)
  BEGIN
    g_is_initialized := FALSE;
    g_session_context := NULL;
  END my_stateful_pkg;
/

-- Calling code with ORA-04068 retry and re-initialization
DECLARE
  v_retry NUMBER := 0;
BEGIN
  LOOP
    BEGIN
      IF NOT my_stateful_pkg.g_is_initialized THEN
        my_stateful_pkg.initialize(SYS_CONTEXT('USERENV','SESSION_USER'));
      END IF;
      my_stateful_pkg.do_work();
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -4068 AND v_retry < 2 THEN
          v_retry := v_retry + 1;
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;
/

Quick Fix Solutions

Immediate fix — Add a retry handler for ORA-04068. The error is typically transient and the second call succeeds:

DECLARE
  v_retry NUMBER := 0;
BEGIN
  LOOP
    BEGIN
      my_package.my_procedure();  -- your package call
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -4068 AND v_retry < 3 THEN
          v_retry := v_retry + 1;
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
END;
/

Batch recompile — After any deployment, recompile all invalid objects before traffic resumes:

-- Serial recompile (safer for production)
EXEC UTL_RECOMP.RECOMP_SERIAL('MY_SCHEMA');

-- Parallel recompile (faster, use with caution)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'MY_SCHEMA');

Prevention Tips

Schedule deployments during low-traffic windows and always run UTL_RECOMP at the end of your deployment script to pre-validate all objects before users hit them.
Prefer stateless package design — pass required values as parameters rather than storing them in package-level global variables. If global state is unavoidable, implement a clear initialize() procedure and check the initialization flag on every entry point.

Related Errors

Error Code	Description
ORA-04061	Existing state of object has been invalidated (precedes ORA-04068)
ORA-04065	Object altered or dropped — cannot execute
ORA-06508	PL/SQL program unit not found (package was dropped)
ORA-00604	Error at recursive SQL level (often accompanies ORA-04068)

Tip: These errors almost always appear together as a stack. Always check the full error stack in your alert log or application log to identify the root cause object.

📖 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