Q
Problem solve Get help with specific problems with your technologies, process and projects.

Package does not exist after batch job runs

A developer batch job runs every Wednesday at 8 pm. It calls a package and runs for about an hour. The package exists for the whole week until this batch job is run, and come the next morning, when it's called again by other processes during the day, it sends out an error saying that it does not exist.

A developer batch job runs every Wednesday at 8 pm. It calls a package and runs for about an hour. The package exists for the whole week until this batch job is run, and come the next morning, Thursday, when it's called again by other processes during the day, it sends out an error saying that it does not exist. The developer swears that he doesn't see the package as invalid as he has checked when the error occurs and it is dropped by something or someone. This has now happened two weeks in a row. I cannot find it in the redo log as a dropped object for some reason and have exhausted most avenues that I know on how to look for this. The developer has to re-create it using the source code. Can you suggest what else I could try? The error he gets is:
ERROR at line 1: ORA-04068: existing state of packages has been discarded 
ORA-04067: not executed, package body "WRAPSUPP.PMM_TRANSACTIONS" does not exist 
ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1

It seems to me that someone is dropping your package and you'd like to find out who it is. I'd turn on auditing for this and then I'd look at the audit trail (DBA_AUDIT_TRAIL) to see who the culprit is.

AUDIT DROP PACKAGE  ON WRAPSUPP.PMM_TRANSACTIONS WHENEVER SUCCESSFUL;
Once you know who is dropping the package, you can take appropriate actions to reverse this course of action.

Alternatively, you can create a trigger which stops the DROP PACKAGE from occurring.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close