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

score 9 · Answer 1 · answered Nov 18 '13 at 07:25

9

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

answered Nov 18 '13 at 07:25

Marco Baldelli

3,638
1
22
29

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. – gahlot.jaggs Nov 18 '13 at 09:12
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. – Marco Baldelli Nov 18 '13 at 09:46
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. – gahlot.jaggs Nov 18 '13 at 11:03
1

@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. – Nick Krasnov Nov 18 '13 at 11:16
1

@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 Nov 18 '13 at 11:44

Nick Krasnov · Accepted Answer · 2013-11-18T14:00:16.570

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:

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

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. — gahlot.jaggs, Nov 18 '13 at 12:51
@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. — Yiannis Nennes, Nov 18 '13 at 13:00
@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. — Nick Krasnov, Nov 18 '13 at 13:44

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

2 Answers2

Linked