Implicit Conversion Errors

A while ago, I failed over a database (as planned) to it’s Dataguard copy, and of course everything worked as expected. Everything, that is, except a couple of reports which get sent directly from the database server early in the morning. The report generation had failed. After some investigation, we discovered that the newly active Dataguard server did not have NLS_DATE_FORMAT set in the environment, and the 2 reports in question were coded something like:

select col1, col2, col3 from user_data where user_date >= '25-Nov-2011 00:00:00';

If the developer had coded an explicit conversion, then we would not have experienced a problem.

select col1, col2, col3 from user_data
where user_date >= to_date('25-Nov-2011 00:00:00','DD-MON-YYYY HH24:MI:SS');

Coding with an implicit date mask is great and works successfully every time, as long as the NLS_DATE_FORMAT in your session matches the date mask you have supplied, which is course it always does. Until something changes and it doesn’t. In my experience, implicit conversion is probably the single greatest source of failure in systems and also one of the hardest to track down. It frequently occurs in a badly designed schema which doesn’t use the correct datatypes. I have seen schemas where everything is being stored a VARCHAR2, including numeric fields. This works fine as Oracle will happily insert implicit to_number functions into your code and return answers in ways which seem correct, until you get some rogue data into the database and everything falls apart.

USER1 @ orcl > -- Create a table but allow generic data, rather than specifying numeric data
USER1 @ orcl > -- The client will take care of validation. Of course it will.
USER1 @ orcl > create table implicit_problem (col1 varchar2(10), col2 varchar2(10));

Table created.

USER1 @ orcl >
USER1 @ orcl > -- Lets fill the table with reasonable data
USER1 @ orcl > insert into implicit_problem values (1,1);

1 row created.

USER1 @ orcl > insert into implicit_problem values (2,10);

1 row created.

USER1 @ orcl > insert into implicit_problem values (3,66);

1 row created.

USER1 @ orcl >
USER1 @ orcl > -- Oracle is putting an implicit to_number around col1*col2 to allow the calculation
USER1 @ orcl > select col1,col2,col1*col2 from implicit_problem;

COL1       COL2        COL1*COL2
---------- ---------- ----------
1          1                   1
2          10                 20
3          66                198

USER1 @ orcl >
USER1 @ orcl > -- And now lets have some incorrectly validated data
USER1 @ orcl > insert into implicit_problem values (4,'A');

1 row created.

USER1 @ orcl >
USER1 @ orcl >
USER1 @ orcl > -- And now the implicit conversion fails
USER1 @ orcl > select col1,col2,col1*col2 from implicit_problem;
ERROR:
ORA-01722: invalid number

no rows selected

USER1 @ orcl >
USER1 @ orcl > -- Cleanup
USER1 @ orcl > drop table implicit_problem;

Table dropped.

It’s much easier (and quicker) to catch bad data going into a system than it is to perform problem resolution. Always code explicitly for your data types. Implicit conversion in yuor coding invariably leads to hard-to-find bugs.