Re: DBMS_SQL and cursor scope

On Wed, 12 Apr 2000 20:27:02 GMT, mvalek_at_ema-inc.com wrote:

>I am using DBMS_SQL to select a row from a table based on rowid. I use
>EXECUTE_AND_FETCH to fetch the row. I next do an update on the fetched
>row, also using DBMS_SQL, except using 'UPDATE .. where current of c1',
>where c1 is the cursor handle of the previously DBMS_SQL fetched row.
>Is that cursor out of scope with the call to DBMS_SQL.PARSE of the
>update statement? I receive an "ORA-00936: missing expression". Thanks
>for any help.
>
>Matt
>
>---------------------------------------------
>cursor_handle_update := DBMS_SQL.OPEN_CURSOR;
>
>DBMS_SQL.PARSE
> (cursor_handle_update,
> 'SELECT * FROM '||p_table||' WHERE rowid = '''||p_row_id||'''',
> DBMS_SQL.NATIVE);
>
>l_update_status := DBMS_SQL.EXECUTE_AND_FETCH(cursor_handle_update,
>FALSE);
>
>IF l_update_status = 0 THEN
> RAISE UPDATE_FAILED;
>END IF;
>
>cursor_handle := DBMS_SQL.OPEN_CURSOR;
>--------------------------------------------------------------
>
>-- update current record as pointed to by cursor_handle_update
>--------------------------------------------------------------
>FOR k IN 1..p_arg_names.count LOOP
> IF p_arg_values(k) IS NOT NULL THEN
> strQuery := 'UPDATE '||p_table||' SET '||p_arg_names(k)
>||' = '||p_arg_values(k)||' WHERE CURRENT OF cursor_handle_update';
>
> DBMS_SQL.PARSE
> (cursor_handle,
> strQuery,
> DBMS_SQL.NATIVE);
>
> l_status := DBMS_SQL.EXECUTE(cursor_handle);
>
> IF l_status = 0 THEN
> rollback;
> RAISE UPDATE_FAILED;
> END IF;
> END IF;
>END LOOP;
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Obviously the cursor _handle_ is a different datatype than the ordinary cursor, and this simply won't work. As a workaround select the rowid explicitly and use 'where rowid = chartorowid(your_rowid_var)'.
Actually if you ever traced an update where current of, guess what

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Apr 12 2000 - 00:00:00 CDT