Getting weird issue with TO_NUMBER function in Oracle

Question

I have been getting an intermittent issue when executing to_number function in the where clause on a varchar2 column if number of records exceed a certain number n. I used n as there is no exact number of records on which it happens. On one DB it happens after n was 1 million on another when it was 0.1. million.

E.g. I have a table with 10 million records say Table Country which has field1 varchar2 containing numberic data and Id

If I do a query as an example

select * 
from country 
where to_number(field1) = 23
and id >1 and id < 100000

This works

But if I do the query

select * 
from country 
where to_number(field1) = 23 
and id >1 and id < 100001

It fails saying invalid number

Next I try the query

select * 
from country
where to_number(field1) = 23 
and id >2 and id < 100001

It works again

As I only got invalid number it was confusing, but in the log file it said

Memory Notification: Library Cache Object loaded into SGA
Heap size 3823K exceeds notification threshold (2048K)
KGL object name :with sqlplan as (
    select c006 object_owner, c007 object_type,c008 object_name
      from htmldb_collections
     where COLLECTION_NAME='HTMLDB_QUERY_PLAN'
       and c007 in ('TABLE','INDEX','MATERIALIZED VIEW','INDEX (UNIQUE)')),
ws_schemas as(
    select schema 
      from wwv_flow_company_schemas
     where security_group_id = :flow_security_group_id),
t as(
        select s.object_owner table_owner,s.object_name table_name,
               d.OBJECT_ID
          from sqlplan s,sys.dba_objects d

It seems its related to SGA size, but google did not give me much help on this.

Does anyone have any idea about this issue with TO_NUMBER or oracle functions for large data?

Answer 1

which has field1 varchar2 containing numberic data

This is not good practice. Numeric data should be kept in NUMBER columns. The reason is simple: if we don't enforce a strong data type we might find ourselves with non-numeric data in our varchar2 column. If that were to happen then a filter like this

where to_number(field1) = 23 

would fail with ORA-01722: invalid number.

I can't for certain sure say this is what is happening in your scenario, because I don't understand why apparently insignificant changes in the filters of ID have changed the success of the query. It would be instructive to see the execution plans for the different versions of the queries. But I think it is more likely to be a problem with your data than a bug in the SGA.

Answer 2

Assuming you know that the given range of ids will always result in field1 containing numeric data, you could do this instead:

select *
from (
  select /*+NO_MERGE*/ * 
  from country 
  where id >1 and id < 100000
)
where to_number(field1) = 23;

Answer 3

Suggest doing the following to determine for sure whether there are records containing non-numeric data. As others have said, variations in the execution plan and order of evaluation could explain why the error does not appear consistently.

(assuming SQLPlus as the client)

SET SERVEROUTPUT ON

DECLARE
   x  NUMBER;
BEGIN
  FOR rec IN (SELECT id, field1 FROM country) LOOP
    BEGIN
      x := TO_NUMBER( rec.field1 );
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line( rec.id || ' ' || rec.field1 );
    END;
  END LOOP;
END;
/

An alternative workaround to your original issue would be to rewrite the query to avoid implicit type conversion, e.g.

SELECT id, TO_NUMBER( field1 )
  FROM county
  WHERE field1 = '23'
    AND <whatever condition on id you want, if any>

Answer 4

Consider writing an IS_NUMBER PL/SQL function:

CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER 
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/

---

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('TEST') IS NOT NULL;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('123.45') IS NOT NULL;

  COUNT(*)
----------
         1