pl/sql stored procedure: parameter name same as co

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.

Answer 3

RE Vincent's answer about prepending a prefix--that solution works until somebody modifies the table and adds a column whose name happens to collide with the parameter name. Not everybody goes through every line of code to make sure their table modifications won't conflict with variable or parameter names. Oracle's recommendation is to qualify every parameter or variable name in a SQL query.

If you're working with an anonymous block (outside a procedure), you can name the block and qualify variables that way:

<<MY_BLOCK>>
declare
   X   sys.USER_TABLES%rowtype;
   Y   sys.USER_TABLES.TABLE_NAME%type := 'some_table_name';
begin
   select UT.*
   into   MY_BLOCK.X
   from   sys.USER_TABLES UT
   where  UT.TABLE_NAME = MY_BLOCK.Y;
end MY_BLOCK;

Answer 4

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;