I 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)
as
amount pls_integer;
begin
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');
amount=0
PL/SQL procedure successfully completed.
SQL>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?
Cheers
Tim…