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