4 Replies Latest reply: Jan 9, 2013 3:58 AM by BluShadow RSS

    Exception

    887479
      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
          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
            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
              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
                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