Skip to content
Tags

Learn more about Oracle by examining obscure SQL

April 21, 2008

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.

About these ads

From → SQL

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 180 other followers

%d bloggers like this: