Excellent suggestion regarding downloading the en_US.oxt.zip dictionary file. The following is a Windows VBS script (save with a .vbs extension, and then execute with cscript at a command line) that reads the en_us.dic file, creates a table named US_DICTIONARY, and then inserts the words from the en_us.dic into the US_DICTIONARY table:

]]>By: Charles Hooperhttps://hoopercharles.wordpress.com/2011/08/05/the-new-order-oracle-coding-challenge-3-mind-boggle/#comment-3772
Mon, 08 Aug 2011 18:58:14 +0000http://hoopercharles.wordpress.com/?p=5269#comment-3772Here is another way to generate variable length words using the characters ESOIMEFOALEUSAYE. First, we need each character to be on a row by itself with its position in the list of characters identified. We also need a NULL character that will be assigned a position of 0.

With the above SQL statement slide into a WITH block, we are abel to easily build 3 to 7 character letter groupings using the letters. We need to make certain that a NULL character (P=0) does not appear in the first 3 character positions, and that if the NULL character appears after the third position, all characters after the NULL character are also NULL. We will end up with something like the following:

The SQL statement can be easily plugged into the sample Excel macro that I provided. It appears that this method is a little faster than my PL/SQL function that is included in the article.

]]>By: Radoslav Golianhttps://hoopercharles.wordpress.com/2011/08/05/the-new-order-oracle-coding-challenge-3-mind-boggle/#comment-3770
Sun, 07 Aug 2011 22:58:21 +0000http://hoopercharles.wordpress.com/?p=5269#comment-3770Charles,
your solution is definitely more practical than my – I mean the checking dictionary part..
I used utl_http because it was the first solution which came to my mind and it was the fastest to write, but in fact, it’s very inpractical for milions of rows..

In a “real life” I would probably download some free dictionary (aspell, open/libre office), load it to the database (using external table or sqlldr, probably hash cluster table/IOT would be the best structure to load into)
In the pl/sql solution I would query that table – lot of context switching (sql vs pl/sql), but still better and faster than HTTP.
In the sql solution I would join generated words to the loaded table.

I do not think that “elfs” appears in many English dictionaries – the word is usually spelled “elves”, and indicates more than one elf. In the blog article I was attempting to create a funny sentence from the words found in the puzzle, and I momentarily forgot the correct spelling of elves. The word “elfs” was found in one of the dictionaries that I checked, so I left it in the article for a little humor.

but this directory didn’t find elfs.. so again it’s not completely correct..

]]>By: Radoslav Golianhttps://hoopercharles.wordpress.com/2011/08/05/the-new-order-oracle-coding-challenge-3-mind-boggle/#comment-3767
Sun, 07 Aug 2011 16:56:34 +0000http://hoopercharles.wordpress.com/?p=5269#comment-3767It’s not a oracle bug, it’s my bug, but I believe the minor one :). The searched string is not in first 2000 bytes, so it always returns 0..
The pl/sql is also not correct but “xxkhs” can’t be generated from those letters..
The pl/sql could be rewritten using utl_http.request_pieces..

I have to found an API, where response is smaller, I think there are few.. dictionary.com also provides some API, but registration is needed and I’m to lazy to register :)

That is an impressive solution – I did not think that it was possible with just a single SQL statement. I am not sure if it is a sign of a bug on 11.2.0.2 patch 4 on Windows x64, a time-out issue, or something else – my output is: