What exact exception to be caugth while calling TO

问题:

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

回答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

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