ORA-01858: a non-numeric character was found where a numeric was expected

ORA-01858: a non-numeric character was found where a numeric was expected

Oracle PL/SQL error message: ORA-01858: a non-numeric character was found where a numeric was expected.

Cause:

The input data to be converted using a date format model was incorrect.
The input data did not contain a number where a number was required by the format model.

Solution:

Change the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

Example:

declare
  v_output varchar2(100);
begin
  select to_date(sysdate,'dd-mm-yyyy') into v_output from dual;
  DBMS_OUTPUT.PUT_LINE('date output: '||v_output);
end;

Output:

ORA-01858: a non-numeric character was found where a numeric was expected.

ORA-06512: at line 4

Correct:

declare
  v_output varchar2(100);
begin
--select to_date(sysdate,'dd-mm-yyyy') into v_output from dual;
  select to_char(sysdate,'dd-mm-yyyy') into v_output from dual;
  DBMS_OUTPUT.PUT_LINE('date output: '||v_output);
end;

Output:

date output: 30-11-2015