0

Due to hardware failure after a power outage we had to recreate the database for one of our 3rd party applications from an dumpfile (created via exp). The old hardware had Oracle 10G R2 running while the new hardware is running Oracle 11G R2. Everything went smoothly and the application ran normally again.

The last few days our users reported problems with the software. After some research the application runs a sql statement which tries to filter a varchar column with a number thereby forcing an implicit conversion of the column.

In the previous version of Oracle everything went fine but now an "ORA-01722: invalid number" is thrown.

Might there be some setting we have missed for the new database?

We already contacted the developing company but it will take some time.

Sample table and data:

create table conversion (
  col1 varchar2(10)
  );

insert into conversion values ('test2');
insert into conversion values ('42');
insert into conversion values ('test3');

Simplified statement throwing the error:

select count(*) from conversion where col1 = 42;

Thanks in advance and best regards
Jan

Improve this question
6
  • How about showing us the SQL statement that generates the error?
    – kevinskio
    Jan 17, 2014 at 13:07
  • The problem is as ddaniel described it, I added an example above
    – belthazor
    Jan 17, 2014 at 16:13
  • 1
    Some developer somewhere should read up on the perils of mixing data types in the same column.
    – kevinskio
    Jan 17, 2014 at 16:24
  • Well I would normally say the same but in this kind of table I would sanction it
    – belthazor
    Jan 17, 2014 at 16:27
  • I'm almost 100% certain that 10g would have thrown the same error. So I don't think it's an upgrade issue. Maybe somewhere during the migration someone entered some testdata that broke that query.
    – a_horse_with_no_name
    Jan 17, 2014 at 17:35

2 Answers 2

Reset to default
1

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :)

Don't really see any solutions here except updating the SQL query. This is why nobody recommends the usage of implicit conversions. They could also cause a slight decrease of performance.

EDIT: If you don't have access to the query and have to wait for the dev team, but instead you have enough privileges on the database, and know for sure that the VARCHAR column holds only number, you could try to alter the table and convert the column type.

Improve this answer
2
  • Therein lies the problem. The column has not only numbers in it. The table is kind of holding information for user defined fields inside the application. Well, thanks anyway for you quick answer!
    – belthazor
    Jan 17, 2014 at 16:16
  • If the column held only numbers, the exception wouldn't have happened in the first place. So I doubt it can be converted.
    – a_horse_with_no_name
    Jan 17, 2014 at 17:34
-1

When you leave it on database to decide, it will try to convert char to number, because it can compare numbers more fast.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.