Obsessed with Oracle PL/SQL

I posted the following puzzle on Twitter : What change(s) can you make to this code so that "ORA-00001: unique constraint" appears on the screen after execution? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) Here are the answers from the TwitterSphere: Change line 5's assignment to dbms_sql.number_table(1=>1,2=>1) In other words, try to insert the same value twice. Since there is a unique index on the column, that will cause ORA-00001 to be raised. So that will do it, right? Wrong. Hans and Dirk both point out why that is not enough, and offer the second part of the solution: The value deposited in the error_code field of the SQL%BULK_EXCEPTIONS array is unsigned . In other words, 1 rather than -1 is stored. Unfortunately, the SQLERRM function assumes that the error code you pass it will be signed (negatively). So you must multiply the value in the pseudo-collection by -1. Then...

When it comes to PL/SQL puzzles via Twitter, I decided to change things up this week. I presented it as multiple choice this time. Here's the puzzle: After executing the code shown in the image what will be displayed on the screen (serveroutput is on!)? a. "No dummy" b. Unhandled VALUE_ERROR exception c. Unhandled NO_DATA_FOUND exception d. Compilation error e. Your guess is as good as mine. Before I unveil the amazing, mind-boggling answer....I will give you a moment to try to solve it yourself. OK. So the first inclination you might have as regards the output from this block is, quite logically, "No dummy!". After all, there are no rows in the dual table (or any other table for that matter) for which 1 is equal to 2. So that SELECT-INTO is going to raise a NO_DATA_FOUND exception. No doubt about that at all. And there's an exception handler for NO_DATA_FOUND (well, no_data_found, wait they are the same thing in PL/SQL! :-) ). So ...

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.PU...

A blog post about Oracle error codes? Curiosities about them, even? If you doubt that this might be possible or of interest, then answer these two questions: Can an error in Oracle have more than one error code? Are error codes positive or negative? If you answered "yes" for the first and "yes and no" for the second, you probably don't need to read this post. Oracle errors with more than one error code? Well that wouldn't be very normalized, would it? :-) But it is true that there at least one error that has two different error codes associated with it, and it's one of the most common "errors" you'll encounter in your code:  The NO_DATA_FOUND exception When I execute a SELECT-INTO statement, Oracle will raise NO_DATA_FOUND if no row is found for the query. It will raise TOO_MANY_ROWS if more than one row is found. So what error code is associated with NO_DATA_FOUND? The following code demonstrates this curiosity. I...

This question was submitted as a comment in one of my videos today: Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms? The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that  subprogram. I can certainly see why this question would come up. A package body can  have its own exception handler. Here's an example: CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE proc IS BEGIN RAISE NO_DATA_FOUND; END; BEGIN NULL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Was proc executed?'); END; / And it kinda, sorta looks like if I execute the following block, I will see "Was proc executed?" on my screen. BEGIN pkg.proc; END; / But I would be wrong. Instead, I will see: ORA-01403: no da...

The DBMS_UTILITY has long (since 10.2) offered three functions that are very handy when either tracing execution or logging errors: FORMAT_CALL_STACK - answering the question "How did I get here?" FORMAT_ERROR_STACK - answering the question "What was the error?" (or a stack of errors, depending on the situation) FORMAT_ERROR_BACKTRACE - answering the question "On what line was my error raised?" Therefore (and prior to 12.2), if you wanted to get the error information + the line number on which the error was raised, you would need to call both of the "*ERROR*" as in: CREATE OR REPLACE PROCEDURE p3 AUTHID DEFINER IS BEGIN p2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); RAISE; END; Of course, in the real world, you would not display the text on the screen. You would write them to a log table via an autonomous tra...

Oracle Database raises an exception when something goes wrong (examples: divide by zero, duplicate value on unique index, value too large to fit in variable, etc.). You can also raise exceptions when an application error occurs (examples: balance too low, person not young enough, department ID is null). If that exception occurs in your code, you have to make a decision: Should I handle the exception and let it propagate out unhandled? You should let it propagate unhandled (that is, not even trap it and re-raise) if handling it in that subprogram or block will not "add value" - there are no local variables whose values you need to log, for example. The reason I say this is that at any point up the stack of subprogram invocations, you can always call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it will trace back to the line number on which the error was originally raised. If you are going to handle the exception, you have to make several decisions: Should I log the e...

How Do I (or Oracle) Raise NO_DATA_FOUND?  "Let me count the ways." The brain teaser is: what are any or all of the ways that the NO_DATA_FOUND exception can be raised in a PL/SQL block? Extra bonus points if you can provide an example in two rhyming lines, as you will find in the sonnet that inspired this brain teaser: How Do I Love Thee? (Sonnet 43) by Elizabeth Barrett Browning, 1806 - 1861 How do I love thee? Let me count the ways. I love thee to the depth and breadth and height My soul can reach, when feeling out of sight For the ends of being and ideal grace. I love thee to the level of every day’s Most quiet need, by sun and candle-light. I love thee freely, as men strive for right. I love thee purely, as they turn from praise. I love thee with the passion put to use In my old griefs, and with my childhood’s faith. I love thee with a love I seemed to lose With my lost saints. I love thee with the breath, Smiles, tears, of all my life; and, if Go...

Programmers need to keep lots of information in their heads: about the language(s) with which they are writing code, the data model of their application, requirements, etc. So they tend to not remember (and even forget rather quickly) information they are pretty sure is not in their critical path. I was reminded of this very forcefully at a two day course I gave in the Netherlands a few years ago. On the first day, I talked about FORALL and SAVE EXCEPTIONS , and how Oracle would raise ORA-24381 if at least one statement failed in the FORALL's execution. Then at the end of the second day, when I discussed maintainability, I again talked about ORA-24381 (for reasons that will become clear below). And then, then it was time for the end-of-course ten-part quiz, with reputation and prizes on the line. Lo and behold, when the dust settled, we had a tie for first place. So then it was time for a sudden death playoff. Whoever gave me the right answer first, wins. I showed "-24...

Fielded a question the other day from a frustrated PL/SQL developer. He included an exception handler for an exception that was being raised in his program, but the exception propagated out unhandled from the block anyway. What's with that ? To answer that question, why not test your own knowledge of PL/SQL: what will you see on the screen when you execute the following block (with server output enabled): [Note, sure you could copy and paste the code and run it, but I urge you, I implore you, to just read the code and see if you can sort it out yourself. Reading code - verifying things logically - is a critical skill for any developer to, um, develop.] DECLARE aname VARCHAR2(50); BEGIN DECLARE aname VARCHAR2(5) := 'Big String'; BEGIN DBMS_OUTPUT.PUT_LINE (aname); EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE ('Inner block'); END; DBMS_OUTPUT.put_line (SQLCODE); DBMS_OUTPUT.PUT_LINE ('What er...