It was Chen Shapira's idea and here I am making it the official first ever Oracle community contest.

Read on to learn more about the obfuscated SQL code contest.

What does obfuscate mean?

To obfuscate means to make so confused or opaque as to be difficult to perceive or understand.

What are the goals of the contest?

  • To write the most Obscure/Obfuscated SQL query.
  • To show the importance of SQL programming style, in an ironic way.
  • To stress SQL techniques with unusual code.
  • To illustrate some of the subtleties of the SQL language.
  • To provide a safe forum for poor SQL code.

What are the rules of the contest?

  • Your entry must be one SQL script file.
  • The SQL script must contain only one SQL (SELECT) statement.
  • The SQL statement can be as short or as long as you want and may contain an unlimited number of subqueries or inline views.
  • You must provide the database version the script was tested on.
  • The SQL statement must be able to be executed by anyone with a connection to an Oracle database - with at least the same database version as indicated by the author.
  • If needed, the script may include test data or database object creation code required to make the SQL statement execute successfully.
  • The SQL script must be of original work.

Who is eligible to participate?

Any Oracle Community member is eligible to submit one entry.

How do I submit an entry?

Just upload the SQL script file with your reply below. Make sure the file's extension is either sql or txt. In addition to uploading the file, you may also include a link to your entry, if you have published it on your blog or web site.

What else?

April 1st 2008 is the last day you can participate in this contest and submit your entry. After that date, all eligible entries will be voted/commented on. The author of the most popular entry will be declared the winner.

Happy obfuscation and good luck!

Views: 2071

Replies are closed for this discussion.

Replies to This Discussion

The SQL script must contain only one SQL (SELECT) statement.

So no subqueries are permitted, inculding inline views?
I meant one top level SELECT. You can have an unlimited number of subqueries or inline views. Thanks for the clarification. I have clarified the rules above accordingly.
The scripts requires version 11.1.0.6, because of the pivot operator.
If you don't have that version, you can see the output here
Attachments:
Got my vote! Belongs on a t-shirt ...

does it need to be our own code? I don't think I could win if I had to write something worse than other peoples code that I see everyday.

here's one from OTN. the question was "how to calculate the next YYYYIW in a sequence". but it really was "the next IYYYIW". so if you're currently in 200813, the answer is 200814. but at the end of a year, you wrap around to IYYY+1, and IW goes back to 01. and don't forget to account for years with 53 weeks (like 2009).

The Obfuscated sql statement makes use subquery factoring, generates rows from dual via "connect by level <", and is overall quite the spectacle to look at. and it is truely obfuscated when you consider the same results can be achieved with this simple statement:

to_char( trunc(trunc(to_date( iyyy,'yyyy'),'iyyy'),'iw') + iw*7 ,'IYYYIW') next_iw

(where IYYY and IW represent the year/week value which needs to be incremented).
this came from http://forums.oracle.com/forums/thread.jspa?threadID=633028

I tested it (and my simpler solution) on 10.1.0.2.0
Attachments:
10.1.0.5
here is the code :
tried it on both 10.1.0.2 and 10.2.0.3 and got the following error:

ORA-19202: Error occurred in XML processing
ORA-24333: zero iteration count
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
The only unobfuscated part of Laurent message says 10.1.0.5.
Try there :-)
well if I had that exact version, I'd try it ;)
so instead I tried just a little lower and just a little higher, and neither worked.
so how about someone with 10.1.0.5 try it and post the results so I can at least see what I'm missing.

and since this seems to work on one and only one subversion, it might be the most obfuscated code possible.
--------------------------- Quadruplet Puzzle ---------------------------------

The following Quadruplet Puzzle Query Solution can solve "4" different puzzles in a single ORACLE SQL.

Puzzle 1: Largest Rectangle of Words
find the largest possible rectangle of letters such that every row forms a word (right to left) and every column forms a word (reading top to bottom)

Puzzle 2: blank SUDOKU Solution

Puzzle 3: Impossible_Combinations
What is the smallest number of coins that you can’t make a dollar with? i.e.,for what N does there not exist a set of N coins adding up to a dollar? It is possible to make a dollar with 1 current U.S. coin (a Susan B. Anthony), 2 coins (2 fifty cent pieces), 3 coins (2 quarters and a fifty cent piece), etc. It is not possible to make exactly a dollar with 101 coins.

Puzzle 4: Divisible by 1 to 9
Find a 9-digit number a[1]a[2]a[3]…. containing all the digits 1,2,…,9 such that “K” divides a[1]a[2]a[3]…..a[k] for each k = 1,2,3….9.

Version : Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Here is the SQL Code :
Attachments:
LOL, pasted it into the code formatter at orafaq, formatter couldn't handle it.

RSS

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Badge

Loading…

Blog Posts

Steps to Follow When Developing Interactive Children’s eBooks

Today, just getting a book published and made available in print isn’t enough. As printed books will only reach half of your audience base. Publishers can now increase their sales by developing books in a format that is preferred by your readers. Publishers are already recognizing the disruption brought about by eBooks.…

Continue

Posted by Alvaro Dee on December 3, 2016 at 12:25am

Recover a deleted folder

Specifically to solve caused by- starting up you see, our own laptop or computer publisher, Right after that using [HKEY_CLASSES_ROOT\ftp] Add a vital dubbed recovering files from sd card "ShellFolder" When it comes to cord cost point "E436EBB6-524F-11CE-9F53-0020AF0BA770, The mistake do just stop developing, With out a reactivate or other behavior applicable.…

Continue

Posted by Rachel C on December 1, 2016 at 10:09pm

4 Things to Know About the GREATEST Function in Oracle

The GREATEST function in Oracle is one that is helpful, but I haven't seen used very often. If you do use it, keep these things in mind.
What Is The GREATEST Function?
The first thing to know about the function is what it does.
GREATEST finds the greatest value in a list of provided values.
The syntax is:
GREATEST ( expr1, [ expr_n] )
You provide it with a set of expressions, and the function will find the greatest of…
Continue

Posted by Ben Brumm on November 27, 2016 at 10:00am

Oracle E-Business Suite Training

Tekslate.com is the Industry leader in providing  Oracle E-Business Suite Training across the globe. Our online training methodology focus on hands on experience of Oracle EBS.

The objective of this training is to provide hands on experience and in depth understanding of the following modules of Oracle E-Business Suite Training.

Course Modules: 

  • Understanding…
Continue

Posted by Mohd Azher on November 17, 2016 at 10:24pm

© 2016   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service