Getting strange problem with TO_NUMBER function in Oracle
I am getting intermittent problem when executing to_number function in where clause in varchar2 column if the number of records exceeds a certain number n. I used n as there is no exact number of records this happens on. On one DB, this happens after n was 1 million on the other, when it was 0.1. million
eg. I have a table with 10 million records like Table Country which has a varchar2 field1 containing numeric data and Id
If I make a request as an example
select *
from country
where to_number(field1) = 23
and id >1 and id < 100000
It works
But if I make a request
select *
from country
where to_number(field1) = 23
and id >1 and id < 100001
I can't say the wrong number
Next, I try the query
select *
from country
where to_number(field1) = 23
and id >2 and id < 100001
It works again
Once I got the invalid number it got confused 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 looks like it has something to do with the SGA size, but google didn't help me with that.
Does anyone know about this issue with TO_NUMBER or oracle functions for big data?
a source to share
which has field1 varchar2 containing numeric data
This is not a good practice. Numeric data must be stored in NUMBER columns. The reason is simple: if we don't use a strong data type, we can find odd data in our varchar2 column. If it did, then a filter like this
where to_number(field1) = 23
will end with ORA-01722: invalid number
.
I can’t say for sure what is happening in your scenario because I don’t understand why the seemingly minor changes to the ID filters changed the success of the request. It would be instructive to see execution plans for different versions of queries. But I think this is more of a problem with your data than a bug in the SGA.
a source to share
Suggest doing the following to determine exactly if there are records that contain non-numeric data. As others have said, changes in the execution plan and the order of evaluation may explain why the error does not appear consistently.
(assuming SQLPlus as 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 for your original problem would be to rewrite the query to avoid implicit type conversion, eg.
SELECT id, TO_NUMBER( field1 )
FROM county
WHERE field1 = '23'
AND <whatever condition on id you want, if any>
a source to share
Consider writing the 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
a source to share