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_rowid2 (l_rowid in varchar2)3 return varchar24 is5 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 begin12 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 YORK20 RESEARCH DALLAS30 SALES CHICAGO40 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>
