D
PL/SQL Name Resolution

This appendix explains how PL/SQL resolves references to names in potentially ambiguous SQL and procedural statements.

This appendix discusses the following topics:

What Is Name Resolution?
Various Forms of References
Name-Resolution Algorithm
Understanding Capture
Avoiding Capture
Accessing Attributes and Methods
Calling Subprograms and Methods
Name Resolution for SQL Versus PL/SQL

What Is Name Resolution?

During compilation, the PL/SQL compiler associates identifiers such as the name of a variable with an address (memory location), datatype, or actual value. This process is called binding. The association lasts through all subsequent executions until a recompilation occurs, which might cause a rebinding.

Before binding the names, PL/SQL must resolve all references to them in the compilation unit. This process is called name resolution. PL/SQL considers all names to be in the same namespace. So, one declaration or definition in an inner scope can hide another in an outer scope. In the following example, the declaration of variable client hides the definition of datatype Client because PL/SQL is not case sensitive except within string literals:

BEGIN 
   <<block1>> 
   DECLARE 
      TYPE Client IS RECORD (...);
      TYPE Customer IS RECORD (...);
   BEGIN 
      DECLARE 
         client Customer;      -- hides definition of type Client 
                               -- in outer scope 
         lead1  Client;        -- not allowed; Client resolves to 
the 
                               -- variable client 
         lead2  block1.Client; -- OK; refers to type Client
      BEGIN
         NULL; 
      END;
   END; 
END;

However, you can still refer to datatype Client by qualifying the reference with block label block1.

In the CREATE TYPE person1 statement below, the compiler resolves the second reference to manager as the name of the attribute you are trying to declare. In the CREATE TYPE person2 statement, the compiler resolves the second reference to manager as the name of the attribute you just declared. In both cases, the reference to manager generates an error because the compiler expects a type name.

CREATE TYPE manager AS OBJECT (dept NUMBER);
CREATE TYPE person1 AS OBJECT (manager manager);
CREATE TYPE person2 AS OBJECT (manager NUMBER, mgr manager);

Various Forms of References

During name resolution, the compiler can encounter various forms of references including simple unqualified names, dot-separated chains of identifiers, indexed components of a collection, and so on. Some examples of legal references follow:

CREATE PACKAGE pkg1 AS
   m NUMBER;
   TYPE t1 IS RECORD (a NUMBER);
   v1 t1;
   TYPE t2 IS TABLE OF t1 INDEX BY BINARY_INTEGER;
   v2 t2; 
   FUNCTION f1 (p1 NUMBER) RETURN t1;
   FUNCTION f2 (q1 NUMBER) RETURN t2;
END pkg1;

CREATE PACKAGE BODY pkg1 AS
   FUNCTION f1 (p1 NUMBER) RETURN t1 IS
      n NUMBER;
   BEGIN
      n := m;            -- (1) unqualified name
      n := pkg1.m;       -- (2) dot-separated chain of identifiers
                         --     (package name used as scope
                         --     qualifier followed by variable name)
      n := pkg1.f1.p1;   -- (3) dot-separated chain of identifiers
                         --     (package name used as scope
                         --     qualifier followed by function name
                         --     also used as scope qualifier
                         --     followed by parameter name)
      n := v1.a;         -- (4) dot-separated chain of identifiers
                         --     (variable name followed by
                         --     component selector)
      n := pkg1.v1.a;    -- (5) dot-separated chain of identifiers
                         --     (package name used as scope
                         --     qualifier followed by 
                         --     variable name followed by component
                         --     selector)
      n := v2(10).a;     -- (6) indexed name followed by component
                         --     selector
      n := f1(10).a;     -- (7) function call followed by component
                         --     selector
      n := f2(10)(10).a; -- (8) function call followed by indexing
                         --     followed by component selector
      n := scott.pkg1.f2(10)(10).a;
                         -- (9) function call (which is a dot-
                         --     separated chain of identifiers,
                         --     including schema name used as
                         --     scope qualifier followed by package
                         --     name used as scope qualifier
                         --     followed by function name)
                         --     followed by component selector
                         --     of the returned result followed
                         --     by indexing followed by component
                         --     selector
      n := scott.pkg1.f1.n;
                        -- (10) dot-separated chain of identifiers
                        --      (schema name used as scope qualifier
                        --      followed by package name also used
                        --      as scope qualifier followed by
                        --      function name also used as scope
                        --      qualifier followed by local
                        --      variable name)
      ...
   END f1;

   FUNCTION f2 (q1 NUMBER) RETURN t2 IS
   BEGIN
      ...
   END f2; 
END pkg1;

Name-Resolution Algorithm

Let us take a look at the name-resolution algorithm.

The first part of name resolution involves finding the basis. The basis is the smallest prefix to a dot-separated chain of identifiers that can be resolved by looking in the current scope, then moving outward to schema-level scopes.

In the previous examples, the basis for (3) pkg1.f1.p1 is pkg1, the basis for (4) scott.pkg1.f1.n is scott.pkg1, and the basis for (5) v1.a is v1. In (5), the a in v1.a is a component selector and resolves as field a of variable v1 because v1 is of type t1, which has a field called a.

If a basis is not found, the compiler generates a not declared error. If the basis is found, the compiler attempts to resolve the complete reference. If it fails, the compiler generates an error.

The length of the basis is always 1, 2, or 3. And, it can be 3 only inside SQL scope when the compiler resolves a three-part name as

schema_name.table_name.column_name

Here are more examples of bases:

variable_name
type_name
package_name
schema_name.package_name
schema_name.function_name
table_name
table_name.column_name
schema_name.table_name
schema_name.table_name.column_name

Finding the Basis

Now, let us look at the algorithm for finding the basis.

If the compiler is resolving a name in SQL scope (which includes everything in a DML statement except items in the INTO clause and schema-level table names), it first attempts to find the basis in that scope. If it fails, it attempts to find the basis in PL/SQL local scopes and at the schema level just as it would for names in non-SQL scopes.

Here are the rules for finding the basis in SQL scope when the compiler expects to find a column name:

Given one identifier, the compiler attempts to find a basis of length 1 using the identifier as an unqualified column name in one of the tables listed in any FROM clauses that are in scope, starting with the current scope and moving outward.
Given a of chain two identifiers, the compiler attempts to find a basis of length 2 using the identifiers as a column name qualified by a table name or table alias, starting with the current scope and moving outward.
Given a chain of three identifiers, the compiler attempts to find in each scope that it searches, starting with the current scope and moving outward, either
- a basis of length 3 using the three identifiers as a column name qualified by a table name qualified by a schema name, or
- a basis of length 2 using the first two identifiers as a column name of some user-defined type qualified by a table alias
Given a chain of four identifiers, the compiler attempts to find a basis of length 2, using the first two identifiers as a column name of some user-defined type qualified by a table alias, starting with the current scope and moving outward.

Once the compiler finds the basis as a column name, it attempts to resolve the complete reference by finding a component of the basis and so on depending upon the type of column name.

Here are the rules for finding the basis in SQL scope when the compiler expects to find a row expression (which is a table alias that can appear by itself; it can be used only with an object table and operator REF or VALUE, or in an INSERT or UPDATE statement for an object table):

Given one identifier, the compiler attempts to find a basis of length 1 as a table alias, starting with the current scope and moving outward. If the table alias does not correspond to an object table, the compiler generates an error.
Given a chain of two or more identifiers, the compiler generates an error.

If the name being resolved either

does not appear in SQL scope, or
appears in SQL scope but the compiler cannot find a basis for it in that scope

the compiler attempts to find the basis by searching all PL/SQL scopes local to the compilation unit, starting with the current scope and moving outward. If the name is found, the length of the basis is 1. If the name is not found, the compiler attempts to find the basis by searching for schema objects using the following rules:

First, the compiler attempts to find a basis of length 1 by searching the current schema for a schema object whose name matches the first identifier in the chain of identifiers. The schema object found might be a package specification, function, procedure, table, view, sequence, synonym, or schema-level datatype. If it is a synonym, the basis will be resolved as the base object designated by the synonym.
If the previous search fails, the compiler attempts to find a basis of length 1 by searching for a public synonym whose name matches the first identifier in the chain. If this succeeds, the basis will be resolved as the base object designated by the synonym.
If the previous search fails and there are at least two identifiers in the chain, the compiler attempts to find a basis of length 2 by searching for a schema object whose name matches the second identifier in the chain and which is owned by a schema whose name matches the first identifier in the chain.
If the compiler finds a basis as a schema object, it checks the privileges on the base object. If the base object is not visible, the compiler generates a not declared error because an insufficient privileges error would acknowledge the existence of the object, which is a security violation.
If the compiler fails to find a basis by searching for schema objects, it generates a not declared error.
If the compiler finds a basis, it attempts to resolve the complete reference depending on how the basis was resolved. If it fails to resolve the complete reference, the compiler generates an error.

Understanding Capture

When a declaration or type definition in another scope prevents the compiler from resolving a reference correctly, that declaration or definition is said to "capture" the reference. Usually this is the result of migration or schema evolution. There are three kinds of capture: inner, same-scope, and outer. Inner and same-scope capture apply only in SQL scope.

Inner Capture

An inner capture occurs when a name in an inner scope that once resolved to an entity in an outer scope, either

gets resolved to an entity in an inner scope, or
causes an error because the basis of the identifier chain got captured in an inner scope and the complete reference could not be resolved

If the situation was resolved without error in an inner scope, the capture might occur without your knowing. In the following example, the reference to col2 in the inner SELECT statement binds to column col2 in table tab1 because table tab2 has no column named col2:

CREATE TABLE tab1 (col1 NUMBER, col2 NUMBER);
CREATE TABLE tab2 (col1 NUMBER);
CREATE PROCEDURE proc AS
   CURSOR c1 IS SELECT * FROM tab1
      WHERE EXISTS (SELECT * FROM tab2 WHERE col2 = 10);
BEGIN
   ...
END;

In the last example, if you add a column named col2 to table tab2, as follows

ALTER TABLE tab2 ADD (col2 NUMBER);

then procedure proc is invalidated and recompiled automatically upon next use. However, upon recompilation, the col2 in the inner SELECT statement binds to column col2 in table tab2 because tab2 is in the inner scope. Thus, the reference to col2 is captured by the addition of column col2 to table tab2.

The use of collections and object types allows for more inner capture situations. In the following example, the reference to s.tab2.a resolves to attribute a of column tab2 in table tab1 through table alias s, which is visible in the outer scope of the query:

CREATE TYPE type1 AS OBJECT (a NUMBER);
CREATE TABLE tab1 (tab2 type1);
CREATE TABLE tab2 (x NUMBER);
SELECT * FROM tab1 s  -- alias with same name as schema name
   WHERE EXISTS (SELECT * FROM s.tab2 WHERE x = s.tab2.a);
                               -- note lack of alias

In the last example, suppose you add a column named a to table s.tab2, which appears in the inner subquery. When the query is processed, an inner capture occurs because the reference to s.tab2.a resolves to column a of table tab2 in schema s. You can avoid inner captures by following the rules given in "Avoiding Capture". According to those rules, you should revise the query as follows:

SELECT * FROM s.tab1 p1 
   WHERE EXISTS (SELECT * FROM s.tab2 p2 WHERE p2.x = p1.tab2.a);

Same-Scope Capture

In SQL scope, a same-scope capture occurs when a column is added to one of two tables in the same scope, and that column has the same name as a column in the other table. Consider the following query (and refer to the previous example):

PROCEDURE proc IS 
    CURSOR c1 IS SELECT * FROM tab1, tab2 WHERE col2 = 10;

In this example, the reference to col2 in the query binds to column col2 in table tab1. If you add a column named col2 to table tab2, the query compiles with errors. Thus, the reference to col2 is captured by an error.

Outer Capture

An outer capture occurs when a name in an inner scope, which once resolved to an entity in an inner scope, gets resolved to an entity in an outer scope. Fortunately, SQL and PL/SQL are designed to prevent outer captures.

Avoiding Capture

You can avoid inner capture in DML statements by following these rules:

Specify an alias for each table in the DML statement.
Keep table aliases unique throughout the DML statement.
Avoid table aliases that match schema names used in the query.
Qualify each column reference with the table alias.

Qualifying a reference with <schema-name>.<table-name> does not prevent inner capture if the DML statement references tables that have columns of a user-defined object type.

Accessing Attributes and Methods

Columns of a user-defined object type allow for more inner capture situations. To minimize problems, the name-resolution algorithm includes the following rules:

All references to attributes and methods must be qualified by a table alias. So, when referencing a table, if you reference the attributes or methods of an object stored in that table, the table name must be accompanied by an alias. As the following examples show, column-qualified references to an attribute or method are not allowed if they are prefixed with a table name:

CREATE TYPE t1 AS OBJECT (x NUMBER);  
CREATE TABLE tb1 (col t1);  
SELECT col.x FROM tb1;                    -- not allowed 
SELECT tb1.col.x FROM tb1;                -- not allowed 
SELECT scott.tb1.col.x FROM scott.tb1;    -- not allowed 
SELECT t.col.x FROM tb1 t;
UPDATE tb1 SET col.x = 10;                -- not allowed 
UPDATE scott.tb1 SET scott.tb1.col.x=10;  -- not allowed 
UPDATE tb1 t set t.col.x = 1;
DELETE FROM tb1 WHERE tb1.col.x = 10;     -- not allowed 
DELETE FROM tb1 t WHERE t.col.x = 10;

Row expressions must resolve as references to table aliases. You can pass row expressions to operators REF and VALUE, and you can use row expressions in the SET clause of an UPDATE statement. Some examples follow:

CREATE TYPE t1 AS OBJECT (x number);
CREATE TABLE ot1 OF t1;                       -- object table
SELECT REF(ot1) FROM ot1;                     -- not allowed
SELECT REF(o) FROM ot1 o; 
SELECT VALUE(ot1) FROM ot1;                   -- not allowed
SELECT VALUE(o) FROM ot1 o; 
DELETE FROM ot1 WHERE VALUE(ot1) = (t1(10));  -- not allowed
DELETE FROM ot1 o WHERE VALUE(o) = (t1(10));
UPDATE ot1 SET ot1 = ...                      -- not allowed
UPDATE ot1 o SET o = ....

The following ways to insert into an object table are legal and do not require an alias because there is no column list:

INSERT INTO ot1 VALUES (t1(10)); -- no row expression
INSERT INTO ot1 VALUES (10);     -- no row expression

Calling Subprograms and Methods

In calls to a parameterless subprogram, an empty parameter list is optional, as the following examples show:

CREATE FUNCTION func1 RETURN NUMBER AS
BEGIN
   RETURN 10;
END;

CREATE PACKAGE pkg2 AS
   FUNCTION func1 RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES(func1,WNDS,RNDS,WNPS,RNPS);
END pkg2;

CREATE PACKAGE BODY pkg2 AS
   FUNCTION func1 RETURN NUMBER IS 
   BEGIN 
      RETURN 20;
   END;
END pkg2;

SELECT func1 FROM dual;
SELECT func1() FROM dual;

SELECT pkg2.func1 FROM dual;
SELECT pkg2.func1() FROM dual;

DECLARE
   x NUMBER;
BEGIN
   x := func1;
   x := func1();
   SELECT func1 INTO x FROM dual;
   SELECT func1() INTO x FROM dual;
   SELECT pkg2.func1 INTO x FROM dual;
   SELECT pkg2.func1() INTO x FROM dual;
END;

In calls to a parameterless method, an empty parameter list is optional within PL/SQL scopes. However, an empty parameter list is required within SQL scopes. Some examples follow:

CREATE TYPE type1 AS OBJECT (
   a NUMBER,
   MEMBER FUNCTION f RETURN number,
   PRAGMA RESTRICT_REFERENCES(f,WNDS,RNDS,WNPS,RNPS)
);

CREATE TYPE BODY type1 AS
   MEMBER FUNCTION f RETURN number IS
   BEGIN
      RETURN 1;
   END; 
END;

CREATE TABLE tab1 (col1 type1);
INSERT INTO tab1 VALUES (type1(10));

SELECT x.col1.f FROM tab1 x;   -- not allowed
SELECT x.col1.f() FROM tab1 x;

DECLARE 
   n NUMBER;
   y type1;
BEGIN 
   /* In PL/SQL scopes, an empty parameter list is optional. */
   n := y.f; 
   n := y.f(); 
   /* In SQL scopes, an empty parameter list is required. */
   SELECT x.col1.f INTO n FROM tab1 x;    -- not allowed
   SELECT x.col1.f() INTO n FROM tab1 x;
   SELECT y.f INTO n FROM dual;           -- not allowed
   SELECT y.f() INTO n FROM dual;
END;

Name Resolution for SQL Versus PL/SQL

The name-resolution rules for SQL and PL/SQL are similar. However, there are a few minor differences, which are not noticeable if you follow the capture avoidance rules.

For compatibility, the SQL rules are more permissive than the PL/SQL rules. That is, the SQL rules, which are mostly context sensitive, recognize as legal more situations and DML statements than the PL/SQL rules do.

D PL/SQL Name Resolution