Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using "FOR UPDATE" in dynamic SQL

Aj09Mar 2 2010 — edited Mar 3 2010
Hi Every One.
I am trying to use an UPDATE query as dynamic SQL with the 'FOR UPDATE' and 'WHERE CURRENT OF' clause. Let me explain you 2 scenarios where its works fine and 1 scenario it doesnt.

NOTE: I AM USING 'FOR UPDATE' KEYWORD IN THE CURSOR DECLARATION, BECAUSE OF WHICH SCENARIO 2 WORKS FINE.

Scenario1 **WORKS**:
for v_cur in cur loop
v_update_query :=  'update temp set '
         || v_col_name
         || ' = '
         || v_col_value;
execute immediate v_update_query;
end loop;
Scenario2 **WORKS**:
for v_cur in cur loop
update temp set  col1 = 10 where current of cur;
execute immediate v_update_query;
end loop;
Scenario3 **DOES NOT WORKS ** Error Message "ORA-03001: unimplemented feature":
for v_cur in cur loop
v_update_query :=  'update temp set '
         || v_col_name
         || ' = '
         || v_col_value      
         || ' where current of cur';
execute immediate v_update_query;
end loop;
I also tried putting it like this but does not work: ' where current of ' || cur;
The Error Message is "PLS-00306: wrong number or types of arguments in call to '||' "

Where am I doing wrong. Kindly let me know.
Thanks in advance.
Aj
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2010
Added on Mar 2 2010
3 comments
1,118 views