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)
from dual a
where a.dummy = my_proc.dummy;
dbms_output.put_line('amount=' || my_proc.amount);
set serveroutput on
PL/SQL procedure successfully completed.
You can also do this for parameterised cursors and named/labelled blocks. You learn something new every day! 🙂