4 Replies Latest reply on Jul 10, 2015 12:52 PM by Yahoo!

    PRAGMA EXCEPTION_INIT vs RAISE_APPLICATION_ERROR

    2742751

      what is difference between PRAGMA EXCEPTION_INIT  and RAISE_APPLICATION_ERROR?when to use PRAGMA EXCEPTION_INIT and RAISE_APPLICATION_ERROR?

        • 1. Re: PRAGMA EXCEPTION_INIT vs RAISE_APPLICATION_ERROR
          PraneethG

          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

          • 3. Re: PRAGMA EXCEPTION_INIT vs RAISE_APPLICATION_ERROR
            2742751

            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.

            • 4. Re: PRAGMA EXCEPTION_INIT vs RAISE_APPLICATION_ERROR
              Yahoo!

              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

              (

                EID     NUMBER,

                ENAME   VARCHAR2(10 BYTE),

                SALARY  NUMBER(9,2)

              );

               

              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);

               

              COMMIT;

              CREATE OR REPLACE procedure proc_exception(p_eid number)

              is

              l_name varchar2(10);

              l_sal   number;

              SAL_TOO_HIGH EXCEPTION;

              OTHER_EXP EXCEPTION;

              PRAGMA EXCEPTION_INIT (OTHER_EXP, -20001);

              begin

                  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

                      RAISE OTHER_EXP;

                  elsif l_sal>5000 THEN

                      RAISE SAL_TOO_HIGH;

                  else

                      dbms_output.put_line (l_name);                  

                  end if;

              exception

                  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

                      dbms_output.put_line ('OTHER_EXP!');           

                  WHEN OTHERS THEN

                      dbms_output.put_line ('Other Exception!');                   

              end;

              /

              exec proc_exception(1);

              Too many rows!

               

              exec proc_exception(3);

              PETER

               

              exec proc_exception(4);

              Salary Too High!

               

              exec proc_exception(6);

              No data found!

               

              exec proc_exception(2);

              Other Exception!

               

              Find useful discussions about RAISE, RAISE_APPLICATION_ERROR and EXCEPTION_INIT in this recent trending thread.

               

              Re: RAISE and RAISE_APPLICATION_ERROR