Oracle select for update cursor not updating?

I created an Oracle stored procedure the other day that used a ‘select for update’ cursor to update some information in a staging table. None of the update statements seemed to work. I thought I had the syntax wrong, etc. After spending some time just staring at the code I asked a coworker to take a quick look. We both sat down to examine my code. Everything looked fine.

My procedure looked something like:

create or update procedure my_procedure(mydata IN varchar2) is
begin
declare
    cursor my_cur is select * from my_table
    where my_value > 100 for update;
    row_entry my_cur%ROWTYPE;
    begin 
        -- open the cursor, fetch into row_entry, loop, etc.
        -- update the MYDATA column
        update my_table set MYDATA=mydata where current of my_cur;  
    end;
end;

This looked ok to the both of us but the MYDATA column in my_table refused to update as expected. Eventually we figured it out.

When I update the table, I’m trying to assign the value of the in parameter ‘mydata’ to the ‘MYDATA’ column of at the current location of the cursor. Note the name of both the column I’m trying to assign to and the name of the import parameter.

Oracle doesn’t recognize that the right hand side of this assignment is my input parameter. It treats the right hand side as a column name instead. So in effect I’m assigning the value found at the current cursor location for column ‘MYDATA’ to the column ‘MYDATA’ — a no-op.

Changing the name of the in parameter from ‘mydata’ to ‘p_mydata’ solved my problem. We could have saved a lot of time and frustration if Oracle had at least warned me of this situation.