Obfuscation contest

I have been challenged to participated (my script : lsc2.sql) and now challenged to explain my query by Chen

Ok, I give a try.

To make the query unreadable and unformatable I used no space, no new line, and I started by q’<'>‘ to confuses sqlinform.

Ok, I added undocumented constructs like
connect by .1 < 1. connect by .25 > rownum/100.
with 2 connect by, the first is ignored. The second is buggy in 10gR1 and returns 25 rows in 10gR1 (but 24 rows in 9i, 10gR2, 11g).

and group by () which means only one group. Note this clause is mandatory, you cannot remove it when using an aggregate function and a scalar subquery like in
select (select 1 from dual), count(*) from dual group by ();

The utl_raw and to_char converts a number to the string Oracle Community. I will keep terse on the formula.
The scalar subquery inside TAN returns 1. The regexp removes some characters out of the extract tag. _x0032_ is the alias of the column “2”. The v$reservedwords use UNIQUE to sort the rows (UNIQUE was sorting in 10gR1 for UNIQUE/DISTINCT) and the MAX(rownum) retrieve the 316th row (FROM) and 845th row (SELECT). Remember ROWNUM is evaluated BEFORE aggregation, therefore ROWNUM 845 exists! Note the “+” is an alias and not a string!

With the SELECT and FROM keyword I build a new query that I dynamically evaluates with dbms_xmlgen.getxml. Rest of the first part is fancy calculation.

The dburi is also dynamic.

Well, I could have obfuscated the text a bit more but I was short of time… This simply return the name of the package that have a procedure called GETKEY. Which is DBMS_OBFUSCATION_TOOLKIT_FFI. This is not very clean as the query would fail if you have a PROC.GETKEY in your schema. Well, I wanted to add this not very well know mechanism to query the database.

Last part is in dict, I select the pattern ‘[COTTON+XE]{4,}’ in dict and return the Mode (or modal score), which is CONTEXT.

A bit formatting and I get
Oracle Community_Obfuscation_Contest

By Laurent Schneider

Oracle Certified Master


  1. Objection, your honor : The query should be formated and have a least some sort of meaning like the one that produce a yearly calandar like unix command ‘cal’ (this one is really impressive). Otherwise it is going to turn at who can pile the more regexp and math functions.

  2. 🙂 I used math function quite economically to translate a list of numbers to a hex string ! One of the objective was to show obscure techniques. But well, I could do cal too :

    select * from (
    select * from dual model dimension by (0 day, 1 week)
    measures (0 c)
    c[for day from 0 to 6 increment 1, for week from 0 to 4 increment 1]=
    c[any,any]=case when c[cv(),cv()] between
    1 and extract(day from last_day(sysdate)) then c[cv(),cv()] end))
    pivot (max(c) for day in (
    0 as mon,1 as tue,2 as wed,3 as thu,4 as fri,5 as sat,6 as sun))
    order by week;

    but is this really obscure?

  3. hummm… lemme think….really…..hummm…hard….hard…. Yes it is not evident. The construct within the rule section is not cristal clear and I am lucky to have found the excellent tutorial of Joe on the caluse model http://www.sqlsnippets.com/en/topic-11663.html.
    And even, this query is the soft version using the 11g pivot. I saw one without it for 10g with a bunch of connect by. Was fun, I gave up when I met the second connect by.

    However, I vote for this query over the lsc2, this one has a palatable goal.
    is it possible to do an inverse of matrix using the pivot and model clause ? that would be usefull, awfully complicated so that you put it in the interview under the nose of the candidate and ask what the query does.

  4. I have not done this yet. It is surely possible, but I do not know myself how to calculate inverse matrix.

    To multiply matrix I could imagine :
    SQL> create table m1 as
    select 1 x, 1 y, 10 val from dual
    union all select 1,2,20 from dual
    union all select 2,1,30 from dual
    union all select 2,2,40 from dual
    union all select 3,1,50 from dual
    union all select 3,2,60 from dual
    union all select 4,1,70 from dual
    union all select 4,2,80 from dual;
    Table created.
    SQL> create table m2 as
    select 1 x, 1 y, 100 val from dual
    union all select 1,2,200 from dual
    union all select 1,3,300 from dual
    union all select 2,1,400 from dual
    union all select 2,2,500 from dual
    union all select 2,3,600 from dual;
    Table created.
    SQL> select * from m1 pivot (max(val) for y in (1,2))

    X 1 2
    ---------- ---------- ----------
    1 10 20
    2 30 40
    3 50 60
    4 70 80
    SQL> select * from m2 pivot (max(val) for y in (1,2,3))

    X 1 2 3
    ---------- ---------- ---------- ----------
    1 100 200 300
    2 400 500 600
    SQL> select * from (
    select * from dual model
    m1 on (select * from m1) dimension by (x,y) measures (val)
    m2 on (select * from m2) dimension by (x,y) measures (val)
    dim1 on (select max(x) x,max(y) y, 0 dummy from m1) dimension by (dummy) measures (x,y)
    dim2 on (select max(x) x,max(y) y, 0 dummy from m2) dimension by (dummy) measures (x,y)
    dimension by (1 x,1 y) measures (0 val)
    rules iterate (100) until (dim2.x[0]=iteration_number+1)
    (val[for x from 1 to dim1.x[0] increment 1, for y from 1 to dim2.y[0] increment 1]=
    nvl(val[cv(),cv()],0)+m2.val[iteration_number+1,cv(y)]*m1.val[cv(x),iteration_number+1] ))
    pivot (max(val) for y in (1,2,3))

    X 1 2 3
    ---------- ---------- ---------- ----------
    1 9000 12000 15000
    2 19000 26000 33000
    3 29000 40000 51000
    4 39000 54000 69000

  5. Hooo…….a pivot feeded by 2 nested loop out of 5 dimensions… we are reaching new hight of pain. The query is not made of cryptic or exotic functions, i can undertand every single word apart though I have hard time to understand the whole picture. The worst query are the ones made of simple words but still you don’t understand: the longer you look, the more idiot you feel.

    Back home, I will tell wife that it took me 5 hours to understand 10 lines.

  6. Please join and play!


    The Obfuscated SQL Contest at the PL/SQL Challenge
    This question is Not Answered.
    Steven Feuerstein-Oracle
    Super Elite
    Steven Feuerstein-Oracle 20-Mar-2015 16:28
    For those of you who do not know about, the PL/SQL Challenge (plsqlchallenge.com) is a website that offers weekly and monthly quizzes on SQL, PL/SQL, Database Design and Logic.

    Reading doc (and discussion threads and books and …) and watching videos are relatively passive ways of learning. Taking a quiz directly tests your knowledge, exposing areas that need more attention and reinforcing expertise you already have.

    So I encourage you to sign up at plsqlchallenge.com and give it a try.

    But I write today with a specific and unusual “challenge”: as a complement to our weekly “serious” quizzes, Chris Saxon, Oracle Developer Advocate for SQL and the Database Design quizmaster, has launched the Obfuscated SQL Contest.

    Here’s the description from the site:

    Daylight savings changes are coming in across the world!
    Scheduling meetings with colleagues across the globe is always a challenge. With different countries switching on different dates (and some not switching at all!) through March this is even harder.
    To help us navigate this, we’d like a SQL query that returns the current date and time for different timezones.
    There is, however, a twist. We don’t want plain or boring or readable SQL.
    We want the most ingenious, creative, quirky and downright weird SQL you can muster to perform this task.
    And thus begins the Obfuscated SQL Contest.
    The challenge:
    Write a single SQL statement that returns the current date and time for all the following locations:
    Bangalore, London, New York, Chicago and Denver.
    The rules:
    The output must match this format (all on one line):
    Maximum length the SQL statement can be is 4,000 characters (after removing unnecessary whitespace)
    Supply the minimum version of Oracle required to execute the query and (if relevant) the maximum
    You cannot create any additional objects yourself. However you can use any objects supplied with the default installation of Oracle Enterprise Edition
    The judging panel will pick a winner or winners based on the following criteria:
    Using little-known or rarely-used features of Oracle
    Exploiting features for something other than their intended purpose
    Making use of multiple features within a single query
    Quirky, funny or otherwise ingenious SQL code

    We’ve already gotten some really hard-to-understand submissions. Can you do WORSE? 🙂 And, sure, this is a discussion forum, so I suppose you could simply answer this post with your solution. But then you will not be in the competition for winner of the VERY WORST solution.


Leave a comment

Your email address will not be published.