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';

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;
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.


6 Responses to Implicit Conversion Errors

  1. Gary says:

    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.


    • 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.




      • Gary says:

        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…)


  2. Keryn says:

    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.


    • 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.




  3. Pingback: SYSTIMESTAMP INTERVAL Processing « Neil Chandler's DBA Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.