Re: Oracle 9i and "lenient" SQL parsing

Cristian Veronesi <c.veronesi_at_crpa.it> wrote in message news:<3C512901.35022228_at_crpa.it>...
> Hello.
> We are trying to run a third-party Windows app written for Oracle 8i
> against a 9.0.1.2 database running on SuSE SLES7.
> This apps tries to issue a SQL statement like that (notice the missing
> space between "1" and "and"):
>
> select * from tipi latte where tip lat cod=1and tip lat des like 'A%' ;
>
>
> I said to the third-party developers that this is an incorrect SQL
> statement but they says that against Oracle 8i it works.
> I don't have an Oracle 8i instance to try but I have a 7.3.4 one so I
> tried:
>
> SQL> select * from tipi latte where tip lat cod=1and tip lat des like
> 'A%';
>
> TIP LAT COD TIP LAT DES
> ----------- --------------------------------------------------
> 1 Alimentare
>
> It works!!! And here's the result against the 9i database instead:
>
> SQL> select * from tipi latte where tip lat cod=1and tip lat des like
> 'A%';
> select * from tipi latte where tip lat cod=1and tip lat des like 'A%'
> *
> ERROR at line 1:
> ORA-01722: invalid number
>
> Is there a way to tell Oracle 9i to be more lenient with this type of
> statements?
>
> I noticed that parameter "compatible" is set to 9.0.0. I tried to set it
> to 8.1.0 but the database gave me a couple of errors on startup (sorry,
> I did not captured the error messages).
>
> Any suggestion?
>
> Regards, Cristian

Why do the third party developers refuse to fix this obvious bug (because that is exactly what it is, it *is* incorrect syntax, that it works by accident is completely unimportant)?

You should have used alter database reset compatibility, but then again I still believe the third party developers shouldn't rely on a 'feature', which shouldn't have been there to begin with.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri Jan 25 2002 - 08:18:41 CST