Hello all,
A lot have been said on this, but I’m still confused about this topic.
Steven in one on his publications says:
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.
And my question is: why not? Can’t we communicate with the user through the exception handler and the dbms_output package, like this:
…
exception
when PROGRAM_ERROR then
dbms_output.put_line(‘An error occurred my friend!’);
end;
And vice versa, when Steven goes on to say:
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.
I ask myself yet again, why can't we send the user the same message, but from the exception handler with the help of the dbms_output package?.. Like this:
DECLARE
e1 exception;
pragma exception_init(e1, -20001);
BEGIN
-- some code
RAISE_APPLICATION_ERROR (-20000,
'Salary of '|| :new.salary ||
' is too low. It must be at least $100,000.');
EXCEPTION
when e1 then
dbms_output.put_line(‘Salary is too low. It must be at least $100,000’);
END;
Why send this message from raise_application_error?
What is the difference between sending a message to the user from dbms_output or from raise_application_error?
I am missing something obvious here I guess.
Help please?