Skip to main content


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. …
Recent posts

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…

PL/SQL Puzzle: Add one statement to stop exceptions

OK, folks, here's a PL/SQL puzzle. It was originally posted on Twitter. I give you the link to that thread at the end of this post. But first....don't you want to try to solve the puzzle yourself? :-)

Please note that the solutions to the puzzle have absolutely NOTHING to do with writing good code. They are simply exercising various features of the PL/SQL language.
The puzzle Can you come up with just ONE STATEMENT to add to plsqlpuzzle_proc so that it can execute without terminating with an unhandled exception? Use this LiveSQL script as a starting point for your attempted solutions.
CREATE TABLE plsqlpuzzle (n NUMBER) / CREATE OR REPLACE PROCEDURE plsqlpuzzle_proc IS r plsqlpuzzle%ROWTYPE; TYPE r_t IS TABLE OF plsqlpuzzle%ROWTYPE INDEX BY PLS_INTEGER; t r_t; BEGIN SELECT * INTO r FROM plsqlpuzzle; DBMS_OUTPUT.put_line (r.n); DBMS_OUTPUT.put_line (t (1).n); END; / BEGIN plsqlpuzzle_proc; END; / ORA-01403: no data found I …

Why isn't my exception section catching my error?

I got an interesting email today from a reader of one of my PL/SQL 101" articles for Oracle Magazine, Building with Blocks.

Q. had taken the code from the article, made some changes, tried to run them, and got very confused. He wrote:

When I run this code, I see "Hello World".
DECLARE l_message VARCHAR2(100) := 'Hello World!'; BEGIN DBMS_OUTPUT.put_line (l_message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error='||SQLERRM); END; / Hello World! When I change the block to make the l_message variable too small for its string, I see the VALUE_ERROR error message.
DECLARE l_message VARCHAR2(10); BEGIN l_message := 'Hello World!'; DBMS_OUTPUT.put_line (l_message); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('Error='||SQLERRM); END; / Error=ORA-06502: PL/SQL: numeric or value error: character string buffer too small But when I change the name of the variables inside the call to DBMS_OUTPUT.PUT_LINE to &q…

Real World Testing of PL/SQL Code - An Office Hours Session

Since February 2019, I've been running, with Chris Saxon, on my team of Developer Advocates, monthly PL/SQL Office Hours sessions. They generally consist of short presentations on a PL/SQL-related topic, followed by lots of interesting discussion - between Chris and I, with attendees on the session, and with other speakers.

On November 5 at 9 AM Eastern, I am very pleased to have a session focused on testing PL/SQL code, featuring developers who are doing it out there in the "real world."

No application will ever have zero bugs, but you sure want to keep them to a minimum. The best way to do this is to implement automated regression tests of your code, but "best" as usual does not equate to "easiest." Building and managing tests can be a big challenge, so in this Office Hours session, we will hear from developers who are doing just that. Learn from your peers about the obstacles they faced and how they overcame them. Bring your own stories and your q…

Comparison Methods for Object Types, Part 5

There are special member methods - map or order methods - that we use to tell Oracle Database how to compare two objects of the same datatype. This capability is critical when we want to perform an equality test in PL/SQL or when sorting objects in SQL.

There is no default way to do this. In other words, if I create a simple object type, add it as a column to a table, and try to compare or sort, all I get are errors. Let's take a look. First I will create a table that has an object type as a column and add a couple of rows.
CREATE TYPE food_ot AS OBJECT ( name VARCHAR2 (100), food_group VARCHAR2 (50), grown_in VARCHAR2 (100) ) NOT FINAL / CREATE TABLE meals ( served_on DATE, main_course food_ot ); / BEGIN INSERT INTO meals (served_on, main_course) VALUES (SYSDATE, food_ot ('Shrimp cocktail', 'PROTEIN', 'Ocean')); INSERT INTO meals (served_on, main_course) VALUES (SYSDATE + 1, food_ot ('House Salad', …

Appreciation for Those Who Give of Themselves (#ThanksOGB)

My #ThanksOGB post:

I've been working - and personally benefiting from - Oracle Database technology since 1987. I joined Oracle as a pre-sales consultant, which meant back then I was a techie sidekick for one or more Oracle salespeople. I moved on to various other roles and in 1992 left to become a consultant. Two years later, wrote a book on PL/SQL and have been obsessed with that language ever since. In 1999, I released the first version of utPLSQL - unit test for PLSQL, my version of JUnit. Worked for Quest for many years (bringing Quest Code Tester for Oracle to the market, among other things), and in 2014 rejoined Oracle, where I now lead a team of developer advocates (Blaine Carter, Chris Saxon, Connor McDonald, Dan McGhan).

It's been a great life - and I expect it to keep on being such for a while to come. Part of the point of my little historical review, though, is that I was always paid to provide resources to the community (some of them free, like utPLSQL and my man…