Re: Strange behaviour of Ora 9.2

On Tue, 24 Sep 2002 08:26:43 +0200, "Thomas Fricker" <thomas.fricker_at_bud.bl.ch> wrote:

>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.
>
>

From the Oracle8i SQL Reference 2-33:

Oracle recommends that you specify explicit conversions rather than rely on implicit or automatic conversions for these reasons: (...)
- Implicit conversion depends on the context in which it occurs and may not work the same way in every case. - Algorithms for implicit conversion are subject to change across softwarereleases and among Oracle products. (...)

This disclaimer is already present in Oracle 7 documentation.

Jaap. Received on Tue Sep 24 2002 - 13:54:02 CDT