Useless, but fun

• Integer base conversion in Oracle SQL

I needed to generate alphanumeric (some punctuation allowed, but no unprintable characters) unique IDs, lots of them, all having a fixed length. First plan: get an alphabet of allowed characters and convert a number gotten out of a sequence to base whatever the length of the alphabet turned out to be. Given that creating database objects of any kind except tables and indexes had to be avoided (don't ask), I couldn't just write (or steal from Tom Kyte) a PL/SQL function for that. And so, this SQL was born:

  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:

substr(lpad(rtrim(utl_raw.cast_to_varchar2(utl_encode.base64_encode(hextoraw(to_char(myID.Nextval,'fmXXXXXXXXXXXXXXXXXXX')))),'='),11,'-'), -11)

• Parsing extended ROWIDs

Ever wondered how the Data Object ID, Relative File Number, Block Number, and Row Number are combined to form an extended ROWID?  I did, and I found the answer in the Concepts guide: base64 encoding.
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 :-)