Skip to main content


Showing posts from November, 2019

PL/SQL Puzzle: No extra code please!

I published yet another PL/SQL puzzle on Twitter yesterday. Generated lots of interest and interesting replies. I don't think any single person caught everything, but as usual the community came through.

I will repeat the puzzle here. If you haven't already seen it on Twitter, please try to solve it yourself before looking at my answer.
What text can be removed from lines 3 though 12 in the code below so that after the anonymous block is executed, "121212" is still displayed on the screen?

White space

so you do not immediately

see my answers. 


OK, let's dive in.

Notice, first of all, that I asked about text that can be removed, not lines. So you can remove entire lines or portions of lines. I refuse to accept that whitespace is text, so blank lines don't count. :-)

Here are the opportunities for removal that I found:

4 - Remove the IN keyword. That's the default for parameters (though I generally always include it in my code).

5 - Remove AUTHID DEFINER. …

PL/SQL Puzzle: What code can be removed?

I published a PL/SQL puzzle on Twitter on November 6 2019. I asked the following question:
Which lines of code can be removed (either entirely or in part) from the block below and not affect the output of the program in any way? I neglected to mention in my original tweet a few important assumptions:
You are running this code on Oracle Database 10g or higher.Server output is turned on.Whitespace (spaces, tabs, new-lines) don't count. Here's the code. I will publish it as an image, just as I did on Twitter, so that you can give it a go yourself, before taking a look at the answers from me and others below that.
Check out the Twitter conversation for all the answers that were submitted. It's a fun read!

Here are the full lines that I believe can be removed:

2 - There is not need to declare the iterator used in a FOR loop, numeric or cursor versions.

7 - There is no need to declare an "empty" collection to be used to initialize l_objects.

10 - Collections are empty a…