Oracle ORA-06564 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-06564: object does not exist — What It Means and How to Fix It

ORA-06564 is a PL/SQL runtime error that occurs when Oracle cannot locate a referenced database object — such as a table, view, sequence, procedure, or package — at the time of execution. Unlike compile-time errors, this error surfaces during runtime, making it particularly tricky to diagnose in complex production environments. It frequently appears when using built-in packages like DBMS_UTILITY or when executing dynamic SQL against objects that no longer exist.

Top 3 Causes

1. The Referenced Object Has Been Dropped or Renamed

The most common cause is that an object referenced inside a stored procedure or package has been dropped or renamed after the PL/SQL code was compiled. The code compiles fine but fails at runtime because the underlying object is gone.

-- Check if the object still exists
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS, OWNER
FROM ALL_OBJECTS
WHERE OBJECT_NAME = UPPER('YOUR_OBJECT_NAME')
  AND OWNER = UPPER('YOUR_SCHEMA');

-- Find all INVALID objects in your schema
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM USER_OBJECTS
WHERE STATUS = 'INVALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;

2. Missing Privileges or Synonym for Cross-Schema Objects

When a PL/SQL unit in SCHEMA_A references an object owned by SCHEMA_B without the proper grants or synonyms in place, Oracle treats the object as non-existent and throws ORA-06564.

-- Grant privileges from the owning schema
GRANT SELECT, INSERT ON schema_b.target_table TO schema_a;
GRANT EXECUTE ON schema_b.my_procedure TO schema_a;

-- Create a public synonym so any schema can reference it
CREATE PUBLIC SYNONYM target_table FOR schema_b.target_table;

-- Verify existing grants
SELECT GRANTEE, PRIVILEGE, TABLE_NAME, OWNER
FROM ALL_TAB_PRIVS
WHERE TABLE_NAME = UPPER('TARGET_TABLE')
  AND OWNER = UPPER('SCHEMA_B');

3. Typo or Case Mismatch in Dynamic SQL

Oracle stores object names in uppercase by default. When building SQL strings dynamically using EXECUTE IMMEDIATE or DBMS_SQL, a lowercase or mixed-case object name that doesn't match what's stored in the data dictionary will cause ORA-06564.

-- Safe dynamic SQL with pre-validation
DECLARE
  v_obj_name  VARCHAR2(100) := 'employees';
  v_count     NUMBER := 0;
  v_sql       VARCHAR2(500);
  v_result    NUMBER;
BEGIN
  -- Validate the object exists before executing
  SELECT COUNT(*)
  INTO v_count
  FROM ALL_OBJECTS
  WHERE OBJECT_NAME = UPPER(v_obj_name)
    AND OBJECT_TYPE = 'TABLE';

  IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Object does not exist: ' || v_obj_name);
  END IF;

  -- Execute only after validation
  v_sql := 'SELECT COUNT(*) FROM ' || UPPER(v_obj_name);
  EXECUTE IMMEDIATE v_sql INTO v_result;

  DBMS_OUTPUT.PUT_LINE('Total rows: ' || v_result);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
    RAISE;
END;
/

Quick Fix Solutions

-- Recompile a single invalid package
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;

-- Recompile all invalid objects in a schema (requires DBA privilege)
EXEC DBMS_UTILITY.COMPILE_SCHEMA(SCHEMA => 'YOUR_SCHEMA', COMPILE_ALL => FALSE);

-- Check object dependencies before dropping anything
SELECT NAME, TYPE, DEPENDENCY_TYPE
FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('YOUR_OBJECT_NAME')
  AND REFERENCED_OWNER = UPPER('YOUR_SCHEMA')
ORDER BY TYPE, NAME;

Prevention Tips

Automate dependency checks in your deployment pipeline. Before any DDL change (DROP, RENAME, ALTER), query ALL_DEPENDENCIES to identify all PL/SQL objects that rely on the target. Include a post-deployment step to recompile invalid objects using DBMS_UTILITY.COMPILE_SCHEMA. This single practice eliminates the majority of ORA-06564 occurrences in production.
Enforce a strict naming convention and synonym management policy. All cross-schema object references should go through registered synonyms, and any rename or drop operation must trigger an update to associated synonyms and grants. Pair this with a change management checklist so no object modification goes live without a dependency impact review.

Related Errors

ORA-00942: table or view does not exist — Similar but raised in plain SQL (DML/DDL) rather than PL/SQL context.
ORA-04042: procedure, function, package, or package body does not exist — Raised when a callable object is missing; often appears alongside ORA-06564.
ORA-06550: PL/SQL compilation error — Frequently accompanies ORA-06564 when an invalid reference is caught at compile time.

📖 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