Oracle / PLSQL: ORA-01858 Error Message

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

Cause

You tried to enter a date value using a specified date format, but you entered a non-numeric character where a numeric character was expected.

Sometimes in working with database software, matters of syntax and formatting can overlap to create a plethora of problems for users. Some platforms may require only numerical digits in certain sections. Others may need the month in a date written out in full and phonetic form.

Despite the needs of the program, it would be impossible for any one individual to strictly adhere to the parameters of Oracle on every occasion. The ORA-01858 can be considered as a warning to users are jumping between various tables and data sets at a quick pace and are letting the formatting concerns slip through the cracks.

The Problem

The ORA-01858 error is an issue of syntax and formatting. When prompted with the error, the user will receive an accompanying message that will state that “a non-numeric character was located where a digit was expected”. Oracle docs list the cause of this error as “the input data to be converted using a date format model being incorrect. The input data did not contain a number where a number was required by the format model.”

What does this mean in simple terms? This error essentially occurs when a user attempts to convert a string of data into a date, and in doing so specified a date being passed in a particular format. This will most commonly be something along the lines of DD-MM-YYYY. The error pops up when the user then tries to pass the date in the DD-MON-YYYY format. In such a case, a character-stated month such as “JAN” for January or “JUL” for July, will cause the error by inserting an alphabetical name in a place that Oracle expects to read a numerical name (such as “01” for January or “07” for July). Because this error is prompted by a character presence in a numerically-determined field, the most likely culprit will be a date entered in a format that writes the month specifically out in some form.

The Solution

In order to solve this formatting issue, there are basically two options to take. The user can either fix the input data to conform to the format in question, or the user can switch the date format model to ensure that the elements match in number and type and then retry the operation. In most cases, the former will be the simpler strategy.

Before we continue, it would be important to note that if a user is attempting to fetch strings from a table and subsequently convert them into dates, the data in the table should be checked prior to using the date format string. If the table contained strings that are note actually date values and the user attempts to convert them, then this error could be raised (although this is less common).

Since there is no predefined exception for handling this specific error, let us look at an example of a user-defined way of addressing it. In this scenario, the default date format is set to DD-MON-YY and the following statement was executed:

SQL> select to_date(’10-JUN-2014’, ‘DD-MM-YYYY’) from dual;

ERROR:

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

no rows selected

At this juncture, after assessing the data and determining that the date information needs to be adjusted, the following can be run:

SQL> select to_date(’10-JUN-2014’, ‘DD-MON-YYYY’) DT from dual;

   DT

10-JUN-2014

This should clear up the hypothetical date in question and allow the statement to run smoothly.