Back to top of file
How to fix it
The fix depends upon the exact expression which caused the problem. The following guide lists the possible SQL expressions which can give this error, with their most likely cause. When addressing this error, keep in mind that it can indicate a simple keystroke problem with the query, or a deeper problem with the query logic, or even the presence of bad data in the database itself.
You are doing an INSERT INTO ... VALUES (...)
One of the data items you are trying to insert is an invalid number. Locate and correct it.
If all of the numbers appear to be valid, then you probably have your columns out of order, and an item in the VALUES clause is being inserted into a NUMBER column instead of the expected VARCHAR2 column. This can happen when a table has columns added or removed.
You are doing an INSERT or UPDATE, with a sub query supplying the values.
Obviously, the preceding considerations apply here as well. What makes this more complicated is that the offending character string is hidden as a row in a table. The fix is to identify the row (or rows) which has the non-numeric string, and either change the data (if it is in error) or add something to the sub query to avoid selecting it. The problem is in identifying the exact row.
Assuming that the errant datum is an alphabetic character, one can use the following query:
SELECT ... WHERE UPPER(col) != LOWER(col)
where col is the column with the bad data.
You are doing a SELECT, rather than an INSERT or UPDATE.
In this case, there is probably an implicit conversion happening between some predicate in the WHERE clause. Check for a numeric column being compared to a character column.
If you are using the to_number function, make sure the format mask fits all possible character strings in the table.
If you know that a column contains both valid numbers and character strings, make sure that all rows which do not contain valid numbers are being excluded in the WHERE clause.
Other Rare Situations
To expand on the previous comment, if you have a column in a table which contains both valid numbers and character strings, it is just barely possible to get an ORA-01722 even if no character strings are being returned by your query. Example: two tables must be joined. In table A, the column is VARCHAR2, and in table B it is NUMBER. Table A also has non-numeric data in that column in some rows, and has a type column to make it obvious which rows are which. It is possible for the optimizer to choose an access plan in which the join is attempted before the filtering, which will cause the ORA-01772. The fix is to add a hint which changes the plan enough to bypass the rows causing the error.
Doing an explicit conversion can sometimes make things worse. For example, '+17', '-17', & ' 17' all convert successfully implicitly. The last one will raise the error if the 'S99' mask is used in the to_number function.
A field containing only spaces will raise this error. One fix is to replace the spaces with nulls or zeroes.
If you are querying a view rather than a table, any of the above could apply, and be hidden from sight. The fix is to add a predicate to the WHERE clause which excludes the troublesome rows.
