ORA-01722 Error

Today I have received this error;

 

CLOUD*ANTO> sta G:\SQL_FRA\QUERY_0x0022
AND Z.CD_ELEMENT_PROVIDER IN
*
ERROR en línea 176:
ORA-01722: invalid number

 

This error is not new, at least for me, I have sometimes occurred, the problem is why we are making an implicit conversion data, the engine, in this case Oracle (also happens in SYBASE ASE / IQ, SQL Server, MySQL …) tries to convert values ​​for one condition at the same type.

In this particular case is a NUMBER and VARCHAR in an IN clause, the solution is to make an explicit conversion like the one shown below;

 

 WHERE  Z.CD_ELEMENT_PROVIDER  IN
(SELECT TO_CHAR (TAB_ELEMENTS.CD_ELEMENT_PROVIDER)
                  FROM TAB_SATELLITE_RECEIVER, ...

 

Summary

Must always make an explicit conversion so that these problems do not occur. Mainly development is people who should do it as a “best practices” and in general for all database engines.

 

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s