|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Jul
14
Written by:
StevenFeuersteinTW
Wednesday, July 14, 2010 1:02 PM
A PL/SQL developer just sent me the following question:
"I can't understand the difference between RAISE_APPLICATION_ERROR and RAISE. Why would I use one vs. the other?"
Instead of answering via an email to just one developer, I thought I might share my answer with everyone.
The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user.
Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger:
TRIGGER employees_minsal_tr
BEFORE INSERT OR UPDATE
ON employees
FOR EACH ROW
BEGIN
IF :new.salary < 100000
THEN
/* communicate error */
NULL;
END IF;
END;
I can stop the DML from completing by issuing a RAISE statement, such as:
RAISE PROGRAM_ERROR;
But I would not be able to communicate back to the user what the actual problem was.
If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in:
TRIGGER employees_minsal_tr
BEFORE INSERT OR UPDATE
ON employees
FOR EACH ROW
BEGIN
IF :new.salary < 1000000
THEN
RAISE_APPLICATION_ERROR (-20000,
'Salary of '|| :new.salary ||
' is too low. It must be at least $100,000.');
END IF;
END;
And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.
Use RAISE when you want to raise an already-defined exception, whether one of Oracle's (such as NO_DATA_FOUND) or one of your definition, as in:
DECLARE
e_bad_value EXCEPTION;
BEGIN
RAISE e_bad_value;
END;
but if it is one of your own user-defined exceptions, it only makes sense to raise it this way if you are going to trap it inside the backend as well, and then do something in response to the error.
If you let the e_bad_value exception propagate out to your users, all they will know about the error is that the error code is 1 and the error message is "User-defined Exception". Not very helpful.
5 comment(s) so far...
Re: RAISE vs RAISE_APPLICATION_ERROR?
Thank you very much Sir.
By satya82 on
Thursday, July 15, 2010 8:24 AM
|
Re: RAISE vs RAISE_APPLICATION_ERROR?
The IF test has too many zeroes: it's actually checking for a minimum salary of $1,000,000!
Wouldn't THAT be nice!
By AndyDan on
Monday, August 02, 2010 12:37 PM
|
Re: RAISE vs RAISE_APPLICATION_ERROR?
Very helpful. Just noticed a typo error on the second script on the blog - it says "RAISE_APPLICATIONE_ERROR".
By DamerBullet on
Thursday, September 23, 2010 7:18 AM
|
Re: RAISE vs RAISE_APPLICATION_ERROR?
I got the concept between raise and raise_application_error but just for your last point
"If you let the e_bad_value exception propagate out to your users, all they will know about the error is that the error code is 1 and the error message is "User-defined Exception". Not very helpful."
If I use dbms_output.put_line to display the custom error message from exception handler then will this make any sence, knowing that I will be using the custom exception only for a specific purpose?
Is there anything extra we are getting with raise_application_error instead using raise with dbms_output.put_line?
By VISHALAKSHA on
Wednesday, December 01, 2010 2:01 PM
|
Re: RAISE vs RAISE_APPLICATION_ERROR?
Generally, I would not rely on PUT_LINE to communicate information back to a user. If this is "just" an anonymous block/script you are running, then I suppose there isn't a big difference. But RAE has the advantage of setting the error code and error message, so a call to SQLCODE and SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK returns YOUR values, which can then be accessed and presented to the user in a more structured fashion than output to a screen.
By StevenFeuersteinTW on
Tuesday, December 07, 2010 8:59 AM
|
|
|