5 Replies Latest reply: Jan 22, 2014 6:07 AM by _Karthick_ RSS

    Reg: Exception Handling

    EV259

      Dear All,

       

      When I executed the below plsql block i got the error. Not sure,  'ORA-06510: PL/SQL: unhandled user-defined exception', why it is coming.

       

      When No data found, I am raising my own exception "userException". I am bit confused. Request all to explain.

       

      DECLARE
        userException EXCEPTION;
        v_name emp.ename%type;
        v_err VARCHAR2(200);
      BEGIN
        ----------------- statement
        BEGIN
          SELECT ename INTO v_name FROM emp WHERE empno = 1;
        EXCEPTION
        WHEN no_data_found THEN
          v_err := 'No data found for the employee';
          raise userException;
        WHEN OTHERS THEN
          v_err := 'Others Exception';
          raise userException;
        END;
      EXCEPTION
      WHEN userException THEN
        raise_application_error(-20901, v_err, TRUE);
      END;


      Error report:
      ORA-20901: No data found for the employee
      ORA-06512: at line 19
      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-01403: no data found

        • 1. Re: Reg: Exception Handling
          Hoek

          Less is more

           

          DECLARE

            v_name emp.ename%type;

            v_err VARCHAR2(200);

          BEGIN

              SELECT ename INTO v_name FROM emp WHERE empno = 1;

            EXCEPTION

            WHEN no_data_found THEN

              v_err := 'No data found for the employee';

              raise_application_error(-20901, v_err, TRUE);

          END;

          /

          • 2. Re: Reg: Exception Handling
            EV259

            Thanks Mr Hoek for your reply.

             

            But could you please explain me, why the error is coming when i use the 'When OTHERS then'.

             

            Why i am using this is, if the exception is not 'No_data_found' then by using Others...

             

            Thank you in advance.

            • 3. Re: Reg: Exception Handling
              BluShadow

              You're getting the "unhandled user-defined exception" because all your exception handlers are raising the exception and it hasn't been handled.  Ultimately it gets to your raise application error call, which has the TRUE parameter to show the full exception stack, so the raised userException is shown in the stack.

               

              It's exactly what I'd expect.

              • 4. Re: Reg: Exception Handling
                Hoek

                I've stopped using a WHEN OTHERS years ago. 99.99% they only make it harder to find out what's going wrong.

                I only catch errors that are to be expected and when something unexpected happens I'll let the unexpected error propagate up to the caller.

                I suggest you do some reading on exception handling on asktom, he explains it thoroughly in a clear way:

                Ask Tom "when other than exception, and raise ap..."

                Ask Tom "exception handling"

                and there's much more to find there...

                And this FAQ should give you clues as well:

                PL/SQL 101 : Exception Handling

                • 5. Re: Reg: Exception Handling
                  _Karthick_

                  The technical answer to your problem is specific with the third parameter of RAISE_APPLICATION_ERROR.

                   

                  Oracle document says

                   

                  "If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors."

                   

                  To understand this first lets comment your EXCEPTION block and run the code

                   

                  SQL> declare

                    2    userexception exception;

                    3    v_name emp.ename%type;

                    4    v_err varchar2(200);

                    5  begin

                    6    ----------------- statement

                    7    begin

                    8      select ename into v_name from emp where empno = 1;

                    9    exception

                  10    when no_data_found then

                  11      v_err := 'no data found for the employee';

                  12      raise userexception;

                  13    when others then

                  14      v_err := 'others exception';

                  15      raise userexception;

                  16    end;

                  17  /*exception

                  18  when userexception then

                  19    raise_application_error(-20901, v_err, true);*/

                  20  end;

                  21  /

                  declare

                  *

                  ERROR at line 1:

                  ORA-06510: PL/SQL: unhandled user-defined exception

                  ORA-06512: at line 12

                  ORA-01403: no data found

                   

                  So ORA-06510 is the actual error that is raised. So when you set the third parameter to TRUE you are instructing oracle not to overwrite the original error stack. Now let me remove the third parameter.

                   

                  SQL> declare

                    2    userexception exception;

                    3    v_name emp.ename%type;

                    4    v_err varchar2(200);

                    5  begin

                    6    ----------------- statement

                    7    begin

                    8      select ename into v_name from emp where empno = 1;

                    9    exception

                  10    when no_data_found then

                  11      v_err := 'no data found for the employee';

                  12      raise userexception;

                  13    when others then

                  14      v_err := 'others exception';

                  15      raise userexception;

                  16    end;

                  17  exception

                  18  when userexception then

                  19    raise_application_error(-20901, v_err);

                  20  end;

                  21  /

                  declare

                  *

                  ERROR at line 1:

                  ORA-20901: no data found for the employee

                  ORA-06512: at line 19

                   

                  See now oracle overwrites the error stack with just the user error message.