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!

plsql procedure gets invalidated

Discussion in 'SQL PL/SQL' started by ashwanth, May 13, 2014.

  1. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem

    in my case


    Code (Text):
    procedure p1()--resides in db1
    is
    begin
    ....
    select col1 from table1@db2;

    ...
    end
    /
    for some reasons the procedure gets invalidated and the dblinks doesnt work and i get the following errors

    ORA-04052
    ORA-00604
    ORA-02019

    and i recompile all the invalid objects this seem to work.

    i need to figure why the proc in db1 gets invalidated?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It gets invalidated because the database links become invalid. Apparently you have network issues that affect connectivity to the remote databases; if the link is no longer valid then the procedures that use that link also become invalid.


    Fix your network/connectivity issues and the procedures should remain valid.
     
  3. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    ora-2019 indicates that db-link perhaps changed is enough to make the pocedure invalid read in other post.

    can REMOTE_DEPENDENCIES_MODE = SIGNATURE be used to overcome this problem[im using prod env]

    if there is no network/connectivity issues
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    1)
    Look at dependences of your procedure : select * from user_dependencies where name = 'p1'

    2) SELECT * FROM GLOBAL_NAME; ?
    select * from dual@db1; ?

    Additional link :
    http://docs.oracle.com/cd/B10501_01/server.920/a96540/sql_elements10a.htm#27814

     
  5. ashwanth

    ashwanth Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    80
    select * from user_dependencies where name = 'p1'

    has two non-existent in reference type column

    SELECT * FROM GLOBAL_NAME; ?-gives my current db which db1

    select * from dual@db1; ? gives X.

    also have a public synonym with name a p1
     
  6. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Ashwanth,

    In order to avoid recompiling the procedures dependent on remote objects (via dblinks), it is usually recommended to create views or synonyms on these objects. Even if the link fails, the procedure's status would remain valid; however, it will bomb out at run time with the same results you have now currently.

    Views are preferred to synonyms generally for the following reasons:
    - they allow us to see the structure of the remote object (columns / types) and
    - compilation of the local objects is faster

    You can try and let us know if it works in your scenario.

    Regards,
    Rajen.
    P.S: You still need to fix your network issues or else error will persist at execution time.