Pragma exception init associates a user defined exception(i.e., oracle has not given any predefined name to it. example: dead lock error)
to oracle defined error number. whereas in raise_application_error user can provide the error number and message.
(error numbers ranging from -20000 to -20999) . you can use this to raise errors that you define as per your application logic
i have gone through the link and following statement from tom is not clear to me.could anybody elaborate on it
Raise_application_error is used to RAISE an error - exception_init is used to deal with
errors (i guess you could say they are opposites in a way).
I use raise_application_error when I want to setup a custom error message to be returned
to a client application
raise_application_error( -20001, 'You cannot do that!' );
If I just fling a named exception, the client gets the truly usless error message
"unhandled user defined exception". If I use raise_application_error -- the client gets
whatever I send back.
EXCEPTION_INIT is helps to assign a error number to the exception and which can be raised using RAISE and can be handled in EXCEPTION block.
RAISE_APPLICATION_ERROR is used to return a valid error message to the calling application like Java, .NET,...
See the Ex.
CREATE TABLE EMP
ENAME VARCHAR2(10 BYTE),
Insert into EMP Values (1, 'JHON', 20000);
Insert into EMP Values (2, '-', 10000);
Insert into EMP Values (3, 'PETER', 100);
Insert into EMP Values (4, 'JOSEPH', 500000);
Insert into EMP Values (5, 'CLARK', 8000000);
Insert into EMP Values(1, 'JHON-2', 20000);
CREATE OR REPLACE procedure proc_exception(p_eid number)
PRAGMA EXCEPTION_INIT (OTHER_EXP, -20001);
select ename, salary into l_name, l_sal from emp where eid = p_eid;
if l_sal>5000 and l_name = '-' then
RAISE_APPLICATION_ERROR(-20002,'Not a valid employee name'); -- here program will be breaked. this
if l_sal>5000 and l_name = 'JHON' then
elsif l_sal>5000 THEN
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('No data found!');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line ('Too many rows!');
WHEN SAL_TOO_HIGH THEN
dbms_output.put_line ('Salary Too High!');
WHEN OTHER_EXP THEN
WHEN OTHERS THEN
dbms_output.put_line ('Other Exception!');
Too many rows!
Salary Too High!
No data found!
Find useful discussions about RAISE, RAISE_APPLICATION_ERROR and EXCEPTION_INIT in this recent trending thread.