Querying A Table Results In An Apparently Incorrect Error ORA-01722 (Doc ID 1059215.1)

Last updated on JULY 27, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2016***

Symptoms

When executing a SQL SELECT statement, the operation results in an ORA-1722 error.
The SELECT statement contains a WHERE clause comparing a database column to a numeric literal value, e.g.

SQL> SELECT last_name FROM employees WHERE employee_id = 100;


In the above example, the column employee_id is a character column (CHAR, VARCHAR or VARCHAR2) and is compared to a literal numeric value.

As a result, the query does not execute and the operation is aborted, even though the table may contain a row with the value '100' in the corresponding column.

The error may be produced in different contexts, as the direct statement issued in SQL*Plus, or a dynamic query in a precompiler program or a java class.
For example, this Java code:

String id = '100' ;
String sqlStatement = 'SELECT last_name FROM employees WHERE employee_id = '+id ;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms