Oracle: disambiguate between table and schema name

Question

Suppose I have schemas A and B.

In schema A I would like to call package X in schema B. However, there exists a package B in schema A.

A:
    package B
B:
    package X

When I call from schema A:

begin b.x.foo(); end

it looks for procedure X in package B, i.e. A.B.X(), and gets an error.

How can I fully qualify the call to force B to be considered a schema name?

update:

• It does seem there's no way to scope the reference to refer to b.x.foo.
• CREATE SYNONYM B_X for B.X works. B_X.foo() calls the procedure in schema B.

Answer 1

I don't think you can. From the PL/SQL User's Guide:

"The name resolution rules for PL/SQL and SQL are similar. You can avoid the few differences if you follow the capture avoidance rules. For compatibility, the SQL rules are more permissive than the PL/SQL rules. SQL rules, which are mostly context sensitive, recognize as legal more situations and DML statements than the PL/SQL rules.

• PL/SQL uses the same name-resolution rules as SQL when the PL/SQL compiler processes a SQL statement, such as a DML statement. For example, for a name such as HR.JOBS, SQL matches objects in the HR schema first, then packages, types, tables, and views in the current schema.
• PL/SQL uses a different order to resolve names in PL/SQL statements such as assignments and procedure calls. In the case of a name HR.JOBS, PL/SQL searches first for packages, types, tables, and views named HR in the current schema, then for objects in the HR schema."

The second bullet above applies. Since the object "B" exists in schema A, that's what the reference resolves to.

Answer 2

Hi Mark,

I agree with DCookie, this is a normal scoping problem. If you're in this situation though, one way to solve the issue would be to change the CURRENT_SCHEMA:

SQL> exec b.x.foo;

begin b.x.foo; end;

ORA-06550: line 2, column 9:
PLS-00302: component 'X' must be declared
ORA-06550: line 2, column 7:
PL/SQL: Statement ignored

SQL> alter session set current_schema=b;

Session altered

SQL> exec b.x.foo;

PL/SQL procedure successfully completed