Obsessed with Oracle PL/SQL

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

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

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (and similar functionality in the UTL_CALL_STACK package) is a tremendously helpful function. It returns a formatted string that allows you to easily trace back to the line number on which an exception was raised. You know what else is really helpful? The automatic optimization performed by the PL/SQL compiler. The default level is 2, which does an awful lot of optimizing for you. But if you want to get the most out of the optimizer, you can ratchet it up to level 3, which then added subprogram inlining . Unfortunately, these two wonderful features don't mix all that well. Specifically, if you optimize at level 3, then the backtrace may not point all the way back to the line number in your "original" source code (without inlining, of course). Run this LiveSQL script to see the following code below "in action." ALTER SESSION SET plsql_optimize_level = 2 / CREATE OR REPLACE PROCEDURE proc1 IS l_level INTEGER; ...

Here goes: I execute this statement: CREATE OR REPLACE FUNCTION ndf RETURN NUMBER IS BEGIN RAISE NO_DATA_FOUND; END; / We all know what that function is going to do, right? #Fail, as one might say on Twitter. So the brain teaser is:  I n the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen. DECLARE n NUMBER; BEGIN <statement> DBMS_OUTPUT.PUT_LINE ( 'NDF? What NDF?' ); END; / I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post. Wait.... Wait.... Wait for it.... OK! After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer: You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unh...

Just received this feedback on an Oracle Magazine article : I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments. Now, I must confess that I am tempted to respond with such questions along these lines: Did you try it out? That's the best way to learn! But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-) The answer is quite straightforward: Comments do not interfere with "pinpointing the exact line number" on which the error was raised. But I bet you want proof, so let's get down to business. I create a procedure with comments and a RAISE statement: CREATE OR REPLACE PROCEDURE comments_throw_off_line# IS /* Here's my header Blah blah blah */ BEGIN DBMS_OU...

Use LOG ERRORS to suppress row-level errors from within the SQL engine when executing non-query DML statements (like inserts, updates and deletes). Instead of raising an error, the SQL engine will insert a row into your error log table - which you then must, really must check after statement execution to see if there were any problems. Or you will be hiding under the pillows, and your users will really not appreciate that. In contrast, if you use SAVE EXCEPTIONS with FORALL, you will suppress statement-level errors, but all changes made to rows identified by that statement are rolled back. Suppose I execute the following statements: CREATE TABLE plch_employees ( employee_id INTEGER PRIMARY KEY, last_name VARCHAR2 (100), salary NUMBER (3) ) / BEGIN INSERT INTO plch_employees VALUES (100, 'Sumac', 100); INSERT INTO plch_employees VALUES (200, 'Birch', 50); INSERT INTO plch_employees VALUES (300, 'Alde...

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