Name Resolution – Inner Capture

When the PL/SQL compiler compiles code it runs a process called “Name Resolution”. Here Oracle resolves which objects(table, variable etc) are associated with each name in a PL/SQL namespace. But there is one thing that the compiler can’t do for you, Inner capture checking.

As defined by Oracle: “Inner capture occurs when a name in an inner scope, which had resolved to an item in an outer scope, now either resolves to an item in an inner scope or cannot be resolved.”

So let’s make an example of this.

rem create table t1 
create table t1 (col1 number, col2 number);
insert into t1 (col1, col2) values (1, 2);

rem create table t2 
create table t2 (col1 number);
insert into t2 (col1) values (1);

rem create sp
create or replace procedure p1
as
 cursor c1 is
 select * from t1
 where col1 in (select col1 from t2 where col2=1);
begin
 open c1;
 close c1;
end p1;

So I created 2 tables. One with 2 columns, one with 1 column. Then I create a stored procedure which will compile fine. But if we look at SQL of the inner scope we know that t2 don’t have the column col2. Which means that Oracle will implicit interpret that as t1.col2 as derived from the outer scope.

When writing SELECT, SELECT INTO and DML statements there are three rule of thumbs.

• Unique alias for each table in the statement.
• Do not specify a the schema name as table alias for that schema that owns an item referenced in the statement.
• Qualify each column reference in the statement with the appropriate table alias.

Following this, your code will be easier to follow, but also avoid inner capture.

Over & Out