Oracle ORA-06575 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06575: Package or Function is in an Invalid State

ORA-06575 occurs when Oracle attempts to execute a PL/SQL object — such as a package, function, procedure, or trigger — that is currently marked as INVALID. This typically happens after a dependent object (table, view, or another package) has been altered or dropped without recompiling the affected PL/SQL objects. In production environments, this error can surface suddenly after routine DDL operations and bring down critical application workflows.

Top 3 Causes

1. Dependent Object Was Altered or Dropped

When a table or view that a package references undergoes a structural change (ALTER TABLE, DROP TABLE), Oracle automatically marks all dependent PL/SQL objects as INVALID.

-- Check which objects depend on a specific table
SELECT name,
       type,
       referenced_name,
       referenced_type
FROM   user_dependencies
WHERE  referenced_name = 'EMPLOYEES'
ORDER  BY type, name;

-- Find all INVALID objects in your schema
SELECT object_name,
       object_type,
       status,
       last_ddl_time
FROM   user_objects
WHERE  status = 'INVALID'
ORDER  BY object_type, object_name;

2. Object Created With Compilation Errors

Oracle allows PL/SQL objects to be created even when they contain errors, storing them in an INVALID state with a Warning: compiled with compilation errors message. Calling such an object immediately triggers ORA-06575.

-- Check compilation errors for a specific object
SELECT line,
       position,
       text
FROM   user_errors
WHERE  name = 'MY_PACKAGE'
  AND  type = 'PACKAGE BODY'
ORDER  BY sequence;

-- Shortcut in SQL*Plus
SHOW ERRORS PACKAGE BODY my_package;

3. Privilege Revoked or Synonym Dropped

If a GRANT on a referenced object is revoked, or a SYNONYM pointing to that object is dropped, the dependent PL/SQL object becomes INVALID. Note that privileges granted via roles do not apply inside PL/SQL — only direct grants work.

-- Grant direct privilege (NOT via role)
GRANT SELECT ON hr.employees TO app_user;
GRANT EXECUTE ON dbms_crypto  TO app_user;

-- Recompile after fixing privileges
ALTER PACKAGE app_user.my_package COMPILE BODY;

Quick Fix Solutions

Option 1 — Recompile a single object manually:

ALTER PACKAGE   my_package   COMPILE;
ALTER PACKAGE   my_package   COMPILE BODY;
ALTER FUNCTION  my_function  COMPILE;
ALTER PROCEDURE my_procedure COMPILE;
ALTER TRIGGER   my_trigger   COMPILE;

Option 2 — Bulk recompile using UTL_RECOMP (recommended after large DDL changes):

-- Recompile all INVALID objects in one schema
EXEC UTL_RECOMP.RECOMP_SERIAL('APP_SCHEMA');

-- Parallel recompile across the whole database (4 threads)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Confirm no INVALID objects remain
SELECT COUNT(*) AS still_invalid
FROM   dba_objects
WHERE  status = 'INVALID';

Prevention Tips

1. Include a recompile step in every deployment script.
After any DDL operation, automatically call UTL_RECOMP and validate that zero INVALID objects remain before the deployment is marked as successful.

-- Deployment validation block
DECLARE
  v_count NUMBER;
BEGIN
  UTL_RECOMP.RECOMP_SERIAL('APP_SCHEMA');

  SELECT COUNT(*) INTO v_count
  FROM   user_objects
  WHERE  status = 'INVALID';

  IF v_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      v_count || ' INVALID object(s) remain after recompile. Aborting.');
  END IF;
END;
/

2. Always use direct grants inside PL/SQL, never rely on roles.
Audit your schemas periodically to ensure all referenced objects have direct privileges, and keep dependency maps updated when planning schema changes.

Related Errors

Error Code	Description
ORA-04063	View has errors — similar invalid-state issue for views
ORA-04068	Existing package state discarded after recompile
ORA-04065	Stored procedure altered or dropped while in use
ORA-06550	PL/SQL compilation error detail (often accompanies ORA-06575)

📖 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