[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 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 boston
sql> select rowid,a.* from dept a;
rowid deptno dname loc
------------------ ---------- -------------- -------------
aaabipaabaaafrsaaa 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('aaabipaabaaafrsaaa') 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>
|
