- Integer base conversion in Oracle SQL
select decode(sign(:what), -1, '-')||reverse(replace(res,' ','')) to_base from ( select sys_connect_by_path(substr(:alphabet, 1+mod(trunc(abs(:what)/power(:base,level-1)), :base), 1),' ') res, trunc(abs(:what)/power(:base,(level-1))) rem from dual start with :base>1 and trunc(:base)=:base and length(:alphabet)>=:base connect by trunc(abs(:what)/power(:base,(level-1)))>0) where rem<:base;
The code handles negative numbers correctly, checks entry conditions (base must be integer and at least two, alphabet must have at least base characters), and looks reasonably cute. Unfortunately, I can't think of a use for it other than as an exhibit.
Incidentally, I ended up using base64-encoding for my unique IDs:
- Parsing extended ROWIDs
And I just had to test it.
with t1 as (select rowid rid, substr(rowid,1,6) obj, substr(rowid,7,3) fil, substr(rowid,10,6) blk, substr(rowid,16,3) rw from t) select to_number(utl_encode.base64_decode(utl_raw.cast_to_raw('AA'||obj)),'XXXXXXXXXXXX') obj#, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw('A'||fil)),'XXXXXXXXXXXX') fil#, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw('AA'||blk)),'XXXXXXXXXXXX') blk#, to_number(utl_encode.base64_decode(utl_raw.cast_to_raw('A'||rw)),'XXXXXXXXXXXX') rw#, rid from t1 order by rid;
Why would anyone want to use this instead of the handy programs from the DBMS_ROWID package is beyound me. But it was fun to write :-)
And, marginally less useless, here's a script to find a row from a 6-byte restricted rowid (gotten e.g. from an index leaf block dump), assuming you know the data object ID of the table, of course:
with s as (select replace('04 c0 00 0a 01 c0', ' ') rid from dual), rrid as (select dbms_utility.data_block_address_file(to_number(substr(rid,1,8),'XXXXXXXX')) file#, dbms_utility.data_block_address_block(to_number(substr(rid,1,8),'XXXXXXXX')) block#, to_number(substr(rid,-4),'XXXX') row# from s) select t.*, t.rowid, dbms_rowid.rowid_create(0, 0, file#,block#,row#) restricted_rid from t, rrid where t.rowid=dbms_rowid.rowid_create(1, &data_object_id., file#,block#,row#);
For extra meaningless workout, try getting the file# and block# from the first four bytes (Data Block Address) without the help of DBMS_UTILITY. I did.
Update (April 30): Richard Foote finds this nice :-)