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

gahlot.jaggs picture gahlot.jaggs · Nov 18, 2013 · Viewed 15.3k times · Source

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

Answer

Marco Baldelli picture Marco Baldelli · Nov 18, 2013

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