"ORA-01843:无效月份“错误

Question

当试图从代码中运行该过程时，我遇到了此错误。

"ORA-01843:无效月份ORA-06512: at "NOSTAS_OWNER.INSERT_EXPORTMATRIX"，第68行ORA-06512: at 1行“

我将日期传递为"DD/MM/YYYY“字符串。不知道是什么导致了这个错误。在SQL Developer中运行它时，会给出此错误。

ORA-06502: PL/SQL:数字或值错误:字符转换错误ORA-06512:在"NOSTAS_OWNER.INSERT_EXPORTMATRIX"，第68行ORA-06512:第17行

create or replace PROCEDURE INSERT_EXPORTMATRIX
(
  keyClass export_matrix.EXM_KEY_CLASS%type,
  keyClassValue export_matrix.EXM_KEYCLASS_VALUE%type,
  changedClass export_matrix.EXM_CHANGED_CLASS%type,
  changedClassValue export_matrix.EXM_CHANGEDCLASS_VALUE%type,
  dtTo IN VARCHAR2,
  dtFrom IN VARCHAR2,
--    dtTo export_matrix.EXM_DATEFROM%type,
--    dtFrom EXPORT_MATRIX.EXM_DATETO%type,
  vresult OUT NUMBER
) AS 

iskeyClass number;
--today varchar2(50);
--today date;
fvvECCN number;
fvvALNR number;

BEGIN
iskeyClass := 0;
fvvECCN := 0;
fvvALNR := 0;

SAVEPOINT start_tran;

  SELECT count(*) into iskeyClass FROM EXPORT_MATRIX 
    WHERE EXM_KEY_CLASS=keyClass and EXM_KEYCLASS_VALUE=keyClassValue; 
--    SELECT to_date(SYSDATE,'DD/MM/YYYY') into today  FROM DUAL;   
    SELECT count(*) into fvvECCN FROM FIELDS_VALUE_VALIDATION WHERE
        --FVV_FAVL_NAME = 'ECCN' and FVV_VALUE = keyClassValue and (dtTo > trunc(sysdate));
        FVV_FAVL_NAME = 'ECCN' and FVV_VALUE = keyClassValue and (sysdate between dtFrom and dtTo) or dtTo is null;

    SELECT count(*) into fvvALNR FROM FIELDS_VALUE_VALIDATION WHERE
        FVV_FAVL_NAME = 'ALNR' and FVV_VALUE = changedClassValue and (sysdate between dtFrom and dtTo) or dtTo is null;

BEGIN
-- if (iskeyClass = 1 and (dtTo < trunc(sysdate))) or iskeyClass = 0  then
    if (iskeyClass = 1 and (dtTo between DtFrom and sysdate)) or iskeyClass = 0 then
       Insert into EXPORT_MATRIX (EXM_KEY_CLASS, EXM_KEYCLASS_VALUE, EXM_CHANGED_CLASS, 
        EXM_CHANGEDCLASS_VALUE, EXM_DATEFROM) 
        values (keyClass,keyClassValue,changedClass,changedClassValue,trunc(sysdate));

        if fvvECCN = 0 then
                   Insert into FIELDS_VALUE_VALIDATION (FVV_FAVL_NAME,FVV_VALUE,FVV_DATEFROM) 
                    values ('ECCN',keyClassValue,trunc(sysdate));
        end if;
                if fvvALNR = 0 then
                   Insert into FIELDS_VALUE_VALIDATION (FVV_FAVL_NAME,FVV_VALUE,FVV_DATEFROM) 
                    values ('ALNR',changedClassValue,trunc(sysdate));
                end if;
  end if;

--  if keyClass = 1 and (dtTo > trunc(sysdate)) then
    if keyClass = 1 and (sysdate between dtFrom and dtTo) or dtTo is null then
         update EXPORT_MATRIX set    
          EXM_DATETO=to_date(dtTo)        
       WHERE EXM_KEY_CLASS=keyClass and EXM_KEYCLASS_VALUE=keyClassValue;  
  end if;

  END;
  commit;
  vresult := 1;
  EXCEPTION
WHEN OTHERS THEN

  ROLLBACK TO start_tran;
  RAISE;
END INSERT_EXPORTMATRIX;

Answer 1

在转换到日期的字符串时，应添加掩码：

to_date(dtTo,'DD/MM/YYYY') 

此外，在与sysdate进行比较时，还应该将字符串转换为date，因此：

sysdate between to_date(dtFrom,'DD/MM/YYYY') and to_date(dtTo,'DD/MM/YYYY')