What does "ORA-01722: invalid number" mean?

The error message "ORA-01722: invalid number" is a common error encountered in Oracle databases. It indicates that Oracle attempted to convert a string value to a numerical value, but the string did not represent a valid number.

Here's a breakdown of what causes this error and how to address it:

1. The Core Issue

The Oracle database, during the execution of an SQL query or PL/SQL block, needs to perform operations on data. When you use functions or implicit data type conversions that involve numeric data types, the database expects the input to be in a format that can be interpreted as a valid number. When this conversion fails, the ORA-01722 error is raised. This usually occurs when a column defined as a number contains non-numeric characters or text.

2. Common Scenarios:

- Incorrect Input in SQL Queries: For instance, if you're trying to filter records based on a numeric column, but the filtering criteria is a string, for example, `WHERE id = 'abc'`, where 'id' is defined as a number, the error will happen.

- Data Import Errors: When data is imported, errors might happen when text-based sources are being imported directly into numerical columns or when the imported data does not conform to numeric standards.

- PL/SQL Assignments: If you're assigning string variables to numeric variables and the string is not a valid number, the ORA-01722 error will be thrown.

- Implicit Data Type Conversions: Oracle can attempt to perform implicit conversions, for instance during comparison or mathematical operations, and these can fail if it attempts to convert a non-numerical string.

3. Examples:

- Let's say you have a table named `employees` with a `salary` column defined as a NUMBER. The following will likely cause the error:

SELECT FROM employees WHERE salary = 'abc';

- Similarly, the following SQL update will also cause the same error:

UPDATE employees SET salary = 'not a number';

4. How to resolve ORA-01722:

- Identify the Erroneous Data: Inspect the data that's causing the issue. Determine whether you're dealing with incorrect source data or badly formed SQL queries that have unexpected input types. Use queries with appropriate type casting to find the problematic records.

- Use Explicit Conversion Functions: The functions like `TO_NUMBER` can be used to convert strings to numbers in a controlled way. This can reveal whether the string can be converted or not. For example: `SELECT TO_NUMBER('123');` will work, while `SELECT TO_NUMBER('abc');` will cause the same error.

- Data Cleaning: Ensure all columns defined as numbers only contain numeric characters or NULL values during imports, data manipulations etc. You may have to perform data cleaning and transformation of the source data.

- Validate Input Values: In applications and stored procedures, always validate your input values before storing them into a numeric column or use them in operations involving numbers.

- Handle Implicit Conversions carefully: Make sure that, whenever Oracle performs implicit conversions, there is no unexpected string input involved.

In summary, the ORA-01722 error highlights that the data is not in the correct number format during data manipulation and that the database is unable to process the values. Correct the data or modify the SQL queries to fix this issue.

Learn more about this