DECLARE
CURSOR c1 IS SELECT sal, comm FROM emp;
ratio NUMBER;
BEGIN
FOR r1 IN c1
LOOP
ratio := r1.sal / NVL ( r1.comm, 1 );
IF ( ratio < 1 )
THEN
raise_application_error ( -20555
, 'Ratio was less than 1' );
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ( 'No data was found!' );
END;
And when ratio is more than 1, RAISE_APPLICATION_ERROR is called, and the proper error is raised and sent out of the block.
Then I add a WHEN OTHERS Clause in the exception section:
DECLARE
CURSOR c1 IS SELECT sal, comm FROM emp;
ratio NUMBER;
BEGIN
FOR r1 IN c1
LOOP
ratio := r1.sal / NVL ( r1.comm, 1 );
IF ( ratio < 1 )
THEN
raise_application_error ( -20555
, 'Ratio was less than 1' );
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ( 'No data was found!' );
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'I don''t want to see this message!' );
END;
And now when the ratio is more than 1, the WHEN OTHERS exception is raised instead of RAISE_APPLICATION_ERROR. Why do I get different behavior in my code for the same error condition?
Answer:
I will start with an explanation of what is going on in your code, including a quick overview regarding PL/SQL exception raising and handling behavior, and then finish up by pointing you to resources for further education and for best practice recommendations.
First of all, your blocks of code are performing exactly how you told them to perform (which, of course, is virtually always the case, since all the PL/SQL runtime engine can do is follow the commands you have given it!); you just need some additional clarity and understanding of what, in fact, you have told it do, and what you can then expect.
The RAISE_APPLICATION_ERROR built-in, actually defined in the DBMS_STANDARD package (one of the two default packages of PL/SQL, meaning you do not have to qualify references to elements of that package with the package name), is used to raise an application-specific error, usually to communicate that error back to a non-PL/SQL host environment (be it SQL*Plus, Oracle Forms, a Java program, etc.).
In the first block, you use this built-in to raise an exception, to which you assign the -20555 number and a related message.
Now, when an exception is raised, the current executable section shuts down and PL/SQL then checks to see if there is an exception section defined for that block. If so, it checks to see if there is a WHEN clause that will catch or handle the specific error raised.
Your only WHEN clause in the first block checks for NO_DATA_FOUND, whose SQL error codes are 100 and -1403 (yes, that's right: two different error codes for the same error, though at run-time only -1403 is used).
Your code, on the other hand, raises an error using RAISE_APPLICATION_ERROR. You specify the value -20555 as the error code. You have not associated that code with a PL/SQL exception using the EXCEPTION_INIT pragma (see below for more details on this pragma). The only way, therefore, that this error can be caught in the exception section is with a WHEN OTHERS clause. Without that, the error propagates out unhandled from the block and the SQLCODE function, when called, will return -20555.
In your second block, you have added a WHEN OTHERS handler. This is a catch-all handler that will catch any error not previously caught by another WHEN clause in the same exception section.
So when you run the second block, RAISE_APPLICATION_ERROR raises the ORA-20555 error, which is then caught by the WHEN OTHERS clause. Unless you include a RAISE; statement to re-raise the same exception out of the WHEN OTHERS handler, that exception information will be "swallowed up." Here is an example of using RAISE; to log the error occurrence, but then propagate it out to the enclosing block or host program:
EXCEPTION
WHEN OTHERS
THEN
log_the_error;
RAISE;
END;
One of the reasons you had trouble understanding the cause for the way your modified block behaved is that you are not distinguishing clearly enough between raising an exception and handling that exception. I could sense your confusion by this sentence:
"...the WHEN OTHERS exception is raised instead of RAISE_APPLICATION_ERROR."
Remember:
• The RAISE statement and the RAISE_APPLICATION_ERROR procedure both raise an exception.
• The EXCEPTION section with its WHEN clauses handle (or may handle) errors that are raised in the executable section of a block.
So...WHEN OTHERS is not an exception. It is not raised. It is simply used as a way to catch any "other" exceptions that have not already been handled in the exception section.
And RAISE_APPLICATION_ERROR itself is not raised. Instead, it raises the error you specify.
Finally, I mentioned the EXCEPTOIN_INIT pragma earlier. Let's look at an example of using this statement. Consider the following block of code:
SQL> DECLARE
2 my_bad EXCEPTION;
3 PRAGMA EXCEPTION_INIT ( my_bad, -20555 );
4 BEGIN
5 RAISE_APPLICATION_ERROR ( -20555, 'My bad' );
6 EXCEPTION
7 WHEN NO_DATA_FOUND
8 THEN
9 DBMS_OUTPUT.put_line ( 'No_Data_Found UNEXPECTEDLY handled' );
10 END;
11 /
On line 2 I declare an exception. On line 3 I associate that named exception with the -20555 error code. I then raise the -20555 error on line 5. Since I only have a handler for NO_DATA_FOUND, the error goes unhandled and within SQL*Plus I see this error stack:
ERROR at line 1:
ORA-20555: My bad
ORA-06512: at line 5
Notice that the stack contains both the error number and message I specified.
Now I will change that block of code to include a handler by name for my application-specific exception:
SQL> DECLARE
2 my_bad EXCEPTION;
3 PRAGMA EXCEPTION_INIT ( my_bad, -20555 );
4 BEGIN
5 RAISE_APPLICATION_ERROR ( -20555, 'My bad' );
6 EXCEPTION
7 WHEN NO_DATA_FOUND
8 THEN
9 DBMS_OUTPUT.put_line (
10 'No_Data_Found UNEXPECTEDLY handled' );
11 WHEN my_bad
12 THEN
13 DBMS_OUTPUT.put_line ( 'My_Bad handled' );
14 END;
15 /
When I run this block of code with serveroutput enabled, I see the following text:
My_Bad handled
The error was caught and the associated code was executed.
No comments:
Post a Comment