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
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
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
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
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;
/
When you run this on LiveSQL, it will eventually terminate with the following error:
ORA-00040: active time limit exceeded - call aborted
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.
First of all I would use BULK COLLECT directly in the SELECT.
ReplyDeleteThis avoids ORA-1403 at the original line, but causes ORA-01403 at the last DBMS_OUTPUT. Then you can handle the null nested table inside a CASE function...
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 * BULK COLLECT INTO t FROM plsqlpuzzle;
-- SELECT * INTO r FROM plsqlpuzzle;
DBMS_OUTPUT.put_line (r.n);
DBMS_OUTPUT.put_line (case when t.count > 0 then t(1).n else null end);
END;