pl/sql stored procedure: parameter name same as column name

Question

I have a Stored Procedure like this

procedure P_IssueUpdate (     Id in integer,     ModifiedDate in date,     Solution in varchar2 ) AS BEGIN update T_Issue Set   ModifiedDate = ModifiedDate,   Solution = Solution where id = id; END P_IssueUpdate; 

my problem is that the parameter name is the same name as the Table column name. Is there a way to instruct the sql that the value after the "=" should be the parameter and not the column?

Thanks for your help

Answer 1

You can prefix parameter and variable names with the name of the procedure like this:

SQL> declare   2     procedure p (empno number) is   3        ename varchar2(10);   4     begin   5        select ename   6        into p.ename   7        from emp   8        where empno = p.empno;   9        dbms_output.put_line(p.ename);  10     end;  11  begin  12     p (7839);  13  end;  14  / KING  PL/SQL procedure successfully completed. 

Answer 2

what you described is called variable shadowing. It can happen in any language. You were given good workarounds but the common solution is to design a naming scheme so that it will never happen.

For example, name your columns without prefix and have your variables with a prefix that depends upon their scope (P_ for parameters, L_ for local variables, G_ for global package variables, etc...). This will have the added benefit of making the code more readable by giving you additional information.