0

I have a java file which calls a stored procedure on oracle which in turn calls n number of procedures/packages.

The problem is we seem to be getting ORA-6508 at random points of time. All the objects in the server are in valid state only.

       ORA-06508: PL/SQL: could not find program unit being called.

I checked the last_ddl time of the objects as well and it shows that its a few days ago. So there has been no recompilation or ddls on any of the objects to cause this error. Moreover, on the same day this error occurs intermittently. Some calls go through successfully and others don't on a particular day.

We have been using this environment for more than a year now and this behavior is a recent occurrence.

Is there any ORACLE server settings that could be the problem? Any suggestions are welcome since I've no idea how to proceed.

cumberdame
  • 41
  • 2
  • 9
  • did you update to 12.2 or something like that? on which version are you currently running? sure its still 11g? – Cee McSharpface Oct 24 '18 at 10:42
  • Is anything the procedure (or anything it calls) refers to being modified in the background, e.g. altering a table, dropping and recreating an objecet, etc.? In that case the dependant object could be invalidated and recompiled on next references, but `last_ddl_time` won't show that. If there is a package with state involved, a session which exists while that is happening might see that error ([among others](https://stackoverflow.com/a/19380746/266304)). – Alex Poole Oct 24 '18 at 10:49
  • @dlatikay No. Oracle has not been updated. – cumberdame Oct 24 '18 at 10:59
  • @AlexPoole No. There are no DDLs in the procedures that are called. Only inserts updates and deletes. And we have commit and rollback commands as well – cumberdame Oct 24 '18 at 11:00
  • Not necessarily within those procedures - anything they reference might be modified elsewhere. Is there *anything* with a more recent `created` or `last_ddl_time` in `dba_objects` (or `all_` if you can't see that)? You might also want to recursively go through `dba_dependencies`, or use [PL/Scope](https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_plscope.htm) to get more of a picture of what might be related. Also see if Java can report more of the error stack. – Alex Poole Oct 24 '18 at 11:03
  • @AlexPoole I checked all_objects and the latest ddl time available in that is 18-OCT-2018. I will go through dba_dependencies to see if I can spot anything. Thanks. – cumberdame Oct 24 '18 at 11:05

0 Answers0