Oracle SQL PLSQL large number field strange behavior

Question

Have existing table called temptable, column largenumber is a NUMBER field, with no precision set:

largenumber NUMBER;

Query:

select largenumber from temptable;

It returns:

-51524845525550100000000000000000000

But If I do

column largenumber format 999999999999999999999999999999999999999

And then

select largenumber from temptable;

It returns:

-51524845525550:100000000000000000000

Why is there a colon?

To test, I took the number, remove the colon, and insert it to another table temptable2, and did the same column largenumber format, the select returns the number without the colon:

select largenumber from temptable2;

It returns:

-51524845525550100000000000000000000

So the colon is not present here.

So what could possibly be in the original number field to cause that colon?

In the original row, If I do a select and try to do any TO_CHAR, REPLACE, CAST, or concatenate to text, it would give me number conversion error.

For example, trying to generate a csv:

select '"' || largenumber || '",'
FROM temptable;

would result in:

ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number

I can't reproduce it on my 11g, using SQL*Plus. There's no colon in output. — Littlefoot
– Littlefoot, Commented Dec 29, 2020 at 18:06
Try select dump(largenumber) from {your_first_table} and show us what it comes back with. The dump function works on all expressions (of any data type) and it shows exactly what is stored on disk, in raw form. That may give us some clues. — user5683823
– user5683823, Commented Dec 29, 2020 at 18:15
I picked another # as example. Value when doing simple select -514548495355000000000000000000000000 Dump(largenumber): Typ=2 Len=8: 45,50,56,53,52,48,46,48 setting column itembalance format 999999999999999999999999999999999999999: value returned: 51454849535553:100000000000000000000 — alwayslearning
– alwayslearning, Commented Dec 30, 2020 at 1:49
@Littlefoot . That is what I experienced. If I were to manually insert the value to another table, it would not have the same behavior. — alwayslearning
– alwayslearning, Commented Dec 31, 2020 at 1:14
dump(-514548495355000000000000000000000000) on Windows/Linux gives me Typ=2 Len=8: 45,50,56,53,52,48,46,102. What platform are you on? — Connor McDonald
– Connor McDonald, Commented Dec 31, 2020 at 1:31

score 1 · Accepted Answer · 2020-12-31 05:15:39Z

In a comment (in response to a question from me), you shared that dump(largenumber) on the offending value returns

Typ=2 Len=8: 45,50,56,53,52,48,46,48

From the outset, that means that the data stored on disk is invalid (it is not a valid representation of a value of number data type). Typ=2 is correct, that is for data type number. The length (8 bytes) is correct (we can all count to eight to see that).

What is wrong is the bytes themselves. And, we only need to inspect the first and the last byte to see that.

The first byte is 45. It encodes the sign and the exponent of your number. The first bit (1 or 0) represents the sign: 1 for positive, 0 for negative. 45 is less than 128, so the first bit in the first byte is 0; so the number is negative. (So far this matches what you know about the intended value.)

But, for negative numbers, the last byte is always the magic value 102. Always. In another comment under your original question, Connor McDonald asks about your platform - but this is platform-independent, it is how Oracle encodes numbers for permanent storage on any platform. So, we already know that the dump value you got tells us the value is invalid.

In fact, Connor, in the same comment, gave the correct representation of that number (according to Oracle's scheme for internal representation of numbers). Indeed, just the last byte is wrong: your dump shows 48, but it should be 102.

How can you fix this? If it's a one-off, just use an update statement to replace the value with the correct one and move on. If your table has a primary key, let's call it id, then find the id for this row, and then

update {your_table} set largenumber = -50...... where id = {that_id};

Question is, how many such corrupt values might you have in your table? If it's just one, you can shrug it off; but if it's many (or even "a handful") you may want to figure out how they got there in the first place.

In most cases, the database will reject invalid values; you can't simply insert 'abc' in a number column, for example. But there are ways to get bad data in; even intentionally, and in a repeatable way. So, you would have to investigate how the bad values were inserted (what process was used for insertion).

For a trivial way to insert bad data in a number column, in a repeatable manner, you can see this thread on the Oracle developers forum: https://community.oracle.com/tech/developers/discussion/3903746/detecting-invalid-values-in-the-db

Please be advised that I had just started learning Oracle at that time (I was less than two months in), so I may have said some stupid things in that thread; but the method to insert bad data is described there in full detail, and it was tested. That shows just one possible (and plausible!) way to insert invalid stuff in a table; how it happened in your specific case, you will have to investigate yourself.

Collectives™ on Stack Overflow

Oracle SQL PLSQL large number field strange behavior

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related