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; begindbms_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/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; 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 BOSTONSQL> select rowid,a.* from dept a;ROWID DEPTNO DNAME LOC------------------ ---------- -------------- -------------AAABipAABAAAFRSAAA 10 ACCOUNTING NEW YORKAAABiPAABAAAFRSAAB 20 RESEARCH DALLASAAABiPAABAAAFRSAAC 30 SALES CHICAGOAAABiPAABAAAFRSAAD 40 OPERATIONS BOSTONSQL> 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>
