Learn more about Oracle by examining obscure SQL
How many features can you use in one SQL query to solve a problem? (that is, how many necessary features do you need without deliberately obfuscating your code)
In this rather educational example we see (1) XML, (2) UTL_RAW.reverse and UTL_RAW.cast_to_varchar2, and (3) the MODEL clause: “How to convert a number from any base to any base in SQL” (Frank Zhou).
Unfortunately the query crashes with ORA-03113 end-of-file on communication channel on my database (10.2.0.3), but I won’t hold that against him 🙂
Personally, I’d implement this using a PL/SQL function, but that’s just me…
Earlier Frank showed a narrower example (Octal/Hex to Binary) using a similar technique, except using a hierarchical query and sys_connect_by_path instead of the MODEL clause.