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 offer below the solutions that I and others came up with. But first a nice big chunk of white space so you do not see those solutions immediately.

So. You looked at the code and you came up with some ideas? I hope so!

If by chance you have a solution that we do not show below, please add it via comment. I will move it to the post, give you credit and you will be FAMOUS. :-)

Why not just insert a row?

As you likely know, a SELECT-INTO (implicit single-row query) raises the NO_DATA_FOUND exception if no rows are identified. Since the plsqlpuzzle table is empty, an immediate thought is to use that one statement to add a row to the table. Let's try that.

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 
   INSERT INTO plsqlpuzzle VALUES (100);

   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

Huh? Still getting a NO_DATA_FOUND exception? But...but...the table has a row. Yes, it does, but the NO_DATA_FOUND exception is also raised when I try to "read" a "row" in a collection (look at an element in a collection at a specific index value). Well, a collection is kind of like a table, right?

So while the one row in the table fixes the problem with NO_DATA_FOUND from SELECT-INTO, it does not stop the exception from being raised from this line:

DBMS_OUTPUT.put_line (t (1).n); 

RETURNING to the scene of the crime

Sorry, there's no crime. Just needed a catchy title for the header. :-)

So a couple of clever developers (see the Twitter thread link at bottom of post) came up with a modification (or two) to the INSERT statement to get around this problem. Namely: RETURNING.

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 
   INSERT INTO plsqlpuzzle VALUES (100)
      RETURNING n BULK COLLECT INTO t;
   
   SELECT * INTO r FROM plsqlpuzzle; 
 
   DBMS_OUTPUT.put_line (r.n); 
 
   DBMS_OUTPUT.put_line (t (1).n); 
END; 
/

BEGIN
   plsqlpuzzle_proc;
END;
/

100
100

DELETE FROM plsqlpuzzle
/

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 
   INSERT INTO plsqlpuzzle VALUES (100)
      RETURNING n INTO t (1).n;
   
   SELECT * INTO r FROM plsqlpuzzle; 
 
   DBMS_OUTPUT.put_line (r.n); 
 
   DBMS_OUTPUT.put_line (t (1).n); 
END; 
/

BEGIN
   plsqlpuzzle_proc;
END;
/

100
100

In the first version, RETURNING BULK COLLECT INTO populates the collection with all the rows inserted (just happens to be one). In the second iteration, the non-bulk RETURNING-INTO populates a specific element in the collection at index value 1.

Either way, the code executed to display the value of t(1).n no longer throws a NO_DATA_FOUND exception and so they are both excellent solutions.

The RETURNING clause is a really lovely reminder of the tight integration between SQL and PL/SQL. Read lots more about it here.

Another Sort of Return

That use of RETURNING was very clever - I hadn't thought of it when I published the puzzle!

There is another way to achieve the desired effect (add just one statement and no unhandled exception): insert a RETURN statement.

Now, you might be saying: a RETURN statement? But this is a procedure, not a function! You would be absolutely right to say so. But did you know that you can also execute a RETURN inside a procedure? You just don't return anything but control to the outer block or host environment!

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 
   DBMS_OUTPUT.put_line ('Running plsqlpuzzle_proc');

   RETURN;
   
   SELECT * INTO r FROM plsqlpuzzle; 
 
   DBMS_OUTPUT.put_line (r.n); 
 
   DBMS_OUTPUT.put_line (t (1).n); 
END; 
/

BEGIN
   plsqlpuzzle_proc;
END;
/

Running plsqlpuzzle_proc

Two Other "Solutions"

I put that word in quotes because sell they are maybe valid solutions, maybe not, but worth mentioning.

One person proposed adding a one line exception section as you see below.

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 
   DBMS_OUTPUT.put_line ('Running plsqlpuzzle_proc');

   RETURN;
   
   SELECT * INTO r FROM plsqlpuzzle; 
 
   DBMS_OUTPUT.put_line (r.n); 
 
   DBMS_OUTPUT.put_line (t (1).n);
EXCEPTION WHEN OTHERS THEN NULL;  
END; 
/

BEGIN
   plsqlpuzzle_proc;
END;
/

Running plsqlpuzzle_proc

That avoids there procedure terminating with an unhandled exception. But as you will see on Twitter, I had to reject the solution. That's because while this is a single statement:

EXCEPTION WHEN OTHERS THEN is not a statement in PL/SQL. They are reserved words or key words that define the exception section and the WHEN clause.

Otherwise, a fine idea. Except of course this exception section should never be used in production code.

Finally, Philipp Salvisberg surprised absolutely no one by coming up with something else entirely - and testing the boundary edges of the quiz. Here's his code:

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 
   <<burn_cpu>> goto burn_cpu;
   
   SELECT * INTO r FROM plsqlpuzzle; 
 
   DBMS_OUTPUT.put_line (r.n); 
 
   DBMS_OUTPUT.put_line (t (1).n); 
END;
/

Notice the use of GOTO. He is essentially setting up a very tight infinite loop.

When you run this on LiveSQL, it will eventually terminate with the following error:

ORA-00040: active time limit exceeded - call aborted

When you run it in your own database, it will likely run for a much longer time. You will eventually  see that error or (more likely) you will terminate the session. :-)

Does this satisfy the conditions of the quiz? Weeelllll.....it doesn't terminate with an unhandled exception (a PL/SQL exception). It will eventually fail with an Oracle Database error. I will leave it to you to decide.

But at least now you know that there is a GOTO in PL/SQL and as with every other language out there, you should avoid it whenever possible.

You can run all of the above code in LiveSQL by running this script. Feel free, of course, to download and run it in your own database as well.

The Twitter Thread

 This puzzle was originally posted on Twitter. Here is the thread, discussion and solutions as they appeared.