How to get ride of ORA-01426: numeric overflow error?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to get ride of ORA-01426: numeric overflow error?

  1. #1
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Question How to get ride of ORA-01426: numeric overflow error?

    Hi,
    I am tring to create this function for factorial calculation.

    CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER IS
    BEGIN
    IF n = 0 THEN
    RETURN 1;
    ELSIF n = 1 THEN
    RETURN 1;
    ELSE
    RETURN n * fac(n - 1);
    END IF;
    END fac;


    When i am executing this function as

    select FAC(34) from dual
    *
    ERROR at line 1:
    ORA-01426: numeric overflow

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: number precision too large

    Can someone help me about the problem with this function.

    Thanks
    Regards,
    Kumar
    RP Kumar
    You Can Win, if u believe Yourself

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    You've probably gone beyond the limit Oracle can cope with for a number variable. I think it can only go up to number(39).

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135

    CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN INTEGER

    Change the NUMBER to FLOAT.

    CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN FLOAT

    SQL> select fac(34) from dual ;

    FAC(34)
    ----------
    2.9523E+38


    Tamil

  4. #4
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78
    Thanks for your reply Tamil..
    But i have one more question for you .

    select FAC(83) from dual;
    FAC(83)
    ----------
    3.946E+124

    select FAC(84) from dual;
    FAC(84)
    ---------
    ~

    Is there any other work around to resolve this.. I need to calculate till FAC(100) .

    Thanks in Advance
    Regards,
    Kumar
    RP Kumar
    You Can Win, if u believe Yourself

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by Kumar_RP
    Thanks for your reply Tamil..
    But i have one more question for you .

    select FAC(83) from dual;
    FAC(83)
    ----------
    3.946E+124

    select FAC(84) from dual;
    FAC(84)
    ---------
    ~

    Is there any other work around to resolve this.. I need to calculate till FAC(100) .

    Thanks in Advance
    Regards,
    Kumar
    You need to use IBM Mainframe and FORTRAN to calculate FAC(100).

    Tamil

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by tamilselvan
    You need to use IBM Mainframe and FORTRAN to calculate FAC(100).

    Tamil
    For example.

    Code:
    CREATE OR REPLACE FUNCTION fac ( n NUMBER )
       RETURN NUMBER
    IS
    BEGIN
       IF n = 0
       THEN
          RETURN 1;
       ELSIF n = 1
       THEN
          RETURN 1;
       ELSE
          RETURN n * fac ( n - 1 );
       END IF;
    END fac;
    /
    
    SELECT TO_CHAR(fac( 9), '999,999,999') factor_9,
           TO_CHAR(fac(10), '999,999,999') factor_10,
           TO_CHAR(fac(10) - fac(9), 
    		                  '999,999,999') factor_diff,
           TO_CHAR(fac(30), '999,999,999,999,999,999,999,999,999,999,999') factor_30
      FROM dual;
    
    FACTOR_9     FACTOR_10    FACTOR_DIFF  FACTOR_30
    ------------ ------------ ------------ --------------------------------------------
         362,880    3,628,800    3,265,920  265,252,859,812,191,058,636,308,480,000,000
    You can see that the result grows exponentially. Is this a homework assignment???
    this space intentionally left blank

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    cheating only slightly ...
    Code:
    CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN varchar2 IS
    BEGIN
      IF n = 0 THEN  
         RETURN 1;
      ELSIF n = 1 THEN
         RETURN 1;
      ELSIF n = 100 THEN
         RETURN '93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000';
      ELSE
         RETURN n * fac(n - 1);
      END IF;
    END fac;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by slimdave
    cheating only slightly ...
    Code:
    CREATE OR REPLACE FUNCTION fac (n POSITIVE) RETURN varchar2 IS
    BEGIN
      IF n = 0 THEN  
         RETURN 1;
      ELSIF n = 1 THEN
         RETURN 1;
      ELSIF n = 100 THEN
         RETURN '93326215443944152681699238856266700490715968264381621468592963895217599993229915608941463976156518286253697920827223758251185210916864000000000000000000000000';
      ELSE
         RETURN n * fac(n - 1);
      END IF;
    END fac;
    Perhaps you can create a case function to deal with all of the numbers between 30 and 100.

    Code:
    CCDOTTSVR@vizdev> select fac(99) from dual;
    select fac(99) from dual
           *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    ORA-06512: at "CCDOTTSVR.FAC", line 10
    this space intentionally left blank

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Tsh. Fussy fussy. Just calculate it somewhere else and use a lookup table. Be faster
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Question

    Hi Folks,
    Thanks for your responses about my doubt..

    Slimdave... can you please give some more clear idea about whats the need of putting N = 100 as default value.?..

    Tamil.. Can you please throw some lights on your answer " Use IBM or FORTRAN "??

    Thanks in advance.

    Happy Thanks Giving!!!

    Regards,
    Kumar
    RP Kumar
    You Can Win, if u believe Yourself

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width