Using the DBMS

Use the Dbms_rowid package for more information about ROWID

Last Updated:sunday, 2004-11-07 12:46 eygle






The ROWID contains detailed information about the record, which can be obtained through the DBMS_ROWID package. This article describes the use of this package through a defined custom function.

Create or Replace function Get_rowid (L_rowid in varchar2) return Varchar2isls_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; /



Let's take a look at the usage:



[Oracle@jumper tools]$ sqlplus scott/tigersql*plus:release 9.2.0.4.0-production on Sun Nov 7 12:30:19 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to:oracle9i Enterprise Edition release 9.2.0.4.0-productionwith the partitioning optionjserver release 9.2.0.4 .0-productionsql> set echo onsql> @f_get_rowidSQL > 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; Ten 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; 19/function created. Sql> sql> SELECT * FROM Dept DEPTNO dname LOC-------------------------------------ACCOUNTING NEW YORK DALLAS CHICAGO ations bostonsql> Select rowid,a.* from dept A; ROWID DEPTNO dname LOC-------------------------------------------------------aaabipaabaaafrsaaa ACCOUNTING NEW Yorkaaabipaabaaafrsaab dallasaaabipaabaaafrsaac SALES Chicagoaaabipaabaaafrsaad OPERATIONS bostonsql& Gt Col row_id for a60sql> Select Get_rowid (' aaabipaabaaafrsaaa ') row_id from dual; row_id------------------------------------------------------------object# is:6287relative_fno is:1block number is : 21586Row number is:0sql> Select Get_rowid (' Aaabipaabaaafrsaab ') row_id from dual; row_id------------------------------------------------------------object# is:6287relative_fno is:1block number is : 21586Row number Is:1sql>







The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.

Original source:

Http://www.eygle.com/faq/Use.dbms_rowid.Package.Get.Detail.Of.Rowid.htm