ITcareershift.com Blog From Sridhar Kadiyala : how to troubleshoot float errors in oracle

Doc ID 210869.1

Explanation

===========

The ‘~’ is the notation for positive infinity, which is usually the

result of an overflow. An Oracle number text conversion routine

outputs “~” or “-~” when the input number is positive or negative

infinity, respectively, or when rounding (due to buffer size limit)

causes overflow to +/- infinity.

Oracle stores numeric data in variable-length format. Each value is stored in

scientific notation, with one byte used to store the exponent and up to 20

bytes to store the mantissa. The resulting value is limited to 38 digits of

precision.

Zero and positive and negative infinity are stored using unique representations.

Zero and negative infinity each require one byte; positive infinity requires

two bytes.

If a positive value is extremely large and a numeric overflows occurs when

rounding a number, then the infinity sign (~) replaces the value.

Likewise, if a negative value is extremely small and a numeric overflow

occurs when rounding a number, then the negative infinity sign replaces

the value (-~).

Solution======== Concept of infinity is unsupported/undocumented in later Oracle versions.Oracle version 5 was the last release with documented support for positive and negative infinity. As mentioned above oracle can save numbers up to 38 digitsof precision, customers are advised not break this limit. Customers should take oracle’s 38 digit number precision into consideration whiledesigning their application and avoid numbers greater than this. If applicationsare designed to store numbers greater than 38 digits, oracle does not guaranteethe results. Example: Avoid using datatypes like “double.positive_infinity” in JAVA environment.

Incorrectly Stored Number Values or Data in a Database (Doc ID 311346.1)

Numbers in a FLOAT or DOUBLE Type Lose Precision (Doc ID 1429713.1)

An example of losing precision with too many digits:

50000001

In binary, that is:

(10111110101111000010000001)b

In “scientific” notation, that is:

(1.0111110101111000010000001)b * 2^25

The significand has room for only 23 bits. So the value stored is:

(1.01111101011110000100000)b * 2^25

The last two bits of the significand were lost.

When converting back to decimal, the missing bits are filled in with 0s:

(10111110101111000010000000)b

The result in decimal:

50000000

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5011470313887

and we said…

Floats are numbers.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( id number, code float );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t

2 values(

123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890

123456789012345678901234567890123456,

123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890

123456789012345678901234567890123456)

5 /

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set linesize 50

ops$tkyte@ORA817DEV.US.ORACLE.COM> set numwidth 50

ops$tkyte@ORA817DEV.US.ORACLE.COM> select id, code, id-code from t;

————————————————–

CODE

————————————————–

ID-CODE

————————————————–

1.2345678901234567890123456789012345678900000E+125

1.2345678901234567890123456789012345679000000E+125

-1.0000000000000000000000000000000000000000000E+87

Doesn’t show that floats aren’t numbers, they are just a number with a different

precision then number in itself.

(it also shows that numbers in this particular case are capable of storing 39 digits —

this may vary a bit, 38 is all we assure you and that is what the float is actually

storing. The float is less precise in this case and in your case, not the other way

around).

In anycase, give me a REAL WORLD example where this difference makes a difference – for

example, are you storing numbers with 39 digits of precision (if you are BEWARE, 38 is

what we promise, not 39). If you are storing with 38, we can do.

In SQL, the benefits of native operations don’t really show up unless you are using very specific functions – like LN(). for example select sum(float) vs select sum(number) will not be very different (but select sum(ln(float)) will outperform select sum(ln(number)) greatly)

In PLSQL, if you are doing lots of computations – you’ll find that floats/doubles outperform numbers – sometimes by a lot. But remember – lack of precision, inability to store certain numbers altogether make them useless for financial applications and the like.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1416003959639

Tom,

In working with my developers(Java), I have come across the topic of datatype conversion.

They have given me a script which creates tables using INTEGER and FLOAT as datatypes for

some of the columns. I questioned why these dayatypes were being used instead of NUMBER.

I was made aware of the fact that Oracle has its own proprietory datatypes. If the

application we are building wants to insert into or select from a column values which are

externally defined as FLOAT or INTEGER, should I create this column with the external or

internal datatype? And if I use an internal datatype, where does the conversion occur? I

have read through the Oracle documentation but still don’t quite understand. They are

also questioning how they/I can use external datatypes BIGINT, DOUBLE, and TIMESTAMP. As

always your words of wisdom are greatly appreciated.

Keith M Cutler

and we said…

All numbers in Oracle are stored as numbers. Float, integer, etc — they are all

synonyms for numbers.

tkyte@TKYTE816> create table t

2 ( a int, b float(5), c decimal(5,2),

d number(5), e number, f float );

Table created.

tkyte@TKYTE816> desc t

Name Null? Type

———————– ——– —————-

A NUMBER(38)

B NUMBER(5)

C NUMBER(5,2)

D NUMBER(5)

E NUMBER

F FLOAT(126)

We sometimes fake a float for you (but not ALWAYS — see above, b float(5)) in the data

dictionary views — if you look at the text of USER_TAB_COLUMNS, you would find:

decode(c.scale,

null, decode(c.precision#, null, ‘NUMBER’, ‘FLOAT’),

‘NUMBER’),

so, floats are fake, integers are fake — they are ALL numbers.

You, as a creator of tables, never need to worry about external datatypes — they are

only of concern to the developers.

They, as developers using JDBC, need not concern themselves either — they use JDBC and

it is all “hidden” underneath. They just use the type they want. Just make sure to use

a type that has enough precision to retain the number (eg: Oracle supports a number with

38 digits of precision, Java won’t hold a number with that much precision in an Int or

Double type — neither will C or Ada or any 3gl pretty much).

what external loader tool? and have you asked them to correct their erroneous behaviour?

no, cast in a view won’t work.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055

Hi Tom,

I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38)

to be specific.

But What I found to be strange that, it is accepting digits beyong 38 digits.ie.

1.111E+125. If the Datatype’s precision is 38 and scale can range from -84 to 127 how

this value can insert into the column.

In one of the System owned tables, USER_TAB_PRIVS there is a column known as Table_Name

but under this column you will find all the objects of a schema which has been granted

object privs. whether it is table,view or any sequence.

Shouldn’t it be more precise to define this column name as Object_name rather that the

table_name.

Please do explain & clarify.

Thanks in Anticipation,

Vikas Khanna

Vkhanna@quark.stpm.soft.net

and we said…

1.111e+125 only has 4 digits of precision (number of digits needed to represent the

number — in this case 1111).

The 38 is for 38 digits of precision. The SCALE dictates how many places to the right or

left of the decimal place you may have.

It would be more clear to have USER_TAB_PRIVS say that — however, it would break lots of

stuff. User_tab_privs pre-dates many of these object types and retains its naming for

backwards compatibility reasons.

I entered a number 12345678901234567890123456789012345678901234567890123456789012345678901234567890 into that particular column. It showed me the result as 12345678901234567890123456789012345678900000000000000000000000000000000000000000, when set numwidth 2000 was issued.(SQL Worksheet accepts it) When issued set numwidth 80 it displayed ,1.2345678901234567890123456789012345678900000000000000000000000000000000000000000E+79 Why it is so??Moreover I was not able to understand the concept behind this.Can u please explain in more detail how Oracle does store the Numbers internally. Take relevant examples for precision & scale.

Followup October 16, 2001 – 8am UTC:

Well first, I hope you have read the documentation (always a great place to start). see http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c10datyp.htm#743there is a nice table there with various precisions/scales that demonstrate the outcome. Numbers are stored with an assured (at least) 38 digits of precision. It is allowed to go higher (you got 39 digits of precision). Numbers that exceed the PRECISION when the precision is not specified are rounded to fit in there (just like if you stuff a 15 digit number into a C float/double — it’ll be made to “fit” since they hold numbers with 6/13 digits of precision only) The number is like number(*) (wildcard) ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x number(*) );Table created. ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 12345678901234567890123456789012345678901234567890 ); 1 row created. ops$tkyte@ORA717DEV.US.ORACLE.COM> select to_char(x,rpad(‘9′,60,’9’)) from t; TO_CHAR(X,RPAD(‘9′,60,’9’))————————————————————- 12345678901234567890123456789012345678900000000000 With a numwidth of 2000 — there was enough room to display the number in its entirety without using scientific notation — so it did. With a numwidth of 80, there was NOT enough room — so it used scientific notation to display the number in your requested field size. That part seems all very logical (to me anyway, it was just doing exactly what you asked it to do).

Number or number(p)

What the difference between declaring a column using datatype

number

and

number(n)?

Ay advantage or disadvantge declaring one way and the another?

Followup June 30, 2003 – 7am UTC:

number(n) is a number with a constraint and edit.

ops$tkyte@ORA920> create table t ( x number, y number(5) );

Table created.

ops$tkyte@ORA920>

ops$tkyte@ORA920> insert into t values ( 123.456, 123.456 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 123.999, 123.999 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 12345, 12345 );

1 row created.

ops$tkyte@ORA920> insert into t values ( 123456, 123456 );

insert into t values ( 123456, 123456 )

ERROR at line 1:

ORA-01438: value larger than specified precision allows for this column

ops$tkyte@ORA920> select * from t;

X Y

———- ———-

123.456 123

123.999 124

12345 12345

See how the number(5) behaves? 5 digits, no decimals, rounded.

The advantage is

a) you have 5 digits only, never more

b) you have no decimals, it is an integer type

consider it a constraint — like a primary key, NOT NULL, check, whatever.

If your data is such that the number should never be more then 5 digits, the only correct way to

implement it would be as a number(5)

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055

Followup December 1, 2009 – 2am UTC:

You don’t say what client you are using, but it looks like you are using some application that is using floating point numbers in the client to fetch into.

floating point numbers have 6 or 13 digits of precision (we have 38) and are incapable of holding many numbers without losing precision (eg: they cannot store most numbers correctly).

ops$tkyte%ORA10GR2> create table t (a number(20,10),b number(20,11),c number(20,12),d

number(20,13),e number(20,14),f number(20,15));

Table created.

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2> insert into t values (20991.06,20991.06,20991.06,20991.06,20991.06,20991.06);

1 row created.

ops$tkyte%ORA10GR2> insert into t values (400.85,400.85,400.85,400.85,400.85,400.85);

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2> set numformat 99999.0000000000000009

ops$tkyte%ORA10GR2> select * from t;

A B C

———————– ———————– ———————–

D E F

———————– ———————– ———————–

20991.0600000000000000 20991.0600000000000000 20991.0600000000000000

400.8500000000000000 400.8500000000000000 400.8500000000000000

ops$tkyte%ORA10GR2>

ops$tkyte%ORA10GR2> select cast( f as binary_double ) from t;

CAST(FASBINARY_DOUBLE)

———————–

20991.0600000000010000

400.8500000000000200

ops$tkyte%ORA10GR2> select cast( f as binary_float) from t;

CAST(FASBINARY_FLOAT)

———————–

20991.0605000000000000

400.8500060000000000

So, I think the problem is not in the database (use sqlplus and you’ll see) but in the client that fetches and displays this data – that client should not be used to modify any numeric data – you’ll end up messing it up.

I have a simple doubt regarding number declaration. Number is declared as Number(p,s). The maximum

value precision can take is 38 and the least value of scale is -84. But declaring any number with

scale less than equal to -38, For eg Number(38,-39) would result in zeroes being stored in the

table. So could you please explain in which cases are the scale from -38 to -84 useful in number

declaration.

Followup January 14, 2013 – 11am UTC:

ops$tkyte%ORA11GR2> create table t ( x number(38,-39) );

Table created.

ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> insert into t values (

12345678901234567890123456789012345678000000000000000000000000000000000000000 );

1 row created.

ops$tkyte%ORA11GR2>

ops$tkyte%ORA11GR2> select * from t;

———-

1.2346E+76

it would not just store zeros. You are saying “I want 38 digits of precision – for a really really really large number, because I said there will be 39 zeros before we start seeing digits whose value we care about”

Why 126?

Followup May 16, 2013 – 8am UTC:

because that is as big as we can go

ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘8’, 126, ‘9’ ) );

1 row created.

ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘8’, 127, ‘9’ ) );

insert into t values ( rpad( ‘8’, 127, ‘9’ ) )

ERROR at line 1:

ORA-01426: numeric overflow

but we’ve lost the precision by then:

ops$tkyte%ORA11GR2> create table t ( x number(*,0) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘9’, 45, ‘9’ ) );

1 row created.

ops$tkyte%ORA11GR2> select to_char( x ) from t;

TO_CHAR(X)

—————————————-

1.0000000000000000000000000000000000E+45

ops$tkyte%ORA11GR2> set numformat 9999999999999999999999999999999999999999999999999

ops$tkyte%ORA11GR2> select x from t;

————————————————–

1000000000000000000000000000000000000000000000

Rating: 0.0/10 (0 votes cast)

Rating: 0 (from 0 votes)

ITcareershift.com Blog From Sridhar Kadiyala

how to troubleshoot float errors in oracle

Post a Comment

Topics

Home

Latest News

Bookmarks

Help

Follow

ITcareershift.com Blog From Sridhar Kadiyala

how to troubleshoot float errors in oracle

Post a Comment

Topics

Home

Latest News

Bookmarks

Help

Follow

Tags