The Oracle PL/SQL error message ORA-01858 occurs when a non-numeric character is encountered in a context where a numeric value is expected. This error is related to date and time operations in Oracle Database. The most common scenario is when attempting to perform date or timestamp arithmetic, comparisons, or conversions, and Oracle encounters a character that is not a valid part of a numeric date or timestamp representation.
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
To resolve the ORA-01858 error, you should review the SQL statement or PL/SQL code that triggered the error and identify where a non-numeric character is being used in a context where a numeric value is expected. Ensure that your date and timestamp values are properly formatted, and numeric operations are performed on valid numeric data.
