For quite some time I planned to write a short series about automatically deploying Oracle Servers with Ansible – particularly with the module “ansible-oracle“. My Time Thieves didn’t allow that, so today, on ODC Appreciation Day, let me give this short rave on the great contribution by Mikael Sandström to our community!
This is a translation of my German article which was requested through “Google Translate” many times. It seems that throughout the years this error and its mostly simple causes haven’t lost their significance.
Here’s a sample scenario:
- A table’s VARCHAR2 column shall be searched for numeric entries;
- We use a query with a WHERE clause that constrains the scanned rows to only those containing numeric values.
- Despite that, “ORA-01722” is thrown.
So, what happened?
- The Oracle documentation states: “When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.” Think of an implicit TO_NUMBER being applied to the column.
- This implicit TO_NUMBER might be applied before the filter predicates of your WHERE clause.
- This in turn leads to an error, when an alphanumeric entry is hit — even when this entry wouldn’t be part of the scanned set if the filters were applied first.