Collaborate08, annual conference of the International Oracle User Group, and several other national and international user groups, will be held in Denver this year, from April 13th to the 16th. I am going to present three papers, including (for the first time) Weird PL/SQL. I thought you might enjoy reading about some of the weirdnesses of PL/SQL in my ToadWorld blog, so here's an excerpt from the beginning of my whitepaper:
Introduction
You probably think that PL/SQL is rather ordinary programming language. Well, it truly is a very powerful and straightforward language, but there are some features "less traveled" which can seem nothing less than weird. This presentation explores some of stranger nooks and crannies of the PL/SQL language, perhaps in the process making them a little bit less weird to the everyday programmer.
All the code I reference in this paper is available at my PL/SQL Obsession site: www.ToadWorld.com/SF. Just click on the "Trainings" link and then click on the "demo.zip" link.
Disclosure: I will poke fun at Oracle, the PL/SQL language, and implicitly the folks who are responsible for building and enhancing PL/SQL. Please know that it is all done in a spirit of deep appreciation for what PL/SQL (and its developers) has done for me and so many others around the world. But, hey, there's always room for improvement and you've got to keep a sense of humor about all this stuff!
Error Codes: Negative or Positive? Make up your mind, Oracle!
I really would have thought that Oracle would have sorted this out by now. Here's the issue:
When you see this string:
ORA-01855
do you interpret the "-" character as a hyphen or a negative sign?
I always considered it to be a negative sign, and that just about every single error code in the world of Oracle is negative. The only exceptions are 1 (user-defined exception) and 100 ("No data found" – which is another little weirdness in PL/SQL. The "No data found" exception has two error codes: 100 and -1403).
Yet sometimes Oracle treats the error codes as positive numbers, most notably when using the SAVE EXCEPTIONS clause of FORALL. When you include SAVE EXCEPTIONS, Oracle "saves up" any exceptions it encounters as it executes all the DML statements specified by the bind array. Then if at least one error occurred, it raises the ORA-24381 exception (Argh! Sometimes I worry about all the little bits of information that are stuck in my head!). It also populates the SQL%BULK_EXCEPTIONS pseudo-collection with all the exceptions that were raised.
Here's an example of a program that uses this feature:
/* bulkexc.sql */
DECLARE
bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
TYPE namelist_t IS TABLE OF VARCHAR2 (1000);
enames_with_errors namelist_t
:= namelist_t ('ABC'
, 'DEF'
, NULL
, 'LITTLE'
, RPAD ('BIGBIGGERBIGGEST', 250, 'ABC')
, 'SMITHIE'
);
BEGIN
FORALL indx IN enames_with_errors.FIRST .. enames_with_errors.LAST SAVE EXCEPTIONS
UPDATE employees
SET last_name = enames_with_errors (indx);
ROLLBACK;
EXCEPTION
WHEN bulk_errors
THEN
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line ( 'Error '
|| indx
|| ' occurred on index '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ' with error code '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
);
END LOOP;
ROLLBACK;
END;
/
And this is what I see when I run the program:
Error 1 occurred on index 3 with error code 1407
Error 2 occurred on index 5 with error code 12899
Hmmm. Oracle returns the error code as a positive number! So if I want to use SQLERRM to look up the error message for that code, I have to multiply it by -1:
FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.put_line
( 'Error '
|| indx
|| ' occurred on index '
|| SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
|| ' with error '
|| SQLERRM
( -1
* SQL%BULK_EXCEPTIONS (indx).ERROR_CODE
)
);
END LOOP;
And then I see this output:
Error 1 occurred on index 3 with error ORA-01407: cannot update () to NULL
Error 2 occurred on index 5 with error ORA-12899: value too large for column
It would be nice if Larry would decide once and for all if error codes are positive or negative, and then lay down the law.
SQLERRM and DBMS_OUTPUT.PUT_LINE
Speaking of SQLERRM....let's talk about everybody's favorite program: DBMS_OUTPUT.PUT_LINE. For many, many years – all the way up to the release of Oracle Database 10g Release 2, if you tried to display a string of more than 255 characters with DBMS_OUTPUT.PUT_LINE, that built-in would raise an exception. This caused no end of teeth-gnashing and anguished moans from developers over the year. Fortunately, that restriction was lifted in Oracle Database 10g Release 2 – you can now display up to 32K characters.
But there is still a problem with SQLERRM. We all know about SQLERRM. You call it to return the error message for the current error (obtained by calling SQLCODE). Did you know, however, that Oracle recommends that you not use this function,. and instead call DBMS_UTILITY.FORMAT_ERROR_STACK?
Why would that be? The problem is that SQLERRM may truncate your error message. In earlier versions of Oracle, truncated occurred at 255 characters. Now, it is 512. DBMS_UTILITY.FORMAT_ERROR_STACK, on the other hand, returns strings of up to 2000 bytes.
I can just picture some developer years and years ago, confronted with a nasty problem:
He has found that SQLERRM can return error messages that get quite long, depending on application-specific information, like the names of identifiers. And when you try to display that string with DBMS_OUTPUT.PUT_LINE, an exception is raised.
What's a developer to do?
1. Fix DBMS_OUTPUT.PUT_LINE so it displays longer strings.
or
2. Truncate your error message.
Well, obviously, the solution is to truncate the error message!
Isn't that weird?
I am so glad the PL/SQL team finally got around to fixing DBMS_OUTPUT.PUT_LINE! And according to the PL/SQL development manager at the time, all they had to was change one number!
So...there's a little bit of weirdness in PL/SQL...more to come at Collaborate08 and this blog.