Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How about showing us the SQL statement that generates the error? –  kevinsky Jan 17 at 13:07
    
The problem is as ddaniel described it, I added an example above –  belthazor Jan 17 at 16:13
    
Some developer somewhere should read up on the perils of mixing data types in the same column. –  kevinsky Jan 17 at 16:24
    
Well I would normally say the same but in this kind of table I would sanction it –  belthazor Jan 17 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 at 17:35
show 1 more comment

1 Answer

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.

share|improve this answer
    
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 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 at 17:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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