如何用dbms_rowid获取rowid的详细信息

来源：pcdog 作者：佚名出处：巧巧读书 2008-03-26 进入讨论组

关键词：
server
oracle
opera

我们知道，通过dbms_rowid包可以获得Rowid 中包含的详细信息，在下文中，
我们将通过一个定义自定义函数来介绍此package 的使用方法：

create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info

(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);

ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is   :'||to_char(row_number);
return ls_my_rowid ;
end;
/

具体用法:

[oracle@jumper tools]$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 7 12:30:19 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> set echo on
SQL> @f_get_rowid
SQL> create or replace function get_rowid
2 (l_rowid in varchar2)
3 return varchar2
4 is
5 ls_my_rowid        varchar2(200);
6 rowid_type number;
7 object_number      number;
8 relative_fno       number;
9 block_number       number;
10 row_number number;
11 begin
12   dbms_rowid.rowid_info(l_rowid,rowid_type,

object_number,relative_fno, block_number, row_number);
13   ls_my_rowid := 'Object# is         :'||to_char(object_number)||chr(10)||
14                  'Relative_fno is :'||to_char(relative_fno)||chr(10)||
15                  'Block number is :'||to_char(block_number)||chr(10)||
16                  'Row number is   :'||to_char(row_number);
17   return ls_my_rowid ;
18 end;
19 /

Function created.

SQL>
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select rowid,a.* from dept a;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAABiPAABAAAFRSAAA         10 ACCOUNTING     NEW YORK
AAABiPAABAAAFRSAAB         20 RESEARCH       DALLAS
AAABiPAABAAAFRSAAC         30 SALES          CHICAGO
AAABiPAABAAAFRSAAD         40 OPERATIONS     BOSTON

SQL> col row_id for a60
SQL> select get_rowid('AAABiPAABAAAFRSAAA') row_id from dual;

ROW_ID
------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :0

SQL> select get_rowid('AAABiPAABAAAFRSAAB') row_id from dual;

ROW_ID
------------------------------------------------------------
Object# is      :6287
Relative_fno is :1
Block number is :21586
Row number is   :1
SQL>

进入讨论组讨论。