Use the DBMS_ROWID package to get the ROWID details
Last Updated:
Sunday, 2004-11-07 12:46
Eygle
The ROWID contains the details of the record, which can be obtained through the DBMS_ROWID package. This article describes the use of the Package via a defined custom function.
Create Or Replace Function Get_RowID
(l_rowid in varchar2)
Return varcha2
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;
/
Let's take a look at your usage:
[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 varcha2 (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
------------------------------------------------ -----
Aaabipaabaaafrsaaaa 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 ('Aaabipaabaaaafrsaaa') 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>
Author: eygle, Oracle technology followers, Oracle technical forum itpub.www.eygle.com from China is the biggest author's personal site you may contact the author by Guoqiang.Gai@gmail.com welcome to explore technical exchanges and links. exchange.
Original source:
http://www.eygle.com/faq/use.dbms_rowid.package.get.detail.of.rowid.htm
