Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8
ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8 [message #397908] Tue, 14 April 2009 08:28 Go to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have a function which converts a 4 character / 7 digit code into a numerical code, which is in use for a few years now.

We just upgraded from 9.0.2.0.8 to 10.2.0.4.0 and exactly the same code doesn't work there. Here is the function is question, and the workaround I have found so far:

This DOES work on 9.0.2.0.8 :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7
  8    v_fakt_art_tmp VARCHAR2(4);
  9
 10    BEGIN
 11
 12    return  ascii(SubStr(v_char,1,1)) * 10000000000000   +
 13            ascii(SubStr(v_char,2,1)) * 100000000000     +
 14            ascii(SubStr(v_char,3,1)) * 1000000000       +
 15            ascii(SubStr(v_char,4,1)) * 10000000         +
 16            v_nr;
 17    END;
 18  /

Function created.
SQL> SELECT overflow_test('XXXX',1234567) FROM dual;

OVERFLOW_TEST('XXXX',1234567)
-----------------------------
                   8,8889E+14


Exactly the same thing DOESN'T work on 10.0.2.0.4 :

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7
  8    v_fakt_art_tmp VARCHAR2(4);
  9
 10    BEGIN
 11
 12    return  ascii(SubStr(v_char,1,1)) * 10000000000000   +
 13            ascii(SubStr(v_char,2,1)) * 100000000000     +
 14            ascii(SubStr(v_char,3,1)) * 1000000000       +
 15            ascii(SubStr(v_char,4,1)) * 10000000         +
 16            v_nr;
 17    END;
 18  /

Function created.

SQL>
SQL> SELECT overflow_test('XXXX',1234567) FROM dual;
SELECT overflow_test('XXXX',1234567) FROM dual
       *
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "PFK.OVERFLOW_TEST", line 13

SQL>


This workaround by selection from DUAL first does work, however :

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7
  8    v_fakt_art_tmp VARCHAR2(4);
  9    v_ret NUMBER(38,0);
 10
 11    BEGIN
 12
 13    v_fakt_art_tmp :=  SubStr(v_char || ' ',1,4);
 14
 15    SELECT  ascii(SubStr(v_char,1,1)) * 10000000000000   +
 16            ascii(SubStr(v_char,2,1)) * 100000000000     +
 17            ascii(SubStr(v_char,3,1)) * 1000000000       +
 18            ascii(SubStr(v_char,4,1)) * 10000000         +
 19            v_nr
 20      INTO v_ret
 21       FROM dual;
 22
 23    RETURN v_ret;
 24
 25    END;
 26  /

Function created.

SQL>
SQL> SELECT overflow_test('XXXX',1234567) FROM dual;

OVERFLOW_TEST('XXXX',1234567)
-----------------------------
                   8.8889E+14



Has anyone ever run into something similar?

If not, can someone with a running Version 11 DB check, if the thing without the workaround works under 11.X, or if the workaround is needed there, too?

[Updated on: Tue, 14 April 2009 08:35]

Report message to a moderator

Re: ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8 [message #397911 is a reply to message #397908] Tue, 14 April 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7    v_fakt_art_tmp VARCHAR2(4);
  8  BEGIN
  9    return  ascii(SubStr(v_char,1,1)) * 10000000000000.   +
 10            ascii(SubStr(v_char,2,1)) * 100000000000.     +
 11            ascii(SubStr(v_char,3,1)) * 1000000000.       +
 12            ascii(SubStr(v_char,4,1)) * 10000000.         +
 13            v_nr;
 14  END;
 15  /

Function created.

SQL> SELECT overflow_test('XXXX',1234567) FROM dual;
OVERFLOW_TEST('XXXX',1234567)
-----------------------------
                   8.8889E+14

1 row selected.

SQL> @v

Version Oracle : 10.2.0.4.0

I think as all numbers are integers PL/SQL optimizer internally uses native datatypes.
Converting to floating point makes the trick.

Regards
Michel

[Updated on: Tue, 14 April 2009 08:45]

Report message to a moderator

Re: ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8 [message #397913 is a reply to message #397908] Tue, 14 April 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the same thing in 11g:
SQB> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7    v_fakt_art_tmp VARCHAR2(4);
  8  BEGIN
  9    return  ascii(SubStr(v_char,1,1)) * 10000000000000   +
 10            ascii(SubStr(v_char,2,1)) * 100000000000     +
 11            ascii(SubStr(v_char,3,1)) * 1000000000       +
 12            ascii(SubStr(v_char,4,1)) * 10000000         +
 13            v_nr;
 14  END;
 15  /

Function created.

SQB> SELECT overflow_test('XXXX',1234567) FROM dual;
SELECT overflow_test('XXXX',1234567) FROM dual
       *
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at "MICHEL.OVERFLOW_TEST", line 9


SQB> @v

Version Oracle : 11.1.0.7.0

SQB> CREATE OR REPLACE FUNCTION overflow_test
  2  (v_char      IN  VARCHAR2,
  3   v_nr        IN  NUMBER
  4                          )
  5  RETURN NUMBER deterministic
  6  IS
  7    v_fakt_art_tmp VARCHAR2(4);
  8  BEGIN
  9    return  ascii(SubStr(v_char,1,1)) * 10000000000000.   +
 10            ascii(SubStr(v_char,2,1)) * 100000000000.     +
 11            ascii(SubStr(v_char,3,1)) * 1000000000.       +
 12            ascii(SubStr(v_char,4,1)) * 10000000.         +
 13            v_nr;
 14  END;
 15  /

Function created.

SQB> SELECT overflow_test('XXXX',1234567) FROM dual;
OVERFLOW_TEST('XXXX',1234567)
-----------------------------
                   8.8889E+14

1 row selected.

Regards
Michel
Re: ORA-01426: numeric overflow on 10.0.2.0.4 in code that works with 9.0.2.0.8 [message #397914 is a reply to message #397911] Tue, 14 April 2009 08:49 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, yes. That way works here, too.

Thanks

Thomas
Previous Topic: Index Monitoring Usage
Next Topic: SQL query
Goto Forum:
  


Current Time: Fri Dec 09 08:15:24 CST 2016

Total time taken to generate the page: 0.15614 seconds