Oracle errors (ORA-06508 and ORA-04061) when attempting to run job...

[slicendice]

I have a package that compiles and works fine - no issues or errors and the package status is VALID. There is a main function in the package and this in turn calls various other functions within the package as part of the processing. All functions are fully internal to the package and no reference is made to anything external to the package (e.g. other packages, functions, procedures, etc). The package makes updates to some tables, whilst inserting into and deleting from others. Nothing too complicated really...

I can run the main package function from SQL*Plus without any issue using the following:

EXECUTE ctl.pck_mobile_working.pro_process_updates;

User CTL owns the package but I'm running it as user TEST (which has had execute permission on PCK_MOBILE_WORKING granted to it). Running this causes no errors and it does exactly what it should.

However, I need to set up a job that runs the function and that's where the problem is occurring. I can create a job OK (using exactly the same command that works fine from the command line, as above) but every single time I try and run it, it fails with:

ORA-12012: error on auto execute of job 145
ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package "CTL.PCK_MOBILE_WORKING" has been invalidated
ORA-04065: not executed, altered or dropped package "CTL.PCK_MOBILE_WORKING"
ORA-06508: PL/SQL: could not find program unit being called: "CTL.PCK_MOBILE_WORKING"
ORA-06512: at line 1

I've tried dropping and re-creating the job, flushing the cache, disconnecting and re-connecting to the database - nothing seems to work. I even tried creating a separate procedure (called PRO_PROCESS_MOBILE_UPDATES) that simply calls the package function and then setting up a job for this procedure - but same result:

ORA-12012: error on auto execute of job 146
ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package "CTL.PCK_MOBILE_WORKING" has been invalidated
ORA-04065: not executed, altered or dropped package "CTL.PCK_MOBILE_WORKING"
ORA-06508: PL/SQL: could not find program unit being called: "CTL.PCK_MOBILE_WORKING"
ORA-06512: at "CTL.PRO_PROCESS_MOBILE_UPDATES", line 3
ORA-06512: at line 1

Can anyone suggest anything else, as I'm really struggling to get past this?

Many thanks...

 

[slicendice]

Well, just in case anyone's interested, a restart of the database made this issue go away! I have no idea why and it's a little disconcerting, as I really don't want this to just start happening again - because once it's installed at a customer site, it won't be possible to just shut the database down willy nilly. So if anyone has any ideas...I'm all ears!