Implicit Conversion Errors
28th November 2011 6 Comments
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';The select was failing with the error. ORA-01821: date format not recognized
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.