ORA-06508: PL/SQL: could not find program unit being called
-- Oracle throws ORA-06508 error when there is synchronization issue between PL/SQL 
-- user objects such as packages, even when all objects are compiled and in valid 
-- state.  The query below shows how to identify the objects that are not 
-- synchronized.  This is a PL/SQL error and is identified by having EXCEPTION
-- handling and error logging in the code.

-- Check the object status using query below.

SELECT do.name d_name, u.name owner 
FROM sys.obj$ do,
       sys.dependency$ d,
       sys.obj$ po,
       user$ u 
WHERE P_OBJ#=po.obj# (+) 
AND   D_OBJ#=do.obj# 
AND   do.status=1            /* dependent is valid*/ 
AND   po.status=1            /* parent is valid*/ 
AND   po.stime!=p_timestamp  /* parent timestamp not match*/ 
AND   do.owner#=u.user# 
AND   do.type# = 5 
ORDER BY u.name, do.name;

-- Other best way to solve this issue is to recompile all objects by one-by-one 
-- or code shown below from reference [1]
-- PL/SQL code to be executed by SYSDBA

DECLARE
 CURSOR cur_syn IS
  SELECT do.name d_name, u.name owner 
  FROM sys.obj$ do,
       sys.dependency$ d,
       sys.obj$ po,
       user$ u 
  WHERE P_OBJ#=po.obj# (+) 
  AND   D_OBJ#=do.obj# 
  AND   do.status=1            /* dependent is valid*/ 
  AND   po.status=1            /* parent is valid*/ 
  AND   po.stime!=p_timestamp  /* parent timestamp not match*/ 
  AND   do.owner#=u.user# 
  AND   do.type# = 5 
  ORDER BY u.name, do.name;
 
  v_syn_name  obj$.name%TYPE; 
  v_tab_own   user$.name%TYPE; 

BEGIN  
  OPEN cur_syn; 
  LOOP  
    FETCH cur_syn INTO v_syn_name,v_tab_own; 
    EXIT WHEN cur_syn%notfound; 
    IF  v_tab_own = 'PUBLIC' THEN
      EXECUTE IMMEDIATE 'ALTER PUBLIC SYNONYM '||v_syn_name||' COMPILE'; 
    ELSE  
      EXECUTE IMMEDIATE 'ALTER SYNONYM '||v_tab_own||'.'||v_syn_name||' COMPILE'; 
    END IF; 
  END LOOP;
  CLOSE cur_syn; 

END; 
/


Reference

1. Ask Tom Reference

2. Compile Objects

3. Autonomous Transaction

4. Exception Handling

5. Error Logging

Oracle registered trademark of Oracle Corporation.

Last Revised On: June 05th, 2015

  390