Unable to compile a package calling a package procedure in different schema
ArnaudAug 19 2011 — edited Aug 19 2011Hello,
I'm unable to compile a package referencing a package located in another schema. I get the message: PLS-00201: identifier 'pkg1' must be declared
Facts:
0. I'm running Oracle DB 10.2.0.4.0 Enterprise Edition
1. There is one schema ('schema1') containing a package ('pkg1').
2. This package 'pkg1' has a public synonym ('pkg1' as well).
3. The EXECUTE grant is given to a role ('role1').
4. There is another schema 'schema2', which is granted the role 'role1' (and set as default).
5. This schema 'schema2' contains a package ('pkg2').
6. This package 'pkg2' calls a procedure of 'pkg1'.
7. When compiling 'pkg2', I get error message saying 'pkg1' does not exist.
Of course, if I execute 'GRANT EXECUTE ON pkg1 TO PUBLIC' or 'GRANT EXECUTE ON pkg1 TO schema2', I can compile the package 'pkg2'. But I don't want this.
And what's weird is that, connected as 'schema2', I can execute the statement 'execute pkg1.proc1' without any trouble. So I guess the role setup is okay.
What would let me compile 'pkg2' properly?
Regards,
Arnaud