Why would a query return oracle.sql.NUMBER@[hex value] in place of a numeric value?

Question

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 0
• to_number(col) throws ORA-01722: invalid number
• col * 1 gives any of an integer, decimal value, null or a different oracle.sql.NUMBER@...—the results are consistent but only within a transaction
• col * 1 * 1 gives a different result than col * 1, with no pattern that I can discern (and so for col * 1 * 1 * 1, etc.; but col * 2 is consistent with col * 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?).

Answer 1

It is possible to get completely "invalid" numbers into the database, even using PL/SQL. Your guess about 193 is correct, DUMP(X, 1010)

X = DUMP... -1 = 62,100,102 0 = 128 +1 = 193,2 +2 = 193,3

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.