Re: Dynamic SQL "for update"

A copy of this was sent to "Michael Norton" <mike.norton_at_home.com> (if that email address didn't require changing) On Tue, 26 Oct 1999 05:52:53 GMT, you wrote:

>I'm using PL/SQL in Oracle8i and I'm trying to build a generic procedure
>that can be used with many table, doing different updates. I'd like to be
>able to build the cursor dynamically using the dbms_sql package, and update
>the table using a "where current of" statement.
>
>Since the cursor is not named, I've got nothing to pass to "where current
>of". If I try a reference cursor it will not allow me to select "for
>update".
>
>Does anyone have any ideas? The closest thing I can come up with is to have
>a program write the entire update procedure at run time and call that.
>
>
>

you need a real cursor (a plsql cursor) to use where current of.

Instead, you could select out the rowid as well as the columns you are interested in. replace the "where current of CURSOR_NAME" with "where rowid = :x" and bind in that rowid. As long as you did a select for update -- it'll have the same sort of effect as "where current of" does.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 26 1999 - 07:52:32 CDT