5 Replies Latest reply: Jan 22, 2014 4:07 AM by Karthick_Arp RSS

Reg: Exception Handling

EV259 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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_Arp Guru
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points