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

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

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.

  • 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 '14 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 '14 at 17:34

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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