What exact exception to be caugth while calling TO_DATE in pl/sql code

Question

I have below code to_date('1311313', 'yymmdd') which actually throws exception with saying invalid month. Which is can manage as

exception
when others then
  sop('date format is wrong');

Here the problem is everything will get caught which I do not want to do as if some other error will occur then also it will pass the message date format is wrong. I also do not want to create a user defined exception. Just want to know which exception is being thrwon out so that I can use in my code like below

exception
when name_of_exception then
  sop('date format is wrong');

Solution 1

There are at least two approaches to handle different exceptions raised during an attempt to convert character literal to a value of DATE data type:

1. Define as many exception names and associate them with Oracle error codes, using exception_init pragma, as many exceptions to_date() function is able to raise.
2. Create a stand alone, or part of a package, wrap-up function for to_date() function, with one when others exception handler.

Personally I lean toward the second one.

SQL> create or replace package util1 as
  2    function to_date1(
  3      p_char_literal in varchar2,
  4      p_date_format  in varchar2
  5     ) return date;
  6  end;
  7  /
Package created

SQL> create or replace package body util1 as
  2  
  3    function to_date1(
  4      p_char_literal in varchar2,
  5      p_date_format  in varchar2
  6     ) return date is
  7    begin -- in this situation it'll be safe to use `when others`.
  8      return to_date(p_char_literal, p_date_format);
  9    exception 
 10      when others then
 11        raise_application_error(-20001, 'Not a valid date');
 12    end;
 13  
 14  end;
 15  /
Package body created

Now, there is only one exception to handle, -20001 Not a valid date, and your PL/SQl block might look like this:

SQL> set serveroutput on;

-- [1]  otherwise, for '1311313' the ORA-01830 exception would be raised   
SQL> declare
  2    not_a_valid_date exception;
  3    pragma exception_init(not_a_valid_date, -20001);
  4    l_res date;
  5  begin         
  6    l_res := util1.to_date1('1311313', 'yymmdd');
  7  exception
  8    when not_a_valid_date then
  9      dbms_output.put_line(sqlerrm);
  10     -- or other handler sop('date format is wrong');
  11  end;
  12 /

ORA-20001: Not a valid date

-- [2] otherwise, for '000000' the ORA-01843(not a valid month) 
--     exception would be raised
SQL> declare
  2    not_a_valid_date exception;
  3    pragma exception_init(not_a_valid_date, -20001);
  4    l_res date;
  5  begin       
  6    l_res := util1.to_date1('000000', 'yymmdd');
  7  exception
  8    when not_a_valid_date then
  9      dbms_output.put_line(sqlerrm);
  10     -- or other handler sop('date format is wrong');
  11  end;
  12  /

ORA-20001: Not a valid date

Solution 2

The Internally Defined Exceptions section of the Oracle Database PL/SQL Language Reference says:

An internally defined exception does not have a name unless either PL/SQL gives it one (see "Predefined Exceptions") or you give it one.

You code throws the exception ORA-01830:

SQL> select to_date('1311313', 'yymmdd') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Since it is not one of the Predefined Exceptions, you must give it a name yourself:

declare
  ex_date_format exception;
  pragma exception_init(ex_date_format, -1830);

  v_date date;
begin
   select to_date('1311313', 'yymmdd')
     into v_date
     from dual;
exception
  when ex_date_format then
    sop('date format is wrong');
end;
/

Comments

[gahlot.jaggs]

Thanks for your valuable information, but in my case I am getting error code as ORA-01843, not the one which you are getting (01830), that means this code depends upon the oracle version used. Which inherently I belive is not a robust solution and need to change tomorrrow if my version gets changed? correct me if I am wrong.

[Marco Baldelli]

I get ORA-01830 on both Oracle 11.1 and 11.2, on which version do you get a different exception trying to convert the '1311313' string? Your string has 7 characters and your date mask only has 6.

[gahlot.jaggs]

sorry my mistake. yes indeed i am getting the same ORA-01830 and yea I have to parse 6 characters only where I am passing 131313 for yymmdd mask. Thanks for solution.

[Nick Krasnov]

@gahlot.jaggs In fact, while working with dates, depending on the level of incorrectness of input data you can get different errors. It might be ORA-01843 if you provided incorrect month, it might be ORA-01847 if day was wrong, it might be ORA-01830 or ORA-01861 and so forth. So you need to take it into consideration.

[gahlot.jaggs]

@Nicholas..Yea just now i got a different ORA error while calling to_date('000000', 'yymmdd') so my question remained same, how can we encorporate this situation? To be more clear my end goal is to convert a varchar of 6 length to date and and then validate against future date. for the same I am using to_date function to convert it to date type. Any Help?

[gahlot.jaggs]

Thanks for your valuable time for coding above efforts and solution, but again I would say I do not want to capture when others then because it will capture everything else as well apart, for example even before calling to_date if some block of code is throwing no data found then also this will get captured and programmer might not known which cause the issue because eventually both will get cauth into when others then. this was my initial issue with the code.

[Yiannis Nennes]

@gahlot.jaggs the others exception is inside the date function and will only capture errors inside this function. If something else happens before calling this function, that exception will not be masked.

[Nick Krasnov]

@gahlot.jaggs That's why we've created the to_date1() function and placed when others exception handler in it. You wont be using standard to_date() function, instead you will be be using util1.to_date1() function to do the conversion. The only exceptions our when others catches are exceptions generated by to_date() function inside the to_date1() function, nothing more. Try it.