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_rowidvarchar2(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 DNAMELOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALESCHICAGO40 OPERATIONS BOSTONSQL> select rowid,a.* from dept a;ROWIDDEPTNO DNAMELOC------------------ ---------- -------------- -------------AAABiPAABAAAFRSAAA 10 ACCOUNTING NEW YORKAAABiPAABAAAFRSAAB 20 RESEARCH DALLASAAABiPAABAAAFRSAAC 30 SALESCHICAGOAAABiPAABAAAFRSAAD 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>
