Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit re-compile, sometimes the package on B that should also implicitly re-compile goes invalid. The error it shows is PLS-00907:

 Cause: PL/SQL is unable to find and load a library unit that was
        previously available.  This typically happens when you try to
        load a unit which references another library unit that is
        non-existent or invalid.
Action: Provide the needed library unit.

The strange thing is that the package on A is valid and can be recompiled successfully, yet recompiling the package on B is still unsuccessful. It is only when we make an inconsequential change to the package on A that the package on B will then compile successfully. This problem has been occurring every few weeks for the past several months. It may have started when we upgraded B from 11.1 to 11.2.

The database link on B is a private database link using a specific username/password to connect to A. The remote_dependencies_mode parameter is set to SIGNATURE on both databases.

This problem is similar, but not the same as MOS Bug 9719541, and Doc ID 166680.1. Anyone have any idea what could be wrong?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

It is obviously a bug, so should go to Oracle support. As a workaround, are you able to switch that call on the 11gR2 to dynamic SQL to avoid the hard-coded dependency.

You might be able to do that through a wrapper package on the 11g side. Rather than

11g package calls 10g package

it goes

11g package calls 11g wrapper and 11g wrapper (dynamically) calls 10g package.
share|improve this answer
    
I have opened an SR with Oracle, but based on the cases I have opened recently they won't help much since I can't give them a reproducible test case. –  Leigh Riffel Jan 28 '11 at 20:53
    
Your workaround would be useful if the problem were more frequent or occurred to a more critical piece of code. We may use it if support isn't forthcoming. –  Leigh Riffel Jan 28 '11 at 20:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.