What exact exception should be indicated when calling TO_DATE in the pl / sql file

Question

I have a code to_date('1311313', 'yymmdd') that actually throws an exception with the words invalid month . Which can be controlled as

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

The problem here is that everything will be caught, which I don’t want to do, as if some other error occurred, and it will send the message date format is wrong . I also do not want to throw a user-defined exception. I just want to know which exception is being thrown so that I can use it in my code as shown below.

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

Answer 1

There are at least two approaches for handling various exceptions that occur during an attempt to convert a character literal to a value of type DATE :

• Define as many exception names and associate them with Oracle error codes using exception_init pragma, since many to_date() exceptions can raise a function.
• Create a standalone or part of a package wrapper function for the to_date() function, with one exception handler when others .

Personally, I lean toward the second.

 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 only one exception is thrown -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 

Answer 2

The Intra-level Exclusions section of the Oracle Database PL / SQL Language Reference:

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

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