cancel
Showing results for 
Search instead for 
Did you mean: 

Package Discarded and Invalidated:SAP PI JDBC error

former_member216353
Participant
0 Kudos

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:

  1. Checked with DBA team to see if the relevant packages are in “INVALID”or “Un-compiled” state. The answer was NO.
  2. Stopping and Starting the receiver channels did resolve the issue initially but today again we have the same error.
  3. Checked with DB Dev team as well,to see if there were any deployments/installations done at their side, which might have an impact on the stored procedures/packages. The answer was NO.
  4. When the packages or Stored Procedure are run on the DB there is no issue.   

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

Accepted Solutions (0)

Answers (3)

Answers (3)

iaki_vila
Active Contributor
0 Kudos

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.

rajasekhar_reddy14
Active Contributor
0 Kudos

Hi Younus,

Could you past your JDBC target XML here.

Probably SP name not correct  or it is not calling right way.

Regards,

Raj

nabendu_sen
Active Contributor
0 Kudos

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