"ORA-01843: not a valid month" error

Question

I am experiencing this error when trying to run the procedure from the codes.

"ORA-01843: not a valid month ORA-06512: at "NOSTAS_OWNER.INSERT_EXPORTMATRIX", line 68 ORA-06512: at line 1"

I'm passing the date as "DD/MM/YYYY" string. Not sure what caused the error. When running it in SQL Developer, it gives this error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "NOSTAS_OWNER.INSERT_EXPORTMATRIX", line 68 ORA-06512: at line 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;

mkuligowski mkuligowski · Accepted Answer · 2018-04-19T07:55:03

You should add a mask when you convert a string to date:

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

Moreover, you should also convert string to date when you comparing with sysdate, so:

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

"ORA-01843: not a valid month" error

1 Answers