12057

numeric overflow with “NULL” column (0,3)

Question:

I have a table (Oracle 12.1.0.2.0) where when I sum up the values I get

ORA-01426: numeric overflow 01426. 00000 - "numeric overflow" *Cause: Evaluation of an value expression causes an overflow/underflow. *Action: Reduce the operands.

When I check the values in SQL-Developer I see in the column "NULL" which should be no issue for the function sum.

When I use dump(col1) for this table the result is

Typ=2 Len=2: 0,3

shouldn't this be NULL for columns with NULL?

Also checks 'is not null' didn't filter rows.

Exporting the data only exported a part of the first row and stopped after the first occurence of this 0,3.

SQL Navigator showed "error" as value for this column.

Table was created as select. Now it is truncated and refilled and NULL rows are NULL and summing up works.

What was the content of that column? And why SQL-Developer shows NULL instead of an error?

Answer1:

The column value wasn't null, it was corrupt. The internal representation used for numbers is <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnoci/data-types.html#GUID-91151345-2C67-41BC-A782-AD4816B89BCF" rel="nofollow">described in the documentation</a>, or various other places <a href="http://www.jlcomp.demon.co.uk/number_format.html" rel="nofollow">like this</a>.

The first byte is the exponent, and it can - just - be zero, but not with only a 3 following it. The closest I think you can get is 0,3,102 for -9.8*x10^125

<a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78" rel="nofollow">From the docs</a>:

<blockquote>

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10^-130 to but not including 1.0 x 10^126.

</blockquote>

So lets look at how some at the extreme ends are stored:

with t (n) as ( select 1 * power(10, -130) from dual union all select 1 * power(10, 125) from dual union all select -1 * power(10, -130) from dual union all select -1 * power(10, 125) from dual union all select -9.7 * power(10, 125) from dual union all select -9.8 * power(10, 125) from dual union all select -9.85 * power(10, 125) from dual union all select -9.9 * power(10, 125) from dual ) select n, dump(n) d1, dump(n, 1016) d2 from t N D1 D2 ----------- ------------------------------ ------------------------------ 1.000E-130 Typ=2 Len=2: 128,2 Typ=2 Len=2: 80,2 1.000E+125 Typ=2 Len=2: 255,11 Typ=2 Len=2: ff,b -1.000E-130 Typ=2 Len=3: 127,100,102 Typ=2 Len=3: 7f,64,66 -1.000E+125 Typ=2 Len=3: 0,91,102 Typ=2 Len=3: 0,5b,66 -9.700E+125 Typ=2 Len=3: 0,4,102 Typ=2 Len=3: 0,4,66 -9.800E+125 Typ=2 Len=3: 0,3,102 Typ=2 Len=3: 0,3,66 -9.850E+125 Typ=2 Len=4: 0,3,51,102 Typ=2 Len=4: 0,3,33,66 -9.900E+125 Typ=2 Len=3: 0,2,102 Typ=2 Len=3: 0,2,66 <blockquote>

If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error.

</blockquote> select 1 * power(10, 126) from dual; ORA-01426: numeric overflow

Your dumped value of 0,3 doesn't have the 102 at the end denoting a negative number, but if it was positive the first byte would be at least 128.

There have been instances of numbers being corrupted by OCI programs mishandling them, and even legacy import doing the same. Without knowing how the data was originally created you'll probably never know exactly what went wrong, or when, or what the value was originally supposed to be.

<hr />

It's odd that SQL Developer shows null in the results grid (it seems abort if you query as a script); in SQL*Plus it shows no value even if you set null to a fixed string. SQL Developer, or the JDBC driver, may just be silently swallowing the inability to convert from internal representation.

Recommend

  • How to navigate between different html pages in Windows 8 Metro application using javascript?
  • Is it really posible to close a PhoneGap App?
  • Parsing Java dates with truncated timezone information
  • How do I customize header and footer in jqgrid?
  • Casting double to integer when is it undefined behaviour
  • javascript Confirm replacement with return true/false
  • Different builds of turn-based Game Center game can’t see each other’s matches
  • get latest text entry of GROUP BY statement
  • Android application not restoring state when installed from .apk, works fine from eclipse
  • Tools for understanding HTML layout
  • Undefined navigator.push React-native 0.43.4
  • Updating product post meta data in admin meta box field
  • Problems with toDataURL HTML5 other ways to get canvas data?
  • How to log in to an HTTPS website with Jsoup?
  • Google Places API - Find a company's CID and LRD
  • Facebook iOS SDK Not Calling Completion Handler
  • Hibernate to update table schema
  • Image map in Flex
  • JPA flush vs commit
  • ASP.NET MVC Application won't update some controllers
  • What's the purpose of QString?
  • How can I speed up CURL tasks?
  • Refering to the class itself from within a class mehod in Objective C
  • Nant, Vault & Windows Integrated Authentication
  • What is Eclipse's Declaration View used for?
  • Jquery - Jquery Wysiwyg return html as a string
  • Delete MySQLi record without showing the id in the URL
  • SVN: Merging two branches together
  • Unanticipated behavior
  • Comma separated Values
  • Free memory of cv::Mat loaded using FileStorage API
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Trying to get generic when generic is not available
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How does Linux kernel interrupt the application?
  • Linking SubReports Without LinkChild/LinkMaster
  • unknown Exception android
  • Append folder name and increment by 1 using batch script
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass