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

Question

Hello Oracle Experts

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 "=" sould 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

i found a solution. it's working by full qualifying the parameter:

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

Answer 3

Hi Gsharp,

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.