Oracle data type conversion issue with to_number function

Question

I ran across a weird scenario where to_number function is not being able to convert the numbers stored in string data type column in one database but not in another.

So, to rule out all possible scenarios such as additional spaces, nulls, junk etc, I have selected only one single and applied to_number function on the column and I totally this is not the best practice saving numbers as strings.

Interestingly, the query executes fine in one database but in others.

The below query runs fine in development but in test database.

select column_name 
from table_name 
where to_number(column_name) = 1618

Also, when i run the below query in test, I get results with out any error.

select to_number(column_name) from table_name

When I move the to_number function to where clause, that is when I'm getting invalid number error.

Any thoughts?

Thanks...

Well, then you have non-numeric values in your table. To_number in your "where" clause is going to do a full table scan. Any invalid values will throw an error. — OldProgrammer
– OldProgrammer, Commented Jan 24, 2014 at 18:18
Thanks for the quick reply but when i execute the query 'select to_number(column_name) from table_name'; it is giving results. is it not doing a full table scan then? I'm confused :( — user1751356
– user1751356, Commented Jan 24, 2014 at 18:24
Yes, it is. Then there cannot be an invalid value in that table. Since you don't show us any of the input or output data, can't make any determination. Here is a simple SqlFiddle that proves the error. — OldProgrammer
– OldProgrammer, Commented Jan 24, 2014 at 18:27
the prod version of your query, can you show us the code sample how you compare against the column? like a bind variable assignment.. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Jan 24, 2014 at 18:30
I'm not sure how I can provide the input or output of the results Since there are too many. Hence, I've limited my rows to a single row in the where clause and it's a good point that you mentioned pushing it to a where would not help as to_number will be applied on all rows in any case. select to_number(column_name) from table_name'; it is giving results. This drives me crazy. — user1751356
– user1751356, Commented Jan 24, 2014 at 18:36

Justin Cave · Accepted Answer · 2014-01-24 18:51:51Z

2

The problem is almost certainly that in the database that throws an error, you have at least one row that has a string value that cannot be converted to a number. The simplest way to find that row is generally to do something like

CREATE FUNCTION is_number( p_str IN VARCHAR2 )
  RETURN VARCHAR2
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 'Yes';
EXCEPTION
  WHEN others 
  THEN
    RETURN 'No';
END;

and then

SELECT column_name
  FROM table_name
 WHERE is_number( column_name ) = 'No';

As to why

SELECT to_number( column_name )
  FROM table_name

works, are you certain that you are fetching every row? Most tools will only fetch the first, say, 50 rows when you run a query and wait for the user to request more data before the database continues executing the query. Assuming that the invalid data is on row 10,001, Oracle would happily return the first 10,000 rows successfully and then throw an error when a request was made to fetch row 10,001.

answered Jan 24, 2014 at 18:51

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1751356 Over a year ago

I created a backup table in the db where I have this issue and loaded same data into the backup table. When I run the same query on backup table I get query result where as when I run the same query on the original table it says invalid number. So, I looked at execution plans of both tables, on the table where it is fetching result, 'rows were returned by the select statement' where as the table which has the problem says' rows from table were accessed using rowid got from a global (cross-partition) index'. Both has different cost, byte, cardinality. Is it because of the stats?

user1751356 Over a year ago

Definitely, not the data issue.

Justin Cave Over a year ago

@user1751356 - It's a data issue. If your real query is more complex than what you posted, the problem is that some plans will cause Oracle to look at the row with invalid data and attempt to convert it to a number while other plans cause Oracle to exclude those rows before applying the to_number. You'll want to fix the data problem rather than trying to ensure that whatever plan you get applies the predicates in an order that doesn't throw an error.

user1751356 Over a year ago

Thanks for such a quick response. It's actually a dimension table with 3500 rows. This query select x.id, x.column_name from (select id, column_name from schema.bkp_t where column_name <> 'Unspecified') x where to_number(column_name) between 1 and 500 gave result. In the result i saw 1, 2, 384 e.t.c so i used below query select * from schema.bkp_t a where to_number(a.column_name) = 1, now this second query is giving invalid number. :(

Justin Cave Over a year ago

@user1751356 - There is invalid data in the table. Did you use the function I provided to identify the row(s) with invalid data? Wait, are you saying that you know that you have rows where column_name is the string "Unspecified"? That would obviously not convert to a number successfully. And Oracle is free to evaluate the to_number predicate before or after the column_name <> 'Unspecified' predicate

|

Collectives™ on Stack Overflow

Oracle data type conversion issue with to_number function

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related