Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E17126-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

B PL/SQL Name Resolution

This appendix explains how PL/SQL resolves ambiguous references to identifiers.

Topics:

See Also:

"Resolution of Names in Static SQL Statements"

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 subprogram, a schema, and so on. When a subprogram is recompiled, that association might change if objects were created or deleted.

A declaration or definition in an inner scope can hide another in an outer scope. In Example B-1, the declaration of variable client hides the definition of data type Client because PL/SQL names are not case-sensitive. In the inner block, the reference to the data type must be qualified with the label of outer block.

Example B-1 Resolving Global and Local Variable Names

BEGIN
  <<block1>>
  DECLARE
    TYPE Client IS RECORD ( 
      first_name VARCHAR2(20),
      last_name VARCHAR2(25)
    );
    TYPE Customer IS RECORD (
      first_name VARCHAR2(20),
      last_name VARCHAR2(25)
    );
  BEGIN
    DECLARE
      client  Customer;         -- declaration of variable client
      lead      block1.Client;  -- qualified reference to type Client
    BEGIN
      NULL;
    END;
  END;
END;
/

You can refer to data type Client by qualifying the reference with block label block1.

In these CREATE TYPE statements, the second statement generates a warning. Creating an attribute named manager hides the type named manager, so the declaration of the second attribute does not run correctly:

CREATE OR REPLACE TYPE manager AS OBJECT (dept NUMBER);
/
CREATE OR REPLACE TYPE person AS OBJECT (manager NUMBER, mgr manager)
    -- raises a warning;
/

Name Resolution

In ambiguous SQL statements, the names of columns take precedence over the names of local variables and formal parameters. For example, if a variable and a column with the same name are used in a WHERE clause, SQL considers both names to refer to the column.

Caution:

When a variable name is interpreted as a column name, data can be deleted unintentionally, as Example B-2 shows. Example B-2 also shows two ways to avoid this error.

Example B-2 Block Label for Name Resolution

DROP TABLE employees2;
CREATE TABLE employees2 AS
  SELECT last_name FROM employees;
 
-- Deletes everyone, because both LAST_NAMEs refer to the column:

BEGIN
  DELETE FROM employees2
  WHERE LAST_NAME = LAST_NAME;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
 

Result:

Deleted 107 rows.
 

Undo deletion:

ROLLBACK;
 

Avoid error by giving column and variable different names:

DECLARE
  last_name    VARCHAR2(10) := 'King';
  v_last_name  VARCHAR2(10) := 'King';
BEGIN
  DELETE FROM employees2
  WHERE last_name = v_last_name;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
 

Result:

Deleted 2 rows.
 

Undo deletion:

ROLLBACK;
 

Avoid error by qualifying variable with block name:

<<main>> -- Label block for future reference
DECLARE
  last_name    VARCHAR2(10) := 'King';
  v_last_name  VARCHAR2(10) := 'King';
BEGIN
  DELETE FROM employees2
  WHERE last_name = main.last_name;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
 

Result:

Deleted 2 rows.
 

Undo deletion:

ROLLBACK;
 

You can use a subprogram name to qualify references to local variables and formal parameters, as in Example B-3.

Example B-3 Subprogram Name for Name Resolution

DECLARE
  FUNCTION dept_name (department_id IN NUMBER)
    RETURN departments.department_name%TYPE
  IS
    department_name  departments.department_name%TYPE;
  BEGIN
    SELECT department_name INTO dept_name.department_name
      --   ^column              ^local variable
    FROM departments
    WHERE department_id = dept_name.department_id;
    --    ^column         ^formal parameter
    RETURN department_name;
  END dept_name;
BEGIN
  FOR item IN (
    SELECT department_id
    FROM departments
    ORDER BY department_name) LOOP
 
    DBMS_OUTPUT.PUT_LINE
      ('Department: ' || dept_name(item.department_id));
  END LOOP;
END;
/
 

Result:

Department: Accounting
Department: Administration
Department: Benefits
Department: Construction
Department: Contracting
Department: Control And Credit
Department: Corporate Tax
Department: Executive
Department: Finance
Department: Government Sales
Department: Human Resources
Department: IT
Department: IT Helpdesk
Department: IT Support
Department: Manufacturing
Department: Marketing
Department: NOC
Department: Operations
Department: Payroll
Department: Public Relations
Department: Purchasing
Department: Recruiting
Department: Retail Sales
Department: Sales
Department: Shareholder Services
Department: Shipping
Department: Treasury

Examples of Qualified Names and Dot Notation

During name resolution, the compiler can encounter various forms of references—unqualified names, qualified names, indexed components of collections, and so on. Example B-4 shows some of these.

Example B-4 Dot Notation for Qualifying Names

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

CREATE OR REPLACE PACKAGE BODY pkg1 AS
  FUNCTION f1 (p1 NUMBER) RETURN t1 IS
    n NUMBER;
  BEGIN
     n := m;             -- Unqualified variable name
     n := pkg1.m;        -- Variable name qualified by package name
     n := pkg1.f1.p1;    -- Parameter name qualified by function name,
                         --  which is qualified by package name
     n := v1.a;          -- Variable name followed by component name
     n := pkg1.v1.a;     -- Variable name qualified by package name
                         --  and followed by component name
     n := v2(10).a;      -- Indexed name followed by component name
     n := f1(10).a;      -- Function invocation followed by component name
     n := f2(10)(10).a;  -- Function invocation followed by indexed name
                         --  and followed by component name
     n := hr.pkg1.f2(10)(10).a;  -- Schema name, package name,
                                 -- function invocation, index, component name
     v1.a := p1;
     RETURN v1;
   END f1;

   FUNCTION f2 (q1 NUMBER) RETURN t2 IS
     v_t1 t1;
     v_t2 t2;
   BEGIN
     v_t1.a := q1;
     v_t2(1) := v_t1;
     RETURN v_t2;
   END f2;
END pkg1;
/

An outside reference to a private variable declared in a function body is not legal. For example, an outside reference to the variable n declared in function f1, such as hr.pkg1.f1.n from function f2, raises an exception.

Dot notation is used for identifying record fields, object attributes, and items inside packages or other schemas. When you combine these items, you might need to use expressions with multiple levels of dots, where it is not always clear what each dot refers to. Some of the combinations are:

How Name Resolution Differs in PL/SQL and SQL

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 database manipulation language (DML) statements than the PL/SQL rules.

For information about SQL naming rules, see Oracle Database SQL Language Reference.

What is 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. Capture is usually 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.

Topics:

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:

DROP TABLE tab1;
CREATE TABLE tab1 (col1 NUMBER, col2 NUMBER);
INSERT INTO tab1 (col1, col2) VALUES (100, 10);

DROP TABLE tab2;
CREATE TABLE tab2 (col1 NUMBER);
INSERT INTO tab2 (col1) VALUES (100);

CREATE OR REPLACE PROCEDURE proc AS
  CURSOR c1 IS
    SELECT * FROM tab1
    WHERE EXISTS (SELECT * FROM tab2 WHERE col2 = 10);
BEGIN
  OPEN c1;
  CLOSE c1;
END;
/

Add a column named col2 to table tab2:

ALTER TABLE tab2 ADD (col2 NUMBER);

Now procedure proc is invalid. At its next use, the database automatically recompiles it. 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.

Collections and ADTs are also vulnerable to inner capture. In the following example, the reference to hr.tab2.a resolves to attribute a of column tab2 in table tab1 through table alias hr, which is visible in the outer scope of the query:

CREATE OR REPLACE TYPE type1 AS OBJECT (a NUMBER);
/
DROP TABLE tab1;
CREATE TABLE tab1 (tab2 type1);
INSERT INTO tab1 (tab2) VALUES (type1(10));

DROP TABLE tab2;
CREATE TABLE tab2 (x NUMBER);
INSERT INTO tab2 (x) VALUES (10);

/* Alias tab1 with same name as schema name,
   a bad practice used here for illustration purpose.
   Note lack of alias in second SELECT statement. */

SELECT * FROM tab1 hr
WHERE EXISTS (SELECT * FROM hr.tab2 WHERE x = hr.tab2.a);

Result:

TAB2(A)
---------------
 
TYPE1(10)
 
1 row selected.

Suppose that you add a column named a to table hr.tab2, which appears in the inner subquery. When the query is processed, an inner capture occurs because the reference to hr.tab2.a resolves to column a of table tab2 in schema hr.

To avoid inner captures, follow the rules in "Avoiding Inner Capture in DML Statements". According to those rules, revise the preceding query as follows:

SELECT * FROM hr.tab1 p1
WHERE EXISTS (SELECT * FROM hr.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 had 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 need not 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:

Qualifying a reference with schema_name.table_name does not prevent inner capture if the statement refers to tables with columns of an Abstract Data Type (ADT). Such columns are vulnerable to additional inner capture situations. To minimize problems, the name-resolution algorithm includes these rules for the use of table aliases.

Topics:

Qualifying References to Attributes and Methods

To reference an attribute or method of a table, you must give the table an alias and use the alias to qualify the reference to the attribute or method.

CREATE OR REPLACE TYPE t1 AS OBJECT (x NUMBER);
/
DROP TABLE tb1;
CREATE TABLE tb1 (col1 t1); 

The references in the following INSERT statements do not need aliases, because they have no column lists:

BEGIN
  INSERT INTO tb1 VALUES ( t1(10) );
  INSERT INTO tb1 VALUES ( t1(20) );
  INSERT INTO tb1 VALUES ( t1(30) );
END;
/

The following references cause error ORA-00904:

UPDATE tb1 SET col1.x = 10 WHERE col1.x = 20;

UPDATE tb1 SET tb1.col1.x = 10 WHERE tb1.col1.x = 20;

UPDATE hr.tb1 SET hr.tb1.col1.x = 10 WHERE hr.tb1.col1.x = 20;

DELETE FROM tb1 WHERE tb1.col1.x = 10;

The following references, with table aliases, are correct:

UPDATE hr.tb1 t set t.col1.x = 10 WHERE t.col1.x = 20;

DECLARE
  y NUMBER;
BEGIN
  SELECT t.col1.x INTO y FROM tb1 t WHERE t.col1.x = 30;
END;
/

DELETE FROM tb1 t WHERE t.col1.x = 10;

Qualifying References to Row Expressions

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

CREATE OR REPLACE TYPE t1 AS OBJECT (x number);
/
DROP TABLE ot1;
CREATE TABLE ot1 OF t1;

BEGIN
  INSERT INTO ot1 VALUES (t1(10));
  INSERT INTO ot1 VALUES (20);
  INSERT INTO ot1 VALUES (30);
END;
/

The following references cause error ORA-00904:

UPDATE ot1 SET VALUE(ot1.x) = t1(20) WHERE VALUE(ot1.x) = t1(10);

DELETE FROM ot1 WHERE VALUE(ot1) = (t1(10));

The following references, with table aliases, are correct:

UPDATE ot1 o SET o = (t1(20)) WHERE o.x = 10;

DECLARE
  n_ref  REF t1;
BEGIN
  SELECT REF(o) INTO n_ref FROM ot1 o WHERE VALUE(o) = t1(30);
END;
/

DECLARE
  n t1;
BEGIN
  SELECT VALUE(o) INTO n FROM ot1 o WHERE VALUE(o) = t1(30);
END;
/

DECLARE
  n NUMBER;
BEGIN
  SELECT o.x INTO n FROM ot1 o WHERE o.x = 30;
END;
/

DELETE FROM ot1 o WHERE VALUE(o) = (t1(20));