Let's go through these objections and see how much we can learn about PL/SQL through the process. Player comments are in blue. My response is in purple.
Choice 8990: Both the PEI and RAE implementations allow you to set the error code to one that is not used by Oracle and is returned by a call to SQLCODE.
A player wrote
1. PEI allows code from -20000...-20999, but not only such codes;
2. Several codes from -20000...-20999 is used by Oracle, for example: "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes"
3. Is it unambiguous to say that outside this range all codes are used by Oracle, for example, does "ORA-22567: Message 22567 not found; product=RDBMS; facility=ORA" mean that it's used by Oracle?
My response: you are absolutely right that Oracle does, in a few packages, use some of "our" error codes, such as ORA-20000 and ORA-20000. I've always felt that this was rude behavior on Oracle's part. We only get 1000 error codes with which to work; surely, you could leave all of those to us! So good point, but I don't think it makes this choice wrong in any way. With both those implementations, I can choose to set the error code to one that is not used by Oracle (such as -20704). The choice does not claim that it is impossible for me to choose a code that Oracle also uses.
As to which codes Oracle "uses" - no, I would say that at least for now, -22567 is not in use. But it is certainly the case that Oracle could at some point use these error codes - and we cannot.
Choice 8989: PEI and VE offer "generic" Oracle error messages, while RAE provides an application-specific error message.
Two players raised questions about this choice, and both circle back to the use of the word "generic". My intention behind the use of this word, combined with the "Oracle error message" phrase, is that these are the error messages returned by Oracle and are the same across all installations of Oracle.
I marked answer 8989 as "Incorrect" because PEI uses an application-specific exception - i.e. it's not a "generic" Oracle error. I didn't realize this answer was about the error *message text* in particular. Seems like this answer was a bit ambiguous.
and
I disagree that "RAISE VALUE_ERROR" raises a "generic" error. It raises the very specific error associated with ORA-06502.
and
When you use PRAGMA EXCEPTION_INIT to change the error code of a user-defined exception, then the error message returned by SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK is not generic, it is blank. So I’d suggest that while VE offers a "generic" Oracle error message and RAE provides an application-specific error message that PEI does neither.
My response: the text of the choice is clearly about messages. And my point in this choice is that with the EXCEPTION_INIT pragma, you can change the error code associated with a named exception, but you cannot change the error message. As for VALUE_ERROR resulting in a specific rather than generic error message, I could understand this objection if I wrote the choice as follows:
PEI and VE offer a single "generic" Oracle error message, while RAE provides an application-specific error message.
That is, if I said or implied there was just one "generic" message. But I use the plural form, so I feel it is clear that I am talking about the error messages returned by Oracle, which cannot be changed by the developer with EXCEPTION_INIT or RAISE.
So is a blank message "generic"? If you do not use EXCEPTION_INIT with a user-defined exception, then the error message is, well, generic: "User-defined error". When you assign a different error code to a user-defined exception, the error message is then blank. Gee, I don't know, that seems rather generic to me!
Your thoughts?
Especially the error code -20704 made me thinking a little bit about the answer, because this code is used by Oracle in the package SYSMAN.MGMT_GLOBAL for the BLACKOUT_NOT_STOPPED exception.
ReplyDeleteGreetings and happy new year,
Niels Hecker
Thanks for pointing this out, Niels. I didn't know about the SYSMAN.MGMT_GLOBAL package, and am amazed to see how many of the -20NNN error codes it uses.
ReplyDeleteI continue to be surprised at how Oracle "trespasses" into error codes that they seem to be reserving for users of Oracle technology.
It is still true, however, that by using RAISE_APPLICATION_ERROR, you can choose to use a code that Oracle does not - surely there must be at least ONE integer between -20999 and -20000 that is not used by Oracle somewhere!
SF
Hello Steven, All,
ReplyDeleteNiels' remark made me start thinking about an issue that is already wider than the one of Oracle using "our" exception range in some of its own packages (well, ultimately ... they are kind of "user-like" packages, that Oracle just developed "for us" as a gift !).
Using centralized packages in applications for defining exceptions is universally considered as a best practice, because it offers first of all the encapsulation (hiding) of the hard-coded values used for the exception, theoretically "sparing" from a developer that uses those packages the need to know what are the exact values used behind each exception and offering the possibility to use standard exception names only.
However, if we "push this issue" too far, that is, if it happens that we have conflicting exception values in different packages, than the following interesting scenario might happen:
CREATE OR REPLACE PACKAGE my_pkg1
AS
my_exception1 EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception1,-20704);
END my_pkg1;
/
Package created.
CREATE OR REPLACE PACKAGE my_pkg2
AS
my_exception2 EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception2,-20704);
END my_pkg2;
/
Package created.
-- just testing the above
DECLARE
balance_in NUMBER := &BAL;
BEGIN
CASE
WHEN balance_in < 1000 THEN
RAISE my_pkg1.my_exception1;
WHEN balance_in < 2000 THEN
RAISE my_pkg2.my_exception2;
ELSE
DBMS_OUTPUT.put_line('OK !');
END CASE;
EXCEPTION
WHEN my_pkg1.my_exception1 THEN
DBMS_OUTPUT.put_line(
'This is my_pkg1.my_exception1 !');
WHEN my_pkg2.my_exception2 THEN
DBMS_OUTPUT.put_line(
'This is my_pkg2.my_exception2 !');
END;
/
Enter value for bal: 500
old 2: balance_in NUMBER := &BAL;
new 2: balance_in NUMBER := 500;
WHEN my_pkg2.my_exception2 THEN
*
ERROR at line 15:
ORA-06550: line 15, column 6:
PLS-00484: redundant exceptions 'MY_EXCEPTION1' and 'MY_EXCEPTION2' must appear
in same exception handler
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
If for custom applications we usually demand that some "centralized control" be exercised at the entire application level (which in the above case would mean of course avoiding to use the same error code values in the two packages), it is somehow easier to fail upon a similar situation in which a value used
for one of our custom exceptions is the same with a value used in an Oracle supplied package
and it happens that we check both those exceptions in the same exception handler section, like above.
By the way, the above "reaction" of the PL/SQL compiler to this issue may also be considered
a matter of implementation, because, usually, in statements whose syntax includes several WHEN clauses (or branches), it is customary to choose the first one for which the condition is TRUE.
I'm not sure whether in this case such an implementation, that would have NOT produced an error, would have been better or worse than the actual one.
So, does this means that "encapsulation does have some limits", at least on a theoretical level ?
A good question, Steven, we will be grateful to hear what do you think of this issue ?
Thanks a lot and a joyous, healthy, prosperous and full of achievements 2012 to everybody !
Iudith Mentzel
Interesting "find", Iudith. Note that this is a compile error. In other words, if you try to compile a procedure like this:
ReplyDeleteCREATE OR REPLACE PROCEDURE plch_dupexc
IS
BEGIN
NULL;
EXCEPTION
WHEN my_pkg1.my_exception1
THEN
DBMS_OUTPUT.put_line (
'This is my_pkg1.my_exception1 !');
WHEN my_pkg2.my_exception2
THEN
DBMS_OUTPUT.put_line (
'This is my_pkg2.my_exception2 !');
END;
/
It fails with the PLS-484 error. That error message is interesting: "must appear in same exception handler."
I believe that what Oracle is saying here is that if you really want to trap for both those named exceptions, you'll have to do it as an OR:
WHEN my_pkg1.my_exception1 OR my_pkg2.my_exception2
but that seems like odd advice to give.
Hello Steven,
ReplyDeleteYes, that is true that AFTER the compiler "explains you" what the problem is,
then you can correct it by using a single exception handler, with an OR operator.
But that would mean that the SAME exception handling code will be performed for BOTH
exceptions, which in fact may be very different ones, meant for completely different
purposes, with the only problem that they happened to use the same error code number,
in most cases without "knowing" of each other.
And that is already a problem !
I think that maybe the PL/SQL Compiler should have had to somehow manage
those exceptions that do have explicit exception declarations by name and not only
by error code, and thus to allow different exception handlers for the different exception
names, even if they happen to use the same error code.
That would be like managing the exceptions as a kind of object type, so that each declared exception would be considered a separate object, even if it has the same attributes (error code, message) like another exception.
Such an approach may require a concept change also for the RAISE_APPLICATION_ERROR procedure,
which perhaps can be overloaded to allow an exception as an argument, a.s.o.,
but that's already another perspective.
In fact, when you start learning PL/SQL and even for some time after this,
the impression could be that RAISE and RAISE_APPLICATION_ERROR are two completely different things, one deals with an exception while the other is a procedure, a.s.o.
just to finally understand that they are closely related and that exception handling
is in fact a single common issue, no matter how that exception is raised.
Thanks a lot & Best Regards,
Iudith
A user defined exception is unique unless the EXCEPTION_INIT pragma is used to associate the exception name with a specific error code. When multiple exception names are associated with the same error code, then references to any of the exception names are semantically equivalent. If the user defined exception is only used from within PL/SQL, then associating the exception with an error code may be unnecessary and even undesirable since it creates an additional dependency. Unfortunately, all user defined exceptions not associated with an error code appear the same to external callers (such as Java). RAISE and RAISE_APPLICATION_ERROR can be viewed as satisfying separate but related roles: RAISE is a programmer’s tool for signaling that an exception has occurred, and RAISE_APPLICATION_ERROR provides a mechanism for reporting exceptions in the language of the application domain.
ReplyDeleteOracle DB 11gR2 supports one million error codes via the EXCEPTION_INIT pragma: 100, and any negative integer greater than or equal to -1000000 except -1403 (Oracle’s documentation incorrectly states "any negative integer greater than -10000000"). Only integer codes in the range -20999 through -20000, inclusive, are declared as reserved for user programs and only these values may be used with RAISE_APPLICATION_ERROR. A large application could easily have more than a thousand possible distinct exceptions so some mechanism must be devised to support them. Once possible approach would be to map each exception to an error code indicating the category of error encountered (e.g., resource unavailable, security violation, etc.) and descriptive text. The resulting error code and text could then be passed to RAISE_APPLICATION_ERROR. This mapping approach could easily be extended to support internationalization of error messages.
One wonders about the reasoning behind reporting PLS-00484 as an error when the similar situation of unreachable code is reported as a warning (PLW-06002). Since either situation may indicate the presence of a programming error, both are syntactically valid, and neither are semantically ambiguous, it would seem that they should be handled in like fashion. I agree with Stephen, the proposed resolution suggested by the PLS-00484 error message seems odd. Since all references to exceptions associated with a common error code are semantically equivalent, there is no logical reason to have more than one reference in the exception handler. Perhaps the message should have been "redundant exceptions encountered in EXCEPTION block." The cleanest correction is to remove, not move, the redundancy.
Iudith’s query regarding the limitations of encapsulation touches on an issue associated with the propagation of exceptions out of called routines; specifically, the exposure of, and dependence on, implementation details related to the set of potential exceptions. This is one of the criticisms levied against Java’s checked exceptions and one which applies to any exception system that depends on the caller's awareness of which exceptions might be raised by a called routine. The details of an exception and the circumstances that caused it may be important to users, support staff, and developers; however, except for the need to communicate exception details to the user, program logic seldom needs to know any more than that the called routine failed.