oracle - SQL column name same as PL/SQL variable name - How can this be done in a select statement?

Question

Suppose I have a table:

create table foo (
  col_1     number;
  col_2     number;
);

Then I have the following code

declare
   col_1    number;
   col_2    number;
begin
   col_1 := 1;
   select col_2 into col_2 from foo where col_1 = col_1;
end;

Of course this will not work as expected. How can one make it work without the need to change the variable names?

Answer 1

You can if you're liberal enough of your definition of "without the need to change the variable names". Reading the fabulous PL/SQL Name Resolution says:

If an identifier is declared in a named PL/SQL unit, you can qualify its simple name (the name in its declaration) with the name of the unit (block, subprogram, or package), using this syntax:

unit_name.simple_identifier_name

The following example will print 20 as expected:

create table foo (a number, b number);

insert into foo values(1, 10);
insert into foo values(2, 20);
insert into foo values(3, 30);

begin
  <<bar>>
  declare
    a number;
    b number;
  begin
    a := 2;
    select b into bar.b from foo where a = bar.a;
    dbms_output.put_line(b);
  end;
end;
/

Variable names are not changed. Instead they are hmm ... more qualified :)

Note the following doesn't work:

begin
  declare
    a number;
    b number;
  begin
    a := 2;
    select foo.b into b from foo where foo.a = a;
    dbms_output.put_line(b);
  end;
end;
/

As the non-qualified a in the select-statement is interpreted as a column because of the precedence rules:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.