I'm seeing some odd behavior in an Oracle 11gR2 database. When I select from a particular table, using SQL Developer, certain rows of the result set include non-numeric results in a NUMBER
column (with a NOT NULL
constraint, though I'm not sure that matters). For example:
oracle.sql.NUMBER@b25bc2f
oracle.sql.NUMBER@5a65b760
oracle.sql.NUMBER@4bea2dfd
and so on, mixed in with regular numeric values in the same column. When using SQL*Plus I just see blank spaces instead (as with NULL
), e.g (in the column aliased COL
):
FYEAR CO AB DEV COL COUNT(*)
---------- ---------- --- ------ ---------- ----------
2012 7 SF 107 1
I don't know much about Oracle internals but the strings in SQL Developer look like pointers to me. We stumbled across these not as the result of any error but because a query involving some arithmetic was giving very unexpected results. So, thinking maybe I could dereference them and learn something, I did a bit of experimenting:
cast(col as numeric)
always gives 0to_number(col)
throwsORA-01722: invalid number
col * 1
gives any of an integer, decimal value,null
or a differentoracle.sql.NUMBER@...
—the results are consistent but only within a transactioncol * 1 * 1
gives a different result thancol * 1
, with no pattern that I can discern (and so forcol * 1 * 1 * 1
, etc.; butcol * 2
is consistent withcol * 1
!)- when I start a new transaction, in SQL*Plus all the calculated expression results change; in SQL Developer, the the hex values shown change between transactions but the calculated expression results do not! Wat.
This is, hands down, the weirdest thing I've ever seen in a database. Is this a sign of corruption in the underlying datafiles? Have I stumbled upon some quirky undefined behavior in Oracle?
Here's an example of the result set changing between transactions—there's only one person with DML rights on this table, I verified he's not working with it, and it has no triggers:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> select col, cast(col as numeric), col * 1, col * 2, col * 1 * 1, col * 1 * 1 * 1
2 from schema.table@dblink where foo = bar;
COL CAST(COLASNUMERIC) COL*1 COL*2 COL*1*1 COL*1*1*1
---------- ------------------ ---------- ---------- ---------- ----------
0
0 848 1696 9648 1612
0 8304 9312 1612
0 1612
SQL> rollback;
Rollback complete.
SQL> select col, cast(col as numeric), col * 1, col * 2, col * 1 * 1, col * 1 * 1 * 1
2 from schema.table@dblink where foo = bar;
COL CAST(COLASNUMERIC) COL*1 COL*2 COL*1*1 COL*1*1*1
---------- ------------------ ---------- ---------- ---------- ----------
0
0 8048 6096 3648 140
0 8304 9312 140
0 140
SQL>
In SQL Developer, some of these blanks are NULL
while others show the oracle.sql.NUMBER@...
thing, which SQL Developer does not treat like a string, even on export:
REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into EXPORT_TABLE (COL,"CAST(COLASNUMERIC)","COL*1","COL*2","COL*1*1","COL*1*1*1") values (oracle.sql.NUMBER@3924c5dc,0,null,null,null,42.939572379395723793957237939572379396);
DUMP()
shows it as a 1-byte numeric; I'm not sure what 193 means as an internal representation. Everything I'm finding so far on Google shows 193 appearing in association with other numbers (e.g., 193,1
) but never by itself; according to this page, 193 by itself seems nonsensical? Or I'd say it signifies null, except that DUMP()
shows nulls as NULL
. In any case, the 193 appears consistently for all of the affected rows:
FYEAR COL DUMP(COL) COL*1
2012 oracle.sql.NUMBER@1ef46be8 Typ=2 Len=1: 193 (null)
2013 oracle.sql.NUMBER@40d85c38 Typ=2 Len=1: 193 112
2014 oracle.sql.NUMBER@3c8e81ca Typ=2 Len=1: 193 112
2015 oracle.sql.NUMBER@2feb13d5 Typ=2 Len=1: 193 2712
2016 oracle.sql.NUMBER@72631d86 Typ=2 Len=1: 193 2712
...
This issue persists if I CREATE TABLE tbl AS...
with a query on the affected table. I traced it back to a table that is populated via a legacy program written in C, that no-one on staff knows how to decipher. But my assumption is that the C program can't insert anything that violates the NOT NULL NUMBER
column definition without Oracle kicking it back out with an error (bad assumption?).
Best Answer
It is possible to get completely "invalid" numbers into the database, even using PL/SQL. Your guess about 193 is correct, DUMP(X, 1010)
My guess is the insertion/update code is using a native programming language type e.g. integer, where as it should be using an Oracle NUMBER type. You can't safely assume an Oracle NUMBER is directly equivalent to C# integer. Yes I've seen that odd behaviour before SUM/MAX and other numeric functions were not working properly.
Can you identify the code where those 'bad' numbers came from? The data those bad numbers represented can never be reconstructed, the best you can hope for is to prevent it from happening again.