D How PL/SQL Resolves Identifier Names

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

This appendix contains these topics:

What Is Name Resolution?
Examples of Qualified Names and Dot Notation
Differences in Name Resolution Between SQL and PL/SQL
Understanding Capture
Avoiding Inner Capture in DML Statements
Qualifying References to Object Attributes and Methods
Calling Parameterless Subprograms and Methods
Name Resolution for SQL Versus PL/SQL

What Is Name Resolution?

During compilation, the PL/SQL compiler determines which objects are associated with each name in a PL/SQL subprogram. A name might refer to a local variable, a table, a package, a procedure, a schema, and so on. When a subprogram is recompiled, that association might change if objects have been created or deleted.

A 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 names are not case sensitive:

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;

You can still refer to datatype Client by qualifying the reference with block label block1.

In the following set of CREATE TYPE statements, the second statement generates an error. Creating an attribute named MANAGER hides the type named MANAGER, so the declaration of the second attribute does not work.

CREATE TYPE manager AS OBJECT (dept NUMBER);
/
CREATE TYPE person AS OBJECT (manager NUMBER, mgr manager);
/

Examples of Qualified Names and Dot Notation

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. For example:

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;

Differences in Name Resolution Between SQL and PL/SQL

When the PL/SQL compiler processes a SQL statement, such as a DML statement, it uses the same name-resolution rules as SQL. For example, for a name such as SCOTT.FOO, SQL matches objects in the SCOTT 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 SCOTT.FOO, PL/SQL searches first for packages, types, tables, and views named SCOTT in the current schema, then for objects in the SCOTT schema.

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 no longer refers to an entity in an outer scope:

The name might now resolve to an entity in an inner scope.
The program might cause an error, if some part of the identifier is captured in an inner scope and the complete reference cannot be resolved.

If the reference points to a different but valid name, you might not know why the program is acting differently.

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 preceding example, if you add a column named col2 to table tab2:

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.

Using collections and object types can cause 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 preceding example, you might 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 Inner Capture in DML Statements". 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 used in a join, so that the same column name exists in both tables. Previously, you could refer to that column name in a join query. To avoid an error, now you must qualify the column name with the table name.

Outer Capture

An outer capture occurs when a name in an inner scope, which once resolved to an entity in an inner scope, is resolved to an entity in an outer scope. SQL and PL/SQL are designed to prevent outer captures. You do not need to take any action to avoid this condition.

Avoiding Inner Capture in DML Statements

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 statement refers to tables with columns of a user-defined object type.

Qualifying References to Object 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. 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 allowed 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 Parameterless Subprograms and Methods

If a subprogram does not take any parameters, you can include an empty set of parentheses or omit the parentheses, both in PL/SQL and in functions called from SQL queries.

For calls to a method that takes no parameters, an empty set of parentheses is optional within PL/SQL scopes but required within SQL scopes.

Name Resolution for SQL Versus PL/SQL

The name-resolution rules for SQL and PL/SQL are similar. You can avoid the few minor differences 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.