This discussion is archived
4 Replies Latest reply: Jan 9, 2013 1:58 AM by BluShadow RSS

Exception

887479 Newbie
Currently Being Moderated
I had gone through the FAQ - Exception handling..

Still, I am not very clear how to handle the below scenario
declare
  ln_dummy pls_integer;
begin
  ln_dummy := 10;
exception
  when ln_dummy NOT between 12 and 43 then
   raise 'Invalid value';
end;
How can we do this?
  • 1. Re: Exception
    Karthick_Arp Guru
    Currently Being Moderated
    884476 wrote:
    I had gone through the FAQ - Exception handling..

    Still, I am not very clear how to handle the below scenario
    declare
    ln_dummy pls_integer;
    begin
    ln_dummy := 10;
    exception
    when ln_dummy NOT between 12 and 43 then
    raise 'Invalid value';
    end;
    How can we do this?
    You need to define a exception and check for the value of ln_dummy in you main block and then raise the declared exception if the value does not satisfy the condition.

    Something like this.
    declare
      ln_dummy pls_integer;
      ln_dummy_invalid exception;
    begin
      ln_dummy := 10;
    
      if ln_dummy not between 12 and 43 then
        raise ln_dummy_invalid;
      end if;
    exception
      when ln_dummy_invalid then
        raise_application_error(-20001, 'Invalid Value');
    end;
  • 2. Re: Exception
    jeneesh Guru
    Currently Being Moderated
    Or use RANGE ..You will directly get VALUE_ERROR exception..
    declare
      ln_dummy pls_integer range 12..43;
    begin
      ln_dummy := 10;
    /*exception
      when value_error then
        raise_application_error(-20001, 'Invalid Value');*/
    end;
    Edited by: jeneesh on Jan 9, 2013 11:15 AM
  • 3. Re: Exception
    SomeoneElse Guru
    Currently Being Moderated
    You can constrain the integer using range:
    SQL> declare
      2    ln_dummy pls_integer range 12..43;
      3  begin
      4    ln_dummy := 10;
      5  end;
      6  /
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 4
    Or you can raise an explicit exception:
    SQL> declare
      2    ln_dummy pls_integer;
      3  begin
      4    ln_dummy := 10;
      5    if ln_dummy NOT between 12 and 43 then
      6       raise_application_error(-20043,'Invalid value for dummy, dummy');
      7    end if;
      8  end;
      9  /
    declare
    *
    ERROR at line 1:
    ORA-20043: Invalid value for dummy, dummy
    ORA-06512: at line 6
  • 4. Re: Exception
    BluShadow Guru Moderator
    Currently Being Moderated
    Or in the software engineering style of Billy Verreyne...
    SQL> create or replace procedure assert(p_assertion in boolean, p_err in varchar2) is
      2  begin
      3    if not p_assertion then
      4      -- log error here if required
      5      raise_application_error(-20001, 'Assertion failed: '||p_err, true);
      6    end if;
      7  end;
      8  /
    
    Procedure created.
    
    SQL> declare
      2    ln_dummy pls_integer;
      3  begin
      4    ln_dummy := 10;
      5    assert(ln_dummy between 12 and 43, 'Dummy variable not between 12 and 43');
      6  end;
      7  /
    declare
    *
    ERROR at line 1:
    ORA-20001: Assertion failed: Dummy variable not between 12 and 43
    ORA-06512: at "SCOTT.ASSERT", line 5
    ORA-06512: at line 5

Legend

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