oracle 在 pl/sql 代码中调用 TO_DATE 时要注意什么确切的异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20042038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What exact exception to be caugth while calling TO_DATE in pl/sql code
提问by gahlot.jaggs
I have below code to_date('1311313', 'yymmdd')which actually throws exception with saying invalid month. Which is can manage as
我有下面的代码to_date('1311313', 'yymmdd')实际上抛出异常说invalid month。哪个可以管理为
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
这里的问题是一切都会被捕获，我不想这样做，好像会发生其他一些错误，然后它也会传递消息date format is wrong。我也不想创建用户定义的异常。只是想知道正在抛出哪个异常，以便我可以在我的代码中使用，如下所示
exception
when name_of_exception then
sop('date format is wrong');
回答by Marco Baldelli
The Internally Defined Exceptionssection of the Oracle Database PL/SQL Language Reference says:
Oracle 数据库 PL/SQL 语言参考的内部定义的异常部分说：
An internally defined exception does not have a name unless either PL/SQL gives it one (see "Predefined Exceptions") or you give it one.
内部定义的异常没有名称，除非 PL/SQL 给它一个名称（请参阅“预定义的异常”）或者您给它一个名称。
You code throws the exception ORA-01830:
您的代码抛出异常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;
/
回答by Nick Krasnov
There are at least two approaches to handle different exceptions raised during an attempt to convert character literal to a value of DATEdata type:
至少有两种方法可以处理在尝试将字符文字转换为DATE数据类型值期间引发的不同异常：
- Define as many exception names and associate them with Oracle error codes, using
exception_initpragma, as many exceptionsto_date()function is able to raise. - Create a stand alone, or part of a package, wrap-up function for
to_date()function, with onewhen othersexception handler.
- 定义尽可能多的异常名称并将它们与 Oracle 错误代码相关联，使用
exception_init编译指示，尽可能多的异常to_date()函数能够引发。 to_date()使用一个when others异常处理程序为函数创建一个独立的或包的一部分、包装函数。
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:
现在，只有一个异常需要处理， -20001 Not a valid date，您的 PL/SQl 块可能如下所示：
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
