Skip to main content

PL/SQL Puzzle: Getting the "right" error message to appear

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 SQLERRM will return the right string.

Or as Dirk puts it:

(sqlerrm (sql%bulk_exceptions (indx).error_code));

must be

(sqlerrm (0 - sql%bulk_exceptions (indx).error_code));

You don't really need the 0, though. You can write simply:

(sqlerrm (-sql%bulk_exceptions (indx).error_code));

You can see all these variations at work in my LiveSQL script.

Actually, I was surprised that I did not receive any "silly" answers. After all, there are lots of ways to get "ORA-00001: unique constraint" to appear on the screen, such as:

DECLARE 
   two_ts DBMS_SQL.number_table := DBMS_SQL.number_table (1=>1,2=>2); 
BEGIN 
   DBMS_OUTPUT.PUT_LINE ('ORA-00001: unique constraint');
   RETURN;
   
   FORALL indx IN 1 .. 2 
   SAVE EXCEPTIONS 
      INSERT INTO t VALUES (two_ts(indx)); 
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT || ' inserted'); 
   ROLLBACK; 
EXCEPTION 
   WHEN others 
   THEN 
      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT 
      LOOP 
         DBMS_OUTPUT.put_line 
            (SQLERRM (SQL%BULK_EXCEPTIONS (indx).ERROR_CODE)); 
      END LOOP; 
END;

Yes, that's right, simply display it on the screen and then shortcut everything else with RETURN; (or not, let the rest of the code execute unchanged).

I guess it was a serious week for the Oracle Database developer community. :-)
Labels:

Comments

Post a Comment

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more