I have some question about name resolution in PL/SQL. First, I need to describe situation, because it is quite complicated.
There are three schemas: A1, B1 and O. A1 and B1 are containing proprietary software and I cannot modify objects there (but I can modify configuration tables, so in this way I can call any package from anyhere). O schema is my own schema. A1 and B1 schemas are in one "module", there are also A2 and B2 schemas in second "module" and AN and BN in n-th "module".
In normal situation, A1 schema contains synonym to package B1.PKG (on B1 schema) and uses it to call B1.PKG with DEFINER rights. The same for every "module".
I need to extend some functionality of B.PKG by buliding some kind of "wrapper" on my own O schema. It is only one MY_PKG and should be used by every "module". In this place I need some kind of magic :)
I want to place package MY_PKG with AUTH_ID CURRENT_USER clause on my O schema and use A1..AN schema to call it. Package MY_PKG have to call package placed on one of B1..BN schemas, so I need to create synonym on O schema for package, for example, B1.PKG just to compile the MY_PKG package.
Now, I call MY_PKG package from A1 schema and I expect MY_PKG will use synonym to B1.PKG placed on A1 schema, instead of using synonym on my O schema. That's not happening. MY_PKG still uses synonym on O schema, so it ruins whole concept :)
Actually, I use some set of packages that already work properly in that way, but there is some difference. In my other packages I use SQL and synonyms to tables and views placed on _one_ A schema. I use those synonyms only for compiling. When packages are calling from A1..AN schemas, they use proper objects on proper A1..AN schema. In this case I try to do the same, but with package instead of tables and view. I find it difficult probably because SQL and PLSQL resolves names in other way.
Here is the question, is it possible to make such thing as decribed above? Of course, I can use execute immediate to run proper packages directly from O schema, but I don't want :)
Sorry for my bad english, I hope you understand whole concept.
DB version: 11.2.0.3