Re: DBMS_SQL: Can I update a DBMS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Jul 1998 15:19:42 GMT
Message-ID: <35b36b1a.2899188@192.86.155.100>

A copy of this was sent to "Daniel Clamage" <dclamageNOSPAM_at_telerama.com> (if that email address didn't require changing) On 17 Jul 1998 00:39:31 -0400, you wrote:

>There's a function in dbms_sql package to return the rowid of the current
>row.
>function last_row_id return rowid;
> -- Rowid of the last processed row.
>

but that only is valid immediately after the execute call -- it only applies to rows updated/inserted/deleted. For a fetch, it'll return a constant rowid for every call -- not the rowid of the last fetched row

Try this to test this:

create or replace procedure tst( p_query in varchar2 ) is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_colCnt        number default 0;

begin

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

end loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 4000 );

l_status := dbms_sql.execute(l_theCursor);

loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            if ( l_columnValue = rowidtochar(dbms_sql.last_row_id) ) then
                dbms_output.put_line( '*******' );
            end if;
            dbms_output.put_line( 'Rowid of row (real) ' || l_columnValue);
            dbms_output.put_line( 'Rowid of row (last) ' || dbms_sql.last_row_id
);
        end loop;

end loop;
dbms_sql.close_cursor(l_theCursor); end tst;
/
exec tst( 'select rowid from emp' );

the (real) rowid will never be the same as the (last) rowid reported by dbms_sql.last_row_id.

>--
>- Dan Clamage
>http://www.telerama.com/~dclamage
>If you haven't crashed the Server,
>you haven't been trying hard enough.
>
>cbarron2_at_my-dejanews.com wrote in article
><6ol41f$lgq$1_at_nnrp1.dejanews.com>...
>> Is it possible to update the current record fetched by a DBMS_SQL cursor?
>I'm
>> looking for a solution similar to the UPDATE...WHERE CURRENT OF feature
>of
>> regular PL/SQL cursors.
>>
>> Thanks,
>> Chris Barron
>>
>> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
>> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jul 17 1998 - 10:19:42 CDT

Re: DBMS_SQL: Can I update a DBMS_SQL cursor record?