3 Replies Latest reply on Mar 1, 2019 7:55 AM by AndrewSayer

    Exception ORA-

    happy10319

      Hi,

      The following gives ORA-01426: numeric overflow:

      DECLARE

      A PLS_INTEGER := 2500000;

      B PLS_INTEGER :=2147483647;

      BEGIN

       

      A:=A+1;

      B := B+1;

      dbms_output.put_line('A:' || A || 'B:' ||B);

       

      END;

      /

      How Can I have an exception that shows ORA-XXX as the out put (for any ora- error):

      EXCEPTION

        

         WHEN others THEN

            dbms_output.put_line('Error is ' || ORA-xxx);

       

      Is it possible?

      Thanks

        • 1. Re: Exception ORA-
          Mustafa KALAYCI

          Hi,

           

          are you looking for this:

           

          DECLARE
            A PLS_INTEGER := 2500000;
            B PLS_INTEGER :=2147483647;
          BEGIN
            A:=A+1;
            B := B+1;
            dbms_output.put_line('A:' || A || 'B:' ||B);
          EXCEPTION
            WHEN others THEN
                dbms_output.put_line('Error is ' || sqlerrm());
          END;
          /
          
          • 2. Re: Exception ORA-
            AndrewSayer

            user522961 wrote:

             

            Hi,

            The following gives ORA-01426: numeric overflow:

            DECLARE

            A PLS_INTEGER := 2500000;

            B PLS_INTEGER :=2147483647;

            BEGIN

             

            A:=A+1;

            B := B+1;

            dbms_output.put_line('A:' || A || 'B:' ||B);

             

            END;

            /

            How Can I have an exception that shows ORA-XXX as the out put (for any ora- error):

            EXCEPTION

             

            WHEN others THEN

            dbms_output.put_line('Error is ' || ORA-xxx);

             

            Is it possible?

            Thanks

            Why do you want to do that?

            What do you want to happen to the statement that was just executed and partially failed?

            • 3. Re: Exception ORA-
              BluShadow

              user522961 wrote:

               

              Hi,

              The following gives ORA-01426: numeric overflow:

              DECLARE

              A PLS_INTEGER := 2500000;

              B PLS_INTEGER :=2147483647;

              BEGIN

               

              A:=A+1;

              B := B+1;

              dbms_output.put_line('A:' || A || 'B:' ||B);

               

              END;

              /

              How Can I have an exception that shows ORA-XXX as the out put (for any ora- error):

              EXCEPTION

               

              WHEN others THEN

              dbms_output.put_line('Error is ' || ORA-xxx);

               

              Is it possible?

              Thanks

               

               

              So what you want is to implement exception handling for exceptions you have some method of handling... e.g.

               

              SQL> ed
              Wrote file afiedt.buf

                1  DECLARE
                2    numeric_overflow exception;
                3    pragma exception_init(numeric_overflow,-1426);
                4    A PLS_INTEGER := 2500000;
                5    B PLS_INTEGER := 2147483645; -- start a bit lower
                6  BEGIN
                7    loop
                8      begin
                9        A:=A+1;
              10        B:=B+1;
              11        dbms_output.put_line('A:'||A||' B:'||B);
              12      exception
              13        when numeric_overflow then
              14          dbms_output.put_line('A:'||A||' B:'||B||' - numeric overflow detected');
              15          raise;
              16      end;
              17    end loop;
              18* END;
              SQL> /
              A:2500001 B:2147483646
              A:2500002 B:2147483647
              A:2500003 B:2147483647 - numeric overflow detected
              DECLARE
              *
              ERROR at line 1:
              ORA-01426: numeric overflow
              ORA-06512: at line 15

               

              In this case it's not really handled, so I've re-raised the exception, which also makes it stop looping, but it at least shows the new value of A, even though B couldn't be incremented.

               

              Take a read of the community document: PL/SQL 101 : Exception Handling

               

              In general though, if you're not actually going to handle an exception effectively, then don't bother trying to capture it, unless you have proper exception logging in place.  Only put exception handlers in for exceptions you expect and can handle (do something with).  That is why it's generally considered a bug to use a WHEN OTHERS exception handler because it's trying to capture exceptions you're not expecting and you can't possibly know how to handle.