Oracle Scratchpad

April 24, 2020

Conversion Errors

Filed under: Oracle — Jonathan Lewis @ 11:03 am BST Apr 24,2020

I’ve been meaning to write this note for at least three years and was prompted to write up my draft notes this morning as a follow-up to yesterday’s note on the perils of applying a to_date() function to a date column. But then I took a look at the most recent questions on the Oracle Developer Forum and discovered that Tim Hall (@oraclebase) had (inevitably) already done the necessary write-up, so I’ve just left a brief note here (more for my own benefit than anything else) of the highlights with a link to his page.

Key features available in 12.2 to avoid conversion errors are:

  • The validate_conversion() function that returns a 1 or 0 depending whether on not an expression can be converted successfully to a specific type using a particular – returns null if the expression evaluates to null.
  • Extension to generic conversion functions (e.g. to_date()) that allow a “default” value to be used to replace the supplied value if the attempted conversion raises a conversion error.

Code Sample – report rows with a valid date (in French) that is earlier than the start of this year’s (English) tax year:


rem
rem     Script:         validate_conversion.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2017
rem     Purpose:
rem
rem     Last tested
rem             12.2.0.1
rem

create table t1 (v1 varchar2(42));
insert into t1 values('15-June-2016');
insert into t1 values('15-Juin-2016');
commit;

prompt  ==================================================================
prompt  Single predicate test - only rows that validate as dates in French
prompt  ==================================================================

select  *
from    t1
where   validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/

prompt  ===========================================================
prompt  Valid French dates that are before 6th April 2017 (English)
prompt  ===========================================================

select
        *
from    t1
where
        validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
and     to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
/

prompt  =========================================
prompt  Repeat the above with reversed predicates
prompt  =========================================

select
        *
from    t1
where
        to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
and     validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/

prompt  ====================================================
prompt  Repeat the above but force the order of evaluation
prompt  This will raise error ORA-01843: not a invalid month
prompt  ====================================================

select
        /*+ ordered_predicates */
        *
from    t1
where
        to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
and     validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/

prompt  ========================================================================
prompt  Handle the requirement with the 12.2 extended "to_date()" functionality
prompt  ========================================================================

select
        *
from
        t1
where
        to_date(
                v1 default '06-Avril-2017' on conversion error,
                'dd-month-yyyy',
                'nls_date_language=french'
        ) < to_date(
                '06-Apr-2017',
                'dd-mon-yyyy',
                'nls_date_language=English'
        )
/

Is there any guarantee that a validate_conversion() function will always be called before a call that attempts to use the conversion in another predicate. I doubt it, there’s no indication in the manuals and no general guarantee from Oracle about order of execution of predicates, so I wouldn’t risk it. Maybe the only safe use would be a CASE expression (which short-circuits) with a “when successful then” clause for evaluation of the actual conversion. You’d need to take a little extra care to remember to handle nulls correctly.

Maybe replace the table with a non-mergeable inline view that eliminates the failures? Possibly not, the optimizer might still do a simple filter pushdown.

 

 

 

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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.

Powered by WordPress.com.