Skip to main content

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.

Comments

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post.

PL/SQL is a strongly-typed language. Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type).

PL/SQL offers a wide array of pre-defined data types, both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package).

Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types.

You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the perspective…

Table Functions, Part 1: Introduction and Exploration

Please do feel encouraged to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!


Table functions - functions that can be called in the FROM clause of a query from inside the TABLE operator - are fascinating and incredibly helpful constructs.

So I've decided to write a series of blog posts on them: how to build them, how to use them, issues you might run into.

Of course, I am not the first to do so. I encourage to check out the documentation, as well as excellent posts from Adrian Billington (search for "table functions") and Tim Hall. Adrian and Tim mostly focus on pipelined table functions, a specialized variant of table functions designed to improve performance and reduce PGA consumption. I will take a look at pipelined table functions in the latter part of this seri…