Geek Answers Handbook

Oracle: disambiguate between table and schema name

Suppose I have circuits A and B

In Scheme A I would like to call package X in Schema B However, in scheme A there is package B

 A: package B B: package X

When I call from scheme A:

 begin bxfoo(); end

he searches for procedure X in package B , i.e. ABX() , and receives an error message.

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

update:

  • It seems that there is no way to cover the link for the bxfoo .
  • CREATE SYNONYM B_X for BX working. B_X.foo() calls the procedure in B_X.foo() B.
+7
oracle plsql
source share
2 answers

I do not think you can. From the PL / SQL User Guide:

"Name resolution rules for PL / SQL and SQL are similar. You can avoid several differences if you follow capture prevention rules. For compatibility, SQL rules are more permissive than PL / SQL rules. SQL rules that are mostly context sensitive will be recognized as more legitimate situations and DML statements than PL / SQL rules.

  • PL / SQL uses the same name resolution rules as SQL when the PL / SQL compiler processes an SQL statement such as a DML statement. For example, for a name such as HR.JOBS, SQL first maps the objects in the HR schema, and then the 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 HR.JOBS, PL / SQL first looks for packages, types, tables, and views with the name HR in the current schema, and then for objects in the HR schema.

The second bullet is used. Since the object "B" exists in Scheme A, that link decides.

+7
source share

I agree with DCookie , this is a normal problem. If you are in this situation, although one way to solve the problem is to change CURRENT_SCHEMA :

 SQL> exec bxfoo; begin bxfoo; 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 bxfoo; PL/SQL procedure successfully completed
+3
source share

More articles:


All Articles