B PL/SQL Name Resolution

Name Resolution

In ambiguous SQL statements, the names of database 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.

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;
/
 

Deleted 107 rows.
 

ROLLBACK;
 

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;
/
 

Deleted 2 rows.
 

ROLLBACK;
 

<<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;
/
 

Deleted 2 rows.
 

ROLLBACK;
 

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

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)
   LOOP
     DBMS_OUTPUT.PUT_LINE
       ('Department: ' || dept_name(item.department_id));
   END LOOP;
END;
/
 

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

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
  NULL;
END;
/

Add a column named col2 to table tab2:

ALTER TABLE tab2 ADD (col2 NUMBER);

Now procedure proc is invalid and will be 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.

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 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 need not take any action to avoid this condition.

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 cause error ORA-00904:

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));