on 04-24-2013 2:29 PM
Hi all,
For a particular interface (ECC-->SAP PI-->Oracle DB).With receiver JDBC adapter, we are invoking Stored Procedures at DB for updating the interface data. We have been facing issues, where the receiver JDBC adapter says “
: ORA-04068: existing state of packages has been discarded,ORA-04061: existing state of package body "DB.PackageName.StoredProcName" has been invalidated ORA-04065: not executed, altered or dropped package body " DB.PackageName.StoredProcName" ORA-06508: PL/SQL: could not find program
unit being called: " DB.PackageName.StoredProcName" ORA-06512: at line 1.
Steps taken till now:
We have observed this issue to be intermittent. If any of you have encountered this before or can throw some light on how to get this resolved,it would be appreciated.
Thank you!!
regards,
Younus
Hi Mohammend,
As far as i know, this problem appears when the PL/SQL Body code contains global variables.
Thoes seasons that had executed that package before, if they come to execute it before the package has been recomiled they will get the ORA-4068 error.
Inside the DB enviroment with the compiling there are no further problems but if any web application is using the connection pool that it get before it will stay blocked, it will impossible to use any season of the pool and the unique solution is to RESTART the web application, in our case the PI.
You can avoid these situations with the follow advices:
- Don't use global variables in the PL/SQL code
- If the package is use by web application, the DB progammer must design the package thinking in one call enviroment and not in a season. You can assure that the global variables can't be used further one package call, with the "PRAGMA SERIALLY_REUSABLE" in the specification and in the body, with this the packag will be restarted every time that packcage will be called.
Hope this helps.
Regards.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Younus,
Could you past your JDBC target XML here.
Probably SP name not correct or it is not calling right way.
Regards,
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Younus,
Have you maintained correct structure for Stored Procedure as mentioned by SAP? Can you check with any other tool whether it can successfully execute the SP like Toad. Check the below links:
<StatementName5>
<storedProcedureName action=” EXECUTE”>
<table>realStoredProcedureeName</table>
<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName >
</StatementName5>
</root>
http://scn.sap.com/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures
http://zscribble.blogspot.com/2009/04/execute-stored-procedure-or-function-in.html
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.