tag:blogger.com,1999:blog-8677649049588007585.post6944004593024092980..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: What is a "Generic" Oracle Error Message? (9614)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-8677649049588007585.post-83245426823020997782012-01-03T22:43:13.970+00:002012-01-03T22:43:13.970+00:00A user defined exception is unique unless the EXCE...A user defined exception is unique unless the <i>EXCEPTION_INIT</i> 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). <i>RAISE</i> and <i>RAISE_APPLICATION_ERROR</i> can be viewed as satisfying separate but related roles: <i>RAISE</i> is a programmer’s tool for signaling that an exception has occurred, and <i>RAISE_APPLICATION_ERROR</i> provides a mechanism for reporting exceptions in the language of the application domain.<br /><br />Oracle DB 11gR2 supports one million error codes via the <i>EXCEPTION_INIT</i> 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 <i>RAISE_APPLICATION_ERROR</i>. 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 <i>RAISE_APPLICATION_ERROR</i>. This mapping approach could easily be extended to support internationalization of error messages.<br /><br />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 <b>remove</b>, not <b>move</b>, the redundancy.<br /><br />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.jhall62https://www.blogger.com/profile/10339038131928463003noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-52700232526511745252012-01-03T09:19:12.454+00:002012-01-03T09:19:12.454+00:00Hello Steven,
Yes, that is true that AFTER the co...Hello Steven,<br /><br />Yes, that is true that AFTER the compiler "explains you" what the problem is,<br />then you can correct it by using a single exception handler, with an OR operator.<br /><br />But that would mean that the SAME exception handling code will be performed for BOTH<br />exceptions, which in fact may be very different ones, meant for completely different<br />purposes, with the only problem that they happened to use the same error code number,<br />in most cases without "knowing" of each other.<br />And that is already a problem !<br /><br />I think that maybe the PL/SQL Compiler should have had to somehow manage <br />those exceptions that do have explicit exception declarations by name and not only <br />by error code, and thus to allow different exception handlers for the different exception<br />names, even if they happen to use the same error code.<br /><br />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.<br /><br />Such an approach may require a concept change also for the RAISE_APPLICATION_ERROR procedure,<br />which perhaps can be overloaded to allow an exception as an argument, a.s.o.,<br />but that's already another perspective.<br /><br />In fact, when you start learning PL/SQL and even for some time after this,<br />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.<br />just to finally understand that they are closely related and that exception handling<br />is in fact a single common issue, no matter how that exception is raised.<br /><br />Thanks a lot & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-15381685974898131832012-01-02T19:46:37.795+00:002012-01-02T19:46:37.795+00:00Interesting "find", Iudith. Note that th...Interesting "find", Iudith. Note that this is a compile error. In other words, if you try to compile a procedure like this:<br /><br />CREATE OR REPLACE PROCEDURE plch_dupexc<br />IS<br />BEGIN<br /> NULL;<br />EXCEPTION<br /> WHEN my_pkg1.my_exception1<br /> THEN<br /> DBMS_OUTPUT.put_line (<br /> 'This is my_pkg1.my_exception1 !');<br /> WHEN my_pkg2.my_exception2<br /> THEN<br /> DBMS_OUTPUT.put_line (<br /> 'This is my_pkg2.my_exception2 !');<br />END;<br />/<br /><br />It fails with the PLS-484 error. That error message is interesting: "must appear in same exception handler."<br /><br />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:<br /><br />WHEN my_pkg1.my_exception1 OR my_pkg2.my_exception2<br /><br />but that seems like odd advice to give.Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-38691530926739558262012-01-02T19:01:34.931+00:002012-01-02T19:01:34.931+00:00Hello Steven, All,
Niels' remark made me star...Hello Steven, All,<br /><br />Niels' 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 !).<br /><br />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.<br /><br />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:<br /><br /><br />CREATE OR REPLACE PACKAGE my_pkg1<br />AS<br /> my_exception1 EXCEPTION;<br /> PRAGMA EXCEPTION_INIT (my_exception1,-20704);<br />END my_pkg1;<br />/<br /><br />Package created.<br /><br />CREATE OR REPLACE PACKAGE my_pkg2<br />AS<br /> my_exception2 EXCEPTION;<br /> PRAGMA EXCEPTION_INIT (my_exception2,-20704);<br />END my_pkg2;<br />/<br /><br />Package created.<br /><br /><br />-- just testing the above<br />DECLARE<br /> balance_in NUMBER := &BAL;<br />BEGIN<br /> CASE <br /> WHEN balance_in < 1000 THEN <br /> RAISE my_pkg1.my_exception1;<br /> WHEN balance_in < 2000 THEN<br /> RAISE my_pkg2.my_exception2;<br /> ELSE<br /> DBMS_OUTPUT.put_line('OK !');<br /> END CASE;<br />EXCEPTION<br /> WHEN my_pkg1.my_exception1 THEN<br /> DBMS_OUTPUT.put_line(<br /> 'This is my_pkg1.my_exception1 !');<br /> WHEN my_pkg2.my_exception2 THEN<br /> DBMS_OUTPUT.put_line(<br /> 'This is my_pkg2.my_exception2 !');<br />END;<br />/<br />Enter value for bal: 500<br />old 2: balance_in NUMBER := &BAL;<br />new 2: balance_in NUMBER := 500;<br /> WHEN my_pkg2.my_exception2 THEN<br /> *<br />ERROR at line 15:<br />ORA-06550: line 15, column 6:<br />PLS-00484: redundant exceptions 'MY_EXCEPTION1' and 'MY_EXCEPTION2' must appear<br />in same exception handler<br />ORA-06550: line 0, column 0:<br />PL/SQL: Compilation unit analysis terminated<br /><br /><br />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<br />for one of our custom exceptions is the same with a value used in an Oracle supplied package<br />and it happens that we check both those exceptions in the same exception handler section, like above.<br /><br />By the way, the above "reaction" of the PL/SQL compiler to this issue may also be considered<br />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.<br />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.<br /><br />So, does this means that "encapsulation does have some limits", at least on a theoretical level ?<br /><br />A good question, Steven, we will be grateful to hear what do you think of this issue ?<br /><br />Thanks a lot and a joyous, healthy, prosperous and full of achievements 2012 to everybody !<br /><br />Iudith Mentzeliudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-29023396931579312202012-01-02T15:17:09.956+00:002012-01-02T15:17:09.956+00:00Thanks for pointing this out, Niels. I didn't ...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. <br /><br />I continue to be surprised at how Oracle "trespasses" into error codes that they seem to be reserving for users of Oracle technology.<br /><br />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!<br /><br />SFSteven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-80297697130738323962012-01-02T10:00:35.973+00:002012-01-02T10:00:35.973+00:00Especially the error code -20704 made me thinking ...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.<br /><br />Greetings and happy new year,<br />Niels HeckerAnonymousnoreply@blogger.com