1 2 Previous Next 15 Replies Latest reply on Jan 22, 2019 2:45 PM by mathguy

    How to return an exception in a Oracle function

    Ranjan Swain

      Hi,

       

      I have a requirement like to calculate factorial of a number and I have to check if the input number is -ve or null then it should return an error telling the entered number is invalid

       

      Please share your thoughts on this.

       

      I am using Oracle 12c R2

        • 1. Re: How to return an exception in a Oracle function
          Cookiemonster76

          Check if it's -ve or null and then call raise_application_error to throw the exception

          • 2. Re: How to return an exception in a Oracle function
            Ranjan Swain

            Hi,

             

            Thanks for the reply.

             

            I can raise an exception and print the message but my requirement is to return the error, So wondering is there any way to achieve this.

            • 3. Re: How to return an exception in a Oracle function
              PV5253

              SQL> CREATE OR REPLACE FUNCTION fun_factorial(v_num IN NUMBER)

                2    RETURN NUMBER

                3  AS

                4    v_sign  NUMBER := 0 ;

                5    v_a     NUMBER := 1 ;

                6  BEGIN

                7    SELECT SIGN(v_num) INTO v_sign FROM dual ;

                8 

                9    IF v_sign=-1 OR v_sign IS NULL

              10    THEN

              11      raise_application_error(-20001,'Invalid input is attempt') ;

              12    ELSE

              13      FOR I IN REVERSE 1..v_num

              14      LOOP

              15        v_a := v_a * I ;

              16      END LOOP ; 

              17 

              18      RETURN (v_a) ;

              19    END IF ; 

              20  END ;

              21  /

               

              Function FUN_FACTORIAL compiled

               

              SQL> SELECT fun_factorial(5) factorial FROM dual ;

               

              FACTORIAL

              ----------

                     120

               

              SQL> SELECT fun_factorial(-5) factorial FROM dual ;

               

              Error starting at line : 28 in command -

              SELECT fun_factorial(-5) factorial FROM dual

              Error report -

              ORA-20001: Invalid input is attempt

              ORA-06512: at "CGASP.FUN_FACTORIAL", line 11

              1 person found this helpful
              • 4. Re: How to return an exception in a Oracle function
                Cookiemonster76

                Ranjan Swain wrote:

                 

                Hi,

                 

                Thanks for the reply.

                 

                I can raise an exception and print the message but my requirement is to return the error, So wondering is there any way to achieve this.

                You may need to define terms.

                As far as I'm concerned: raising exception = return error

                 

                So what do you see as the difference between the two?

                • 5. Re: How to return an exception in a Oracle function
                  BluShadow

                  Ranjan Swain wrote:

                   

                  Hi,

                   

                  I have a requirement like to calculate factorial of a number and I have to check if the input number is -ve or null then it should return an error telling the entered number is invalid

                   

                  Please share your thoughts on this.

                   

                  I am using Oracle 12c R2

                   

                   

                  You don't "return" the error.

                  The function is designed to return the result of the calculation.

                  Errors are captured and dealt with or "raised" up the call stack.

                   

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

                  1 person found this helpful
                  • 6. Re: How to return an exception in a Oracle function
                    Cookiemonster76

                    PV5253 wrote:

                     

                    SQL> CREATE OR REPLACE FUNCTION fun_factorial(v_num IN NUMBER)

                    2 RETURN NUMBER

                    3 AS

                    4 v_sign NUMBER := 0 ;

                    5 v_a NUMBER := 1 ;

                    6 BEGIN

                    7 SELECT SIGN(v_num) INTO v_sign FROM dual ;

                    8

                    9 IF v_sign=-1 OR v_sign IS NULL

                    10 THEN

                    .................

                    Why not just:

                    BEGIN

                    IF v_num < 0 OR v_num IS NULL THEN

                     

                    you don't need the sign call or the extra variable.

                    • 7. Re: How to return an exception in a Oracle function
                      cormaco

                      Or just raise VALUE_ERROR:

                       

                      IF n <= 0 OR n IS NULL THEN
                        RAISE VALUE_ERROR;
                      END IF;
                      
                      • 8. Re: How to return an exception in a Oracle function
                        mathguy

                        -ve is not a word; writing that instead of negative shows laziness or snobism, or both. Please keep that in mind when you post on a forum like this one.

                         

                        Regarding your question:

                         

                        The factorial function (the elementary one, anyway) is defined only for non-negative integers. So you need to throw an error when the input is 3.2, not only when the input is a negative number.

                         

                        One approach is to throw all "wrong argument" errors into a single bucket and print a generic error message, something like "invalid value". It is more useful, though, if you identify the different ways in which an input is invalid, and "return" different error messages for each. Sometimes you may need to make a choice; in this example, -3.2 is invalid both because it is negative and because it is not an integer; which error would you throw in this case? I would rather point out that it's a negative number (and it is marginally more efficient for the code, too: it is cheaper to test the sign bit and throw an error for negative inputs, rather than to test if the input is an integer).

                         

                        For the integer condition: you may require that the function argument be an integer, but Oracle will not honor that; rather, it will take your input and it will use it as is, instead of throwing an error if the input is not an integer. It is better to have the argument as NUMBER and test whether it is integer in your code (and throw an error if it isn't).

                         

                        I don't like part of your requirement... if the input is NULL, the function should not throw an error; it should simply return NULL. But it's your problem, your requirements; in the code below, I honored your request.

                         

                        One more thing - theoretically 100! makes perfect sense, but computers are finite systems; Oracle can't work with numbers longer than 38 digits or so, so at some point the function will return 'Infinity' instead of the right number. If you like, you can play with the function a little bit, find out where it starts outputting 'Infinity', and add a "Numeric Overflow" error check, similar to the ones in the code below.

                         

                        create or replace function factorial (n number)

                          return number

                        as

                          f number := 1;

                          c number := 2;

                        begin

                          if    n is null     then raise_application_error(-20001, 'Invalid input: argument is null');

                          elsif n < 0         then raise_application_error(-20003, 'Invalid input: argument is a negative number');

                          elsif n != trunc(n) then raise_application_error(-20002, 'Invalid input: argument is not an integer');

                          end if;

                          while c <= n loop

                            f := f * c;

                            c := c + 1;

                          end loop;

                          return f;

                        end;

                        /

                         

                        And one more thing (or two). It may be tempting to start from n and use a descending loop, but keep in mind that 0! = 1, not 0. So either you hard-code that, or use an ascending loop (as I did). It may also be tempting to use PLS_INTEGER and to use native compilation (resulting in faster execution); but the range of values for PLS_INTEGER is much smaller than for the Oracle data type NUMBER, so you will encounter an overflow condition much sooner. (Of course, if you only need the factorial function for low values of n, then you could add an overflow check and then use SIMPLE_INTEGER for even faster code.)

                        1 person found this helpful
                        • 9. Re: How to return an exception in a Oracle function
                          Ranjan Swain

                          Thanks all for all the insights !!

                          • 10. Re: How to return an exception in a Oracle function
                            John Thorton

                            Ranjan Swain wrote:

                             

                            Thanks all for all the insights !!

                            so mark this thread as ANSWERED

                            • 11. Re: How to return an exception in a Oracle function
                              Ranjan Swain

                              John this is not answered completely as there is no possibility to return the error in the event of any invalid input.

                               

                              yea we can use raise_application_error but if there is when others then exception block then that will be handled ..So my requirement was to return the error which is not possible now.

                               

                              The answer is just helpful

                              • 12. Re: How to return an exception in a Oracle function
                                BluShadow

                                Well, as the answer is that you cannot "return" an exception, then it IS answered.

                                Just because you're asking for something that isn't possible, doesn't mean the question should remain open, otherwise it will never be considered answered.

                                The fact of the matter is that the idea of "returning an exception" is flawed in itself, because exceptions are "raised".

                                Sure, you can capture the exceptions in the exception handler and return those through OUT parameters, or the return statement of a function, if you really wanted to do that, but generally that is considered poor exception handling as it breaks the correct exception handling mechanism inherently built in to PL/SQL.

                                 

                                As far as I can tell, this question IS answered.

                                • 13. Re: How to return an exception in a Oracle function
                                  Cookiemonster76

                                  Well this is the first time you've mentioned a when others, and it begs the question - Do you need to have the when others?

                                  • 14. Re: How to return an exception in a Oracle function
                                    Ranjan Swain

                                    Thanks it makes sense !

                                     

                                    I marked as answered.

                                    1 2 Previous Next