1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Existing state of the package -invalidated

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Dec 14, 2017.

Tags:
  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,

    I am executing one package from Linux shell script.This will invoke JAVA application, from that Database procedure will gets executed.

    But when I am running continuously the same job, some time following error is coming ,but
    some time it executing successfully.

    When I am running the same procedure complete from the DATABASE, always its executing successfully.

    Unable to find out under which scenarios,existing STATE OF PACKAGE is becoming INVALIDATE


    ORA-04061: existing state of package "MAIN.PKG_ABCD_XYZ_HANDOFF" has been invalidated
    ORA-04065: not executed, altered or dropped package "MAIN.PKG_ABCD_XYZ_HANDOFF"
    ORA-06508: PL/SQL: could not find program unit being called: "MAIN.PKG_ABCD_XYZ_HANDOFF"

    Please help me to understand on this .

    Thanks
    Revathi Thirunagari
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    For a start :

    SELECT * FROM user_errors;

    Your package uses dblink ?

    if there is a suspicion that some session changes the package , then you can view dba_audit_trail.
     
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi krasnoslobodtsev_si
    Thanks for interest to solve the issue .But there are No Errors & i am not using any DB Links .
    My Package contains DDL with execute immediate.

    Thanks
    Revathi.T
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    770
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    You re-create package from of dynamic -sql and later to calling it in application ?

    It's problem can will be solved a few paths ::

    1) if you have opportunity, then rewrite the section code with dynamic creation of package.That is not good practice .

    2) Otherwise , create on exception handler ora-04061 and here make re-call your functional of the element.
     
    Last edited: Dec 20, 2017