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?