Re: Strange behaviour of Ora 9.2

The Oracle 9 optimizer has been enhanced. Are all the init.ora parameters the same, including block size, across the two databases ? Have you generated stats (including system_stats) for one and not the other ?

Order of predicate evaluation will allow queries that 'deserve' to crash with ORA-01722 actually to run to completion. And in Oracle 9.2 that order is affected by column selectivities when cpu_costing is active.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Thomas Fricker wrote in message ...


>Oops, opposite around: Here once again.

>

>I've got two instances of oracle 9.2.0.1, one on W2K and one on NT4.

>The data were imported from the same dump in the same way.

>A Table X has columns Y and Z, both of Varchar2(240).

>If I do the following:

>

>SELECT * FROM  X WHERE Y = 1989 and Z = 'J'

>on NT4 I get:ORA-01722: invalid number

>and on W2K I get the rows.!!!!!

>

>SELECT * FROM  X WHERE Y = 1989

>I get: ORA-01722: invalid number on both instances.

>

>SELECT * FROM  X WHERE Z = is not null and 1989 between Y and Z

>I get the rows on both Instances.!!!!!

>

>Why do I get rows on NT4 in the first example?

>Ther first example was also working on previous Oracle Versions (8.1.6,

>8.1.7) on NT4.

>

>I know, I have to use TO_CHAR() in the future, but now I have to rewrite

100


>procedures.

>

>

>