Implicit Conversion Errors
28/11/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.
Neil Chandler's DB Blog 
The question arises is, where did the date value come from. If it was hard-coded, then I agree with you. If it is being entered, then you might be better off capturing the locale of the user and inferring a date format from that.
Sometimes it is appropriate to actually use the session’s default date format rather than a hard-coded format mask. That is more often the case when converting a date to a string that the other way round, but it does happen.
LikeLike
Gary,
When you are converting a character string into a date format, you need to apply a consistent date mask and ensure your character string matches it. The format of your date should be controlled by the client application, and the presentation of that string for conversion should be consistent, irrespective of the visual input format, locale or any other variable. They must match.
I understand if you’re trying to display dates in a format familiar to each locale but that’s converting a date into a character string, not a character string into a date.
I’m think I’m really going to struggle to get a developer who doesn’t get explicit conversion to think about locale presentation.
regards
Neil
LikeLike
I agree you need consistency. It is a question of where you put it. For example, your application allows an upload of data which will include a date in a user’s local format. You can do a single ALTER SESSION to that local format and use a TO_DATE(char) or you can put a TO_DATE(char,local_format) everywhere (where local format is a variable rather than a literal). Or you do a ‘belt and braces’ and use both.
Unfortunately there are plenty of developers who see everything as an array of characters rather than as dates, numbers (or as phone numbers, post codes, IP addresses…)
LikeLike
I have annoying example of poor design mixed in with invalid assumptions of implicit conversion.
This is the problem: A report consistently errors due to a PK violation
After digging around I discovered that the report was getting the next sequential number based on existing records for the PK. Why didn’t they use a sequence; there is no special logic, just a sequential number?
Next I had a look at the table and discovered that the PK is VARCHAR2. Why is this varchar2; again nothing special, just a number?
Next step look at the data; PK started at 10,000 and had just clicked up to 100,000. Interesting.
Next run the select (MAX + 1) to find the next PK; returns 100,000 – that’ll be why we get the PK violation.
The developer assumed (incorrectly) that max would always return the numeric max even though it was VARCHAR2. This assumption worked until the data reached 100,000 at which point the character maximum of 99,999 is returned rather than the intended numeric maximum 100,000.
Let’s just say that I am glad the developer no longer works here.
LikeLike
Keryn!
Thanks for the comments! 🙂
It’s so true though. Is generally lazy development and it’s the boundary conditions that catch the unwary and indolent. Then the developer moves on ever seeing the problems they caused…
To anyone reading this – if Keryn agrees with me it must be true. The best Oracle PL/SQL programmer I have ever had the pleasure of working with.
Regards
Neil
LikeLike
Pingback: SYSTIMESTAMP INTERVAL Processing « Neil Chandler's DBA Blog