Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns, but you could just qualify each use of a PL/SQL identifier with the identifier for the name of the block in which the item is declared in this case the procedure name. In the following example, “dummy” is both a parameter name and a table column, but Oracle knows exactly what you mean because you’ve fully qualified it.

create or replace procedure my_proc(dummy varchar2)
  amount pls_integer;
  select count(*)
  into   my_proc.amount
  from   dual a
  where  a.dummy = my_proc.dummy;
  dbms_output.put_line('amount=' || my_proc.amount);
end my_proc;

set serveroutput on
exec my_proc('Y');

PL/SQL procedure successfully completed.


You can also do this for parameterised cursors and named/labelled blocks. You learn something new every day! 🙂

This was just one of the points Bryn discussed in his whitepaper called Doing SQL from PL/SQL: Best and Worst Practices.

Bryn: Look for this section: “Name capture, fine grained dependency tracking, and defensive programming”. This discussion leads to this:

Principle 1: In embedded SQL, dot-qualify each column name with the from list item alias. Dot-qualify each PL/SQL identifier with the name of the name of the block that declares it.

It is also mentioned in his paper Why use PL/SQL?