ORA-04065: not executed, altered or dropped stored procedure

Question

I have an Stored procedure in Oracle Enterprise 12c. This program is calling more than 100 sub-procedures to truncate tables and re-insert (ETL).

Master Proc IS
BEGIN
  CHILD1;
  CHILD2;
  CHILD3;
   ETC...
END;

This process is being executed every 4hrs as requested. The problem is that sometimes we are getting some errors:

ORA-04065: not executed, altered or dropped
ORA-04065: not executed, altered or dropped stored procedure "child proc1"
ORA-06508: PL/SQL: could not find program unit being called: "OD

But this seems to be solved by itself in the next load even if I re-execute after get the error the procedure finish without any error.Then after some time we get the error again with random stored procedure, first time child1 then child2 then child1 again.

Any idea how to solve or avoid this error?

Note: Not using a packages. Just standalone sp's.

Possible duplicate of [ORA-06508: PL/SQL: could not find program unit being called](https://stackoverflow.com/questions/19376440/ora-06508-pl-sql-could-not-find-program-unit-being-called) — OldProgrammer, Nov 30 '18 at 16:31
Do sub-stored procedures have logging of errors and exceptions to trace or catch onto explicitly expound the erroneous lines and statements? — Jacob, Nov 30 '18 at 16:48
They do have. I have an Exception block in every sub-stored procedure to catch the error and log-in into audit table but nothing is going there. — Jorge Flores, Nov 30 '18 at 16:52
@JorgeFlores Having seen ETL in the question, does the ETL tool provide logs of errors to allude the spurious? If this is not the case, why not debug the stored procedure either by considering the sub-procedures which have the errors, or use the debug option of the IDE. Needless to say, ensure to exclude the ones which do not have errors. — Jacob, Nov 30 '18 at 17:31
That is the problem. I am using some tool (Ellucian IA_Admin) to trigger the master stored procedure every 4 hrs. in that website I am getting the error but in the master level not in the child. The problem is that is failing either with procedure one or two or 99 randomly. — Jorge Flores, Nov 30 '18 at 19:36
I can't even replicate the error in DEV environment because all objects are valid and I did not get the error. Something that I just found is : I took a look a the DBA_OBJECTS in Oracle and I noticed that the same object which the master procedure "Could not find" show me the LAST_DDL at the same time that the error occurs. — Jorge Flores, Nov 30 '18 at 19:38
@JorgeFlores Why not clone the production database to a development environment and start the debugging process. — Jacob, Dec 01 '18 at 05:17
Because my DBA, I'll to try to get a copy of prod DB. BTW the issue did not happen since last week. — Jorge Flores, Dec 03 '18 at 16:02

William Robertson · Accepted Answer · 2018-12-01T15:16:21.903

Something is invalidating a PL/SQL object (package, type, procedure, function) in the call chain, perhaps dropping and recreating a table that it refers to, which forces the runtime engine to recompile it on the next call. If a package has state (global variable or cursor), that will be lost on recompiling so you get a ORA-04068: existing state of packages has been discarded error with an ORA-04065 somewhere in the stack.

I'm not sure how you get two ORA-04065 errors and no ORA-04068, as I would expect ORA-04068 to be at the top, e.g:

SQL> exec dbms_output.put_line(ora_04065_demo.this_creates_package_state)
BEGIN dbms_output.put_line(ora_04065_demo.this_creates_package_state); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "WILLIAM.ORA_04065_DEMO" has been invalidated
ORA-04065: not executed, altered or dropped package "WILLIAM.ORA_04065_DEMO"
ORA-06508: PL/SQL: could not find program unit being called: "WILLIAM.ORA_04065_DEMO"
ORA-06512: at line 1

I am not using package just standalone stored procedure. The only command that I have is TRUNCATE TABLE in each child. Does truncate invalidate a procedure? Because I have checked the objects in that schema, everything valid. Just happening a few times not all the time. — Jorge Flores, Dec 02 '18 at 17:17

ORA-04065: not executed, altered or dropped stored procedure

1 Answers1