B How PL/SQL Resolves Identifier Names

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);
INSERT INTO tab1 VALUES (100, 10);
CREATE TABLE tab2 (col1 NUMBER);
INSERT INTO tab2 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;
/

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 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 TYPE type1 AS OBJECT (a NUMBER);
/
CREATE TABLE tab1 (tab2 type1);
INSERT INTO tab1 VALUES ( type1(10) );
CREATE TABLE tab2 (x NUMBER);
INSERT INTO tab2 VALUES ( 10 );

-- in the following,
-- alias tab1 with same name as schema name,
-- which is not a good practice
-- but is used here for illustration purpose
-- note lack of alias in second SELECT
SELECT * FROM tab1 hr
   WHERE EXISTS (SELECT * FROM hr.tab2 WHERE x = hr.tab2.a);

In the preceding example, you might 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. You can avoid inner captures by following the rules given in "Avoiding Inner Capture in DML Statements". According to those rules, revise the 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 do not need to take any action to avoid this condition.

Qualifying References to Attributes and Methods

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 (col1 t1); 

BEGIN
-- following inserts are allowed without an alias 
-- because there is no column list
  INSERT INTO tb1 VALUES ( t1(10) );
  INSERT INTO tb1 VALUES ( t1(20) );
  INSERT INTO tb1 VALUES ( t1(30) );
END;
/
BEGIN
  UPDATE tb1 SET col1.x = 10 
  WHERE col1.x = 20; -- error, not allowed
END;
/
BEGIN
  UPDATE tb1 SET tb1.col1.x = 10 
  WHERE tb1.col1.x = 20; -- not allowed
END;
/
BEGIN
  UPDATE hr.tb1 SET hr.tb1.col1.x = 10 
  WHERE hr.tb1.col1.x = 20; -- not allowed
END;
/
BEGIN -- following allowed with table alias
  UPDATE hr.tb1 t set t.col1.x = 10 
  WHERE t.col1.x = 20;
END;
/
DECLARE
  y NUMBER;
BEGIN -- following allowed with table alias
  SELECT t.col1.x INTO y FROM tb1 t 
  WHERE t.col1.x = 30;
END;
/
BEGIN
  DELETE FROM tb1 
  WHERE tb1.col1.x = 10; -- not allowed
END;
/
BEGIN -- following allowed with table alias
  DELETE FROM tb1 t 
  WHERE t.col1.x = 10;
END;
/

Qualifying References to Row Expressions

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;

BEGIN
-- following inserts are allowed without an alias 
-- because there is no column list
  INSERT INTO ot1 VALUES ( t1(10) );
  INSERT INTO ot1 VALUES ( 20 );
  INSERT INTO ot1 VALUES ( 30 );
END;
/
BEGIN
  UPDATE ot1 SET VALUE(ot1.x) = t1(20) 
  WHERE VALUE(ot1.x) = t1(10); -- not allowed
END;
/
BEGIN -- following allowed with table alias
  UPDATE ot1 o SET o = (t1(20)) WHERE o.x = 10;
END;
/
DECLARE
  n_ref REF t1;
BEGIN -- following allowed with table alias
  SELECT REF(o) INTO n_ref FROM ot1 o 
  WHERE VALUE(o) = t1(30); 
END;
/
DECLARE
  n t1;
BEGIN -- following allowed with table alias
  SELECT VALUE(o) INTO n FROM ot1 o 
  WHERE VALUE(o) = t1(30); 
END;
/
DECLARE
  n NUMBER;
BEGIN -- following allowed with table alias
  SELECT o.x INTO n FROM ot1 o WHERE o.x = 30; 
END;
/
BEGIN
  DELETE FROM ot1 
  WHERE VALUE(ot1) = (t1(10)); -- not allowed
END;
/
BEGIN -- folowing allowed with table alias 
  DELETE FROM ot1 o 
  WHERE VALUE(o) = (t1(20));
END;
/