ORA-06508 PL/SQL Could Not Find Program Unit Being Called

ORA-06508: PL/SQL: Could Not Find Program Unit Being Called

Error Overview

Error Text: ORA-06508: PL/SQL: could not find program unit being called: "schema.program_unit"

The ORA-06508 error occurs when Oracle cannot locate a PL/SQL program unit (package, procedure, function, or type) that is being referenced. This typically happens when the program unit was dropped, invalidated, or was never created in the expected schema.

Common Causes

1. Program Unit Dropped or Missing

• Package, procedure, or function was dropped
• Object never existed in target schema
• Deployment script failed or was incomplete

2. Invalid Object State

• Package body invalidated by DDL changes
• Dependent objects changed
• Compilation errors preventing execution

3. Schema/Privilege Issues

• Wrong schema qualifier used
• Missing EXECUTE privilege
• Synonym pointing to non-existent object

4. Session State Issues

• Package state discarded after DDL
• Session-level package invalidation
• Cursor invalidation

Diagnostic Queries

Find the Program Unit

-- Search for the object in all schemas
SELECT owner, object_name, object_type, status, created, last_ddl_time
FROM dba_objects
WHERE object_name = UPPER('&object_name')
ORDER BY owner;

-- Search in current schema
SELECT object_name, object_type, status, last_ddl_time
FROM user_objects
WHERE object_name = UPPER('&object_name');

-- Search with wildcard
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name LIKE UPPER('%&partial_name%')
  AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE')
ORDER BY owner, object_name;

Check Object Status

-- Check if object is valid
SELECT object_name, object_type, status
FROM dba_objects
WHERE object_name = UPPER('&object_name')
  AND owner = UPPER('&owner');

-- Find invalid objects in schema
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = UPPER('&owner')
  AND status = 'INVALID'
  AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
ORDER BY object_type, object_name;

-- Check for compilation errors
SELECT name, type, line, position, text
FROM dba_errors
WHERE owner = UPPER('&owner')
  AND name = UPPER('&object_name')
ORDER BY sequence;

Check Synonyms

-- Find synonyms for the object
SELECT owner, synonym_name, table_owner, table_name
FROM dba_synonyms
WHERE synonym_name = UPPER('&object_name')
   OR table_name = UPPER('&object_name');

-- Check if public synonym exists
SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE owner = 'PUBLIC'
  AND synonym_name = UPPER('&object_name');

Check Privileges

-- Check execute privileges
SELECT grantee, privilege, grantable
FROM dba_tab_privs
WHERE table_name = UPPER('&object_name')
  AND privilege = 'EXECUTE';

-- Check if current user has access
SELECT *
FROM all_objects
WHERE object_name = UPPER('&object_name')
  AND object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION');

Resolution Steps

1. Recompile Invalid Objects

-- Recompile specific package
ALTER PACKAGE schema.package_name COMPILE;
ALTER PACKAGE schema.package_name COMPILE BODY;

-- Recompile procedure
ALTER PROCEDURE schema.procedure_name COMPILE;

-- Recompile function
ALTER FUNCTION schema.function_name COMPILE;

-- Recompile with debug
ALTER PACKAGE schema.package_name COMPILE DEBUG BODY;

2. Recompile All Invalid Objects

-- Use UTL_RECOMP for schema
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

-- Parallel recompilation (faster for many objects)
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'SCHEMA_NAME');

-- Recompile entire database
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Manual recompilation script
BEGIN
  FOR rec IN (
    SELECT owner, object_name, object_type
    FROM dba_objects
    WHERE status = 'INVALID'
      AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
    ORDER BY
      CASE object_type
        WHEN 'PACKAGE' THEN 1
        WHEN 'PACKAGE BODY' THEN 2
        ELSE 3
      END
  ) LOOP
    BEGIN
      IF rec.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';
      ELSE
        EXECUTE IMMEDIATE 'ALTER ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ' COMPILE';
      END IF;
      DBMS_OUTPUT.PUT_LINE('Compiled: ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Failed: ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ' - ' || SQLERRM);
    END;
  END LOOP;
END;
/

3. Create Missing Synonym

-- Create private synonym
CREATE SYNONYM local_name FOR schema.object_name;

-- Create public synonym (requires privileges)
CREATE PUBLIC SYNONYM object_name FOR schema.object_name;

-- Replace existing synonym
CREATE OR REPLACE SYNONYM local_name FOR schema.object_name;

4. Grant Execute Privileges

-- Grant to specific user
GRANT EXECUTE ON schema.package_name TO username;

-- Grant to role
GRANT EXECUTE ON schema.package_name TO role_name;

-- Grant with grant option
GRANT EXECUTE ON schema.package_name TO username WITH GRANT OPTION;

5. Fix Schema Qualification

-- Wrong: unqualified call may look in wrong schema
-- EXECUTE my_procedure;

-- Correct: fully qualified
EXECUTE schema_name.my_procedure;

-- Or set current schema
ALTER SESSION SET CURRENT_SCHEMA = schema_name;
EXECUTE my_procedure;

Prevention Strategies

1. Dependency Analysis Before DDL

-- Find dependencies before dropping/modifying
SELECT
  name, type,
  referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE referenced_name = UPPER('&object_name')
  AND referenced_owner = UPPER('&owner')
ORDER BY name;

-- Find what the object depends on
SELECT
  referenced_owner, referenced_name, referenced_type
FROM dba_dependencies
WHERE name = UPPER('&object_name')
  AND owner = UPPER('&owner')
ORDER BY referenced_name;

2. Implement Deployment Validation

-- Post-deployment validation script
CREATE OR REPLACE PROCEDURE validate_deployment AS
  v_invalid_count NUMBER;
BEGIN
  -- Check for invalid objects
  SELECT COUNT(*) INTO v_invalid_count
  FROM user_objects
  WHERE status = 'INVALID';

  IF v_invalid_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_invalid_count || ' invalid objects found');

    FOR rec IN (SELECT object_name, object_type FROM user_objects WHERE status = 'INVALID') LOOP
      DBMS_OUTPUT.PUT_LINE('  - ' || rec.object_type || ': ' || rec.object_name);
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('All objects are valid');
  END IF;
END;
/

3. Use Exception Handling

-- Gracefully handle missing program units
CREATE OR REPLACE PROCEDURE safe_call_procedure AS
BEGIN
  -- Attempt to call the procedure
  EXECUTE IMMEDIATE 'BEGIN schema.my_procedure; END;';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -6508 THEN
      -- Log the error and continue
      DBMS_OUTPUT.PUT_LINE('Program unit not found - skipping');
      -- Or raise custom error
      RAISE_APPLICATION_ERROR(-20001, 'Required procedure not available');
    ELSE
      RAISE;
    END IF;
END;
/

4. Package State Management

-- Check package state
SELECT
  package_name,
  namespace,
  stale,
  NVL(serially_reusable, 'NO') as serially_reusable
FROM v$session s, v$package_state ps
WHERE s.saddr = ps.saddr
  AND s.sid = SYS_CONTEXT('USERENV', 'SID');

-- Reset package state for session
EXEC DBMS_SESSION.RESET_PACKAGE;

-- Mark package as serially reusable (prevents state issues)
CREATE OR REPLACE PACKAGE stateless_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE my_proc;
END;
/

Understanding Package Invalidation

When a package becomes invalid:

Event	Package Spec	Package Body
Table altered	May invalidate	May invalidate
Dependent package recompiled	May invalidate	May invalidate
Grant/Revoke	Usually safe	Usually safe
Package body recompiled	Safe	N/A
Package spec recompiled	Invalidates body	Invalidates body

Related Errors

• ORA-04063 - Package body has errors
• ORA-04068 - Existing state of packages has been discarded
• ORA-06550 - PL/SQL compilation error
• ORA-00942 - Table or view does not exist

Emergency Response

Quick Recompilation

-- Fast recompile of single object
ALTER PACKAGE owner.package_name COMPILE;
ALTER PACKAGE owner.package_name COMPILE BODY;

-- Check if it worked
SELECT status FROM user_objects
WHERE object_name = 'PACKAGE_NAME' AND object_type = 'PACKAGE BODY';

Find and Fix Pattern

-- One-liner to find and show status
SELECT owner || '.' || object_name || ' (' || object_type || ') - ' || status as info
FROM dba_objects
WHERE object_name = UPPER('&missing_object')
ORDER BY owner;