Friday, March 12, 2010

DBMS_ROWID

TEST @dummy1> insert into test1 values (1);

1 row created.

TEST @dummy1> insert into test1 values (2);

1 row created.

TEST @dummy1> select rowid as therowid, id,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid,1, 6), 8, 'A'))), 'XXXXXXXXXXXX') as objid,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 7, 3), 4, 'A'))), 'XXXXXX') as filenum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 10, 6), 8, 'A'))), 'XXXXXXXXXXXX') as blocknum,
to_number(utl_encode.base64_decode(utl_raw.cast_to_raw(lpad(substr(rowid, 16, 3), 4, 'A'))), 'XXXXXX') as rowslot
from test1 where id <= 2;

THEROWID ID OBJID FILENUM BLOCKNUM ROWSLOT
------------------ ---------- ---------- ---------- ---------- ----------
AAAM2dAAEAAAABIAAA 1 52637 4 72 0
AAAM2dAAEAAAABIAAB 2 52637 4 72 1


TEST @dummy1> select dbms_rowid.rowid_create(1,52637,4,72,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAM2dAAEAAAABIAAA

TEST @dummy1> select * from test1 where rowid = dbms_rowid.rowid_create(1,52637,4,72,0);

ID
----------
1