On a couple of occasions in recent days, folks have run into invalid data in their databases, and asked how to detect these invalid values. One had to do with output from add_months generating invalid DATE values, accepted by the DB without validation. The other had a NUMBER, org_id, showing an invalid number format, but saved in a NUMBER column all the same.
I understand there are ways to get invalid data into the database. One may wish that wasn't possible, but it is. (Below I will show one way to do this.) The question was asked, assuming we can't scrub the DB to eliminate the invalid values, is there a way we can at least DETECT them?
A frequent suggestion is "use the value in a function, like to_char(...), or in an operation, like ... + 0 or ... || 'something'; if ... is the invalid value, the function or operation should throw an exception.
However, in trying to learn how to intentionally insert invalid data into the DB, and playing with what I got, I found that calling a function on an invalid value, or using an invalid value in an operation, does NOT always throw an error. Whence my questions:
- Did I get this right, or am I making a mistake somewhere? and
- Is there a direct way to validate data against its declared type in Oracle? (Sorry if this is an Oracle 101 question, that is where I am!)
Why does this matter? Well, the invalid org_id issue discussed in another thread came up in production. So did the invalid date issue. One wouldn't intentionally insert invalid data in the DB (like I do below); however, I can see someone writing code, in PL or C or who knows what, that inserts RAW values into the DB. Converting a null character, \0, the "null terminator" of character strings in C (and likely in various DB and non-DB products), instead of simply dropping it, is a possible such culprit. Of course, that would be bad programming - but not today; that may be something that was done 30 years ago. Bad data DOES get into databases, and in most cases that happens in subtle ways, not like I do below.
So here is my example. Since this may matter, my Oracle version is 11gR2, Express edition (free).
The NUMBER 1000000 has the internal representation: Type 2, Length 2, Bytes: 196, 2. (See the discussion here: I have non numeric values in my number column. How it is possible.) The original poster there had an invalid value, with the representation: Type 2, Length 3, Bytes: 196, 2, 1. That last byte, 1, should never be the last byte in a NUMBER representation. I set out to intentionally get such data into a number column. It's not difficult:
SQL> CREATE TABLE t (col1 NUMBER);
Table created.
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 DBMS_STATS.convert_raw_value('c40201', n);
5 INSERT INTO t VALUES (n);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> column dumped format a40
SQL> SELECT dump(col1) AS dumped FROM t;
DUMPED
----------------------------------------
Typ=2 Len=3: 196,2,1
1 row selected.
OK... now the fun begins. Let's see if col1 + 0 causes an error.
SQL> select col1 + 0 from t;
COL1+0
----------
1 row selected.
Huh? Let's try something else. Let's try col1 + 1:
SQL> select col1 + 1 from t;
COL1+1
----------
1000001
1 row selected.
Interesting. Somehow the DB knows the value is not quite 1000000 - it doesn't show anything when I add 0. But when I ask it to add 1, it does show 1000001?
SELECT col1 FROM t returns an empty string, just like col1 + 0. However, col1 is not null; SELECT CASE WHEN col1 IS NULL THEN 'n' ELSE 'x' END FROM t returns x. Also, while SQL*Plus does not show a value with SELECT col1, SQL Developer does - it shows 1000000. (It would be REALLY odd if a three-byte representation, 196, 2, 1 was allowed to represent NULL; thankfully it doesn't.)
to_char does not throw an error either. Instead, here is what I got.
SQL> select to_char(col1) from t;
TO_CHAR(COL1)
----------------------------------------
┐ ┐P
4P
┐
1 row selected.
(Actually this is incomplete - on my screen, there are also a couple of small question marks inside a small rectangle each... try it in SQL*Plus, in SQL Developer I just got whitespace.) Instead of throwing an error, to_char produces nonsensical output.
Then I added another column to table t, col2, of type VARCHAR2. I tried to update t to SET col2 = to_char(col1); I got the error message below:
SQL> UPDATE t SET col2 = to_char(col1);
UPDATE t SET col2 = to_char(col1)
*
ERROR at line 1:
ORA-12899: value too large for column "INTRO"."T"."COL2" (actual: 4294967295, maximum: 30)
Then I asked the DB to show me the length of to_char(col1)... even though in the example above the length was calculated (and it is huge), calling length on it directly crashed my session:
SQL> select length(to_char(col1)) from t;
select length(to_char(col1)) from t
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10020
Session ID: 113 Serial number: 1181
ERROR:
ORA-03114: not connected to ORACLE
This is all funny, perhaps, but also irrelevant. But the main point is that neither an arithmetic expression nor to_char flagged col1 as an invalid number!