ORA-01722 Error Message

The ORA-01722 message is a fairly typical error in Oracle. It is an “invalid number” error, occurring during a failure when converting a character string to a valid number. While this type of error can be an easy fix, it can be caused by a multitude of numerical mistakes.

The first step in resolving this error is first keeping in mind what a valid number in Oracle represents. Valid numbers will contain the digits zero through nine, and can contain specific characters such as +/- at the beginning or end of the string, a single decimal point or an ‘E’ (upper or lower case) for floating point numbers in scientific notation. You should double check the character strings in the expression in question to make sure that the numbers follow these precise rules.

Practically speaking, there are several common and simple ways in which you could produce this error. If you attempt to add or subtract dates to or from string values, the result will produce ORA-01722 (such as in an expression ’06-JUL-15′ + ‘xyz’). This same idea applies when performing arithmetic functions on strings (for example, ‘xyz’ -571). Another mistake often seen is when the user inserts a comma instead of a decimal point in a numerical expression, due to their proximity on the keyboard. If a field is also filled with exclusively spaces, the error can be raised. By replacing the spaces with zeroes, the error can be resolved.

There are ways to fix this problem, with the first looking at an “INSERT INTO… VALUES (…)” attempt. Go through the steps above; check the data you are entering to make sure the numerical values are in line, and correct any that are invalid. In the event that all of the numbers are valid, the error possibly occurred from your columns being out of order (resulting in a VALUES string being placed into a NUMBER column, often deriving from removing or adding columns to a table).

Other situations can be more complex. In the event of using INSERT or UPDATE to supply values to a sub query, the ORA-01722 will be hidden. You will have to find the row that is supplying an invalid numerical string and resolve it. If instead of INSERT you attempt SELECT, the error will derive from an implicit conversion in the WHERE clause. Make sure numeric columns and character columns are not being compared, and be sure to exclude any rows not including valid numbers from the WHERE clause.

In the grand scheme of things, keeping aware of how numerical expressions and character strings will interact within your database is key to preventing errors like ORA-01722 from happening. It can be easy to get caught up in the tunnel vision that sometimes accompanies coding data programs, so keeping a meticulous eye out for when items likes dates and numerical expressions will converge on arithmetic functions can give you a cautious perspective that could save hours of frustration. Working with a dedicated Oracle consulting firm can provide you with the design elements and mindset necessary to avoid such mistakes in your management of an Oracle database.