OracleORA-01722
ORA-01722: Invalid Number
ORA-01722 is raised when Oracle cannot convert a character string to a number. This often surfaces when comparing a VARCHAR2 column containing non-numeric data to a NUMBER column or literal.
Example Query
SELECT * FROM orders WHERE order_id = 'ABC123'; -- order_id is NUMBER; 'ABC123' cannot be converted
Common Causes
- 1Implicit conversion from VARCHAR2 to NUMBER fails due to non-numeric characters
- 2Numeric column stores mixed data including strings like 'N/A'
- 3Wrong data type used in a WHERE clause comparison
How to Fix It
Use TO_NUMBER with a format mask and error-safe VALIDATE_CONVERSION (12c+): WHERE VALIDATE_CONVERSION(col AS NUMBER) = 1. Cast explicitly: WHERE col = TO_NUMBER('42'). Clean up VARCHAR2 columns storing numeric data.
Need a reliable database?
Try Supabase — free PostgreSQL with a generous free tier. No credit card required.
Got a query causing this error?
Paste it into SQLbuddy and get an instant plain-English explanation with optimization tips.
Analyze My Query →