Re: DBMS_SQL.LAST_ROW_ID doesn't work

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 02 Feb 2000 08:50:30 -0500
Message-ID: <f4dg9sklmsk35u5pjj51aeh9jsbib3fcsu@4ax.com>

A copy of this was sent to Klim Samgin <klimsamgin_at_yahoo.com> (if that email address didn't require changing) On Wed, 02 Feb 2000 12:04:36 GMT, you wrote:

>Hi!
>
>I work on Oracle8 Enterprise Edition Release 8.0.5.0.0
>under Solaris 2.6.
>
>I attempt to get ROWID of last fetched row,
>but DBMS_SQL.LAST_ROW_ID always returns
>
>AAAAA7AABAAAAIvAAF
>
>or
>
>AAAAAAAAAAAAAAAAAA
>
>These ROWIDs do not belong to table my program works with.
>(I guess they do not belong to anything :-) ).
>
>Did anybody encounter this problem?
>
>

the documentatin for last_row_id is "sparse". The last_row_id call works only on "SELECT ... FOR UPDATE", "UPDATE" and "DELETE" statements. Here is an example showing the "for update" working. For Inserts use the "returning rowid into :bindvariable" clause on the insert statement.

ops$tkyte_at_8i> select rowid, empno from emp;

ROWID                   EMPNO
------------------ ----------
AAARxiAADAAACSKAAA       7369
AAARxiAADAAACSKAAB       7499
AAARxiAADAAACSKAAC       7521
AAARxiAADAAACSKAAD       7566
AAARxiAADAAACSKAAE       7654
AAARxiAADAAACSKAAF       7698
AAARxiAADAAACSKAAG       7782
AAARxiAADAAACSKAAH       7788
AAARxiAADAAACSKAAI       7839
AAARxiAADAAACSKAAJ       7844
AAARxiAADAAACSKAAK       7876
AAARxiAADAAACSKAAL       7900
AAARxiAADAAACSKAAM       7902
AAARxiAADAAACSKAAN       7934

14 rows selected.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace procedure show( p_extra in varchar2 )   2 is
  3 cursor_name pls_integer;   4

  5          ignore          pls_integer;
  6          stmnt           varchar2(1000);
  7  
  8          row_id          rowid;
  9  
 10          num             number;
 11  BEGIN
 12      stmnt:='SELECT empno FROM emp ' || p_extra;
 13  
 14      cursor_name := dbms_sql.open_cursor;
 15  
 16      dbms_sql.parse(cursor_name, stmnt, dbms_sql.native);
 17  
 18      dbms_sql.define_column(cursor_name, 1, num );
 19  
 20      ignore := dbms_sql.execute(cursor_name);
 21  
 22      loop
 23  
 24          exit when (dbms_sql.fetch_rows(cursor_name) <= 0);
 25  
 26          row_id:=dbms_sql.last_row_id;
 27          dbms_sql.column_value(cursor_name, 1, num);
 28                  dbms_output.put_line( row_id || ' ' || num );
 29  
 30      end loop;
 31  
 32      dbms_sql.close_cursor(cursor_name);

 33
 34 END;
 35 /

Procedure created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> exec show(null)
AAAAASAABAAAOkzAAN 7369
AAAAASAABAAAOkzAAN 7499
AAAAASAABAAAOkzAAN 7521
AAAAASAABAAAOkzAAN 7566
AAAAASAABAAAOkzAAN 7654
AAAAASAABAAAOkzAAN 7698
AAAAASAABAAAOkzAAN 7782
AAAAASAABAAAOkzAAN 7788
AAAAASAABAAAOkzAAN 7839
AAAAASAABAAAOkzAAN 7844
AAAAASAABAAAOkzAAN 7876
AAAAASAABAAAOkzAAN 7900
AAAAASAABAAAOkzAAN 7902
AAAAASAABAAAOkzAAN 7934

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> exec show('for update' )
AAARxiAADAAACSKAAA 7369
AAARxiAADAAACSKAAB 7499
AAARxiAADAAACSKAAC 7521
AAARxiAADAAACSKAAD 7566
AAARxiAADAAACSKAAE 7654
AAARxiAADAAACSKAAF 7698
AAARxiAADAAACSKAAG 7782
AAARxiAADAAACSKAAH 7788
AAARxiAADAAACSKAAI 7839
AAARxiAADAAACSKAAJ 7844
AAARxiAADAAACSKAAK 7876
AAARxiAADAAACSKAAL 7900
AAARxiAADAAACSKAAM 7902
AAARxiAADAAACSKAAN 7934

PL/SQL procedure successfully completed.

ops$tkyte_at_8i>

--
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 Wed Feb 02 2000 - 07:50:30 CST