{{announcement.body}}
{{announcement.title}}

PL/SQL Puzzle: Add One Statement to Stop Exceptions

DZone 's Guide to

PL/SQL Puzzle: Add One Statement to Stop Exceptions

Do you have the missing piece to the PL/SQL puzzle?

· Database Zone ·
Free Resource

PL/SQL Puzzle

Can you solve the PL/SQL piece of the puzzle?

OK, folks, here's a PL/SQL puzzle. It was originally posted on Twitter, so I will 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.

You may also like: A Quick Guide to Writing Dynamic SQL in PL/SQL 

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


Below, I offer 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? Right?! Well, I hope so!

If, by chance, you have a solution that we do not show below, please add it via the comments. 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. I 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 keywords 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.

Further Reading

A Quick Guide to Writing Dynamic SQL in PL/SQL 

Nine Good-to-Knows About Oracle PL/SQL Error Management

Topics:
database ,plsql ,mysql ,statement ,exceptions

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}