PL/SQL中出现"ORA-01861:文本与格式字符串不匹配“错误
有人能帮我理解一下下面的代码行做错了什么吗？
res_start_time_ := to_date(to_char(account_date_, 'YYYYMMDD ') || sched_ftime_, 'YYYYMMDD HH24:MI');
res_start_time_和account_date_属于DATE类型。
sched_ftime_为VARCHAR2类型，可以为NULL。
在一个测试场景中，当account_date_的值为NULL，sched_ftime_的值为NULL时，我得到了ORA-01861: literal does not match format string错误。
有人能给我解释一下我做错了什么吗?我怎样才能改掉这个错误？
回答 2
Stack Overflow用户
发布于 2021-08-27 06:44:27
源代码正在尝试形成一个字符串，该字符串可以转换为同时具有日期值和时间值的日期。
此to_char(account_date_, 'YYYYMMDD ')将日期值转换为以空格结尾的9个字符的字符串，该字符串允许使用包含小时和分钟的字符串连接。连接后，它会尝试将该值转换为精确到分钟的日期值。
但是，如果sched_ftime_的非空值不是可以转换为HH24:MI的形式，则会出现错误。例如，'123456' is to long只能被解释为小时和分钟。
这可以复制：
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
DECLARE
res_start_time_ DATE;
account_date_ DATE := TRUNC(SYSDATE);
sched_ftime_ VARCHAR2(20) := '123456'; /* this value fails */
BEGIN
res_start_time_ := to_date(
to_char(account_date_, 'YYYYMMDD ') || sched_ftime_,
'YYYYMMDD HH24:MI'
);
DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/
ORA-01861: literal does not match format string
ORA-06512: at line 6但是，较短的值，例如'1234'，可以解释为小时和分钟：
DECLARE
res_start_time_ DATE;
account_date_ DATE := TRUNC(SYSDATE);
sched_ftime_ VARCHAR2(20) := '1234'; /* this value works */
BEGIN
res_start_time_ := to_date(
to_char(account_date_, 'YYYYMMDD ') || sched_ftime_,
'YYYYMMDD HH24:MI'
);
DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/
1 rows affected
dbms_output:
2021-08-27 12:34:00因此，我建议您通过将第二个参数的长度限制为4个字符(可能使用substr() )来保护转换为日期
DECLARE
res_start_time_ DATE;
account_date_ DATE := TRUNC(SYSDATE);
sched_ftime_ VARCHAR2(20) := '123456'; /* this value gets truncated */
BEGIN
res_start_time_ := to_date(
to_char(account_date_, 'YYYYMMDD ')
|| substr(sched_ftime_,1,4),
'YYYYMMDD HH24:MI'
);
DBMS_OUTPUT.PUT_LINE(res_start_time_);
END;
/您可能需要对该varchar2值进行其他验证，以便它们也都是数字。或者，如果要将冒号包含在小时和分钟值中，则总长度需要为5个字符。简而言之，您需要检查小时和分钟，以便它们合乎逻辑且有效。
注意:对于我扩展了here的源db<>fiddle，表扬到了MT0
Stack Overflow用户
发布于 2021-09-05 22:35:40
问题的根源是您允许时间组件(sched_ftime_)假定您事先不知道确切格式的值。您可以做到这一点，但它有一个编码要求。您需要建立允许的值，然后在运行时派生格式规范，如果值与允许的模式不匹配，则抛出错误。什么是允许的，验证是简单的还是复杂的，就像你想要的那样。作为演示，以下函数查找5种模式:hh24mi、hh24miss、hh24:mi、hh24:mi:ss和null。对于那些，它返回适当的日期，对于其他任何情况，它都返回一个应用程序定义的异常。
create or replace
function get_actual_date_time( account_date_ date
, sched_ftime_ varchar2
)
return date
is
k_format_base constant varchar2(8) := 'yyyymmdd';
k_bad_time_format_msg constant varchar2(32) := ' invalid time specification.';
-- declare regexp for valid time formats
k_regx_time_hh24mm constant varchar2(7) := '^\d{4}$';
k_regx_time_hh24mmss constant varchar2(7) := '^\d{6}$';
k_regx_time_hh24mm_s constant varchar2(11) := '^\d\d:\d\d$';
k_regx_time_hh24mmss_s constant varchar2(16) := '^\d\d:\d\d:\d\d$';
-- declare actual format specification corresponding to valid format
k_fmt_time_hh24mm constant varchar2(6) := 'hh24mi';
k_fmt_time_hh24mmss constant varchar2(8) := 'hh24miss';
k_fmt_time_hh24mm_s constant varchar2(7) := 'hh24:mi';
k_fmt_time_hh24mmss_s constant varchar2(10) := 'hh24:mi:ss';
l_time_format varchar2(16);
begin
case when sched_ftime_ is null then
l_time_format := null;
when regexp_like ( sched_ftime_,k_regx_time_hh24mm) then
l_time_format := k_fmt_time_hh24mm;
when regexp_like ( sched_ftime_,k_regx_time_hh24mmss) then
l_time_format := k_fmt_time_hh24mmss;
when regexp_like ( sched_ftime_,k_regx_time_hh24mm_s) then
l_time_format := k_fmt_time_hh24mm_s;
when regexp_like ( sched_ftime_,k_regx_time_hh24mmss_s) then
l_time_format := k_fmt_time_hh24mmss_s;
else
raise_application_error( -20109,'''' || sched_ftime_ || '''' || k_bad_time_format_msg);
end case;
return to_date(to_char(account_date_,k_format_base) || sched_ftime_
, k_format_base || l_time_format);
end get_actual_date_time;请记住，上面只是的一个例子，只是，还有很多需要改进的地方。例如，is将接受99:99:99的时间规范，即使它显然是无效的时间规范。但它适合'^\d\d:\d\d:\d\d$'的验证。它也不会尝试验证有效的时间规范06:45 PM。参见fiddle here。
https://stackoverflow.com/questions/68946166
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号