Noodles wrote:
> OS: HP Unix 11
> Oracle 9.0.2
>
> I think I've found a SQL optimizer bug. We've opend a tar but I wanted to check
> with the experts here.
>
> The SQL is very simple with 8 columns linking 4 tables with about 4
> where-clause conditions. The first selected column is a varchar2(1) with
> unique values of 'Y','N', and null. The error is "invalid number" (I'm at
> home, so forgive that I don't have the exact ORA err number) and complains
> about the varchar2(1) column. It acts like there's a to_number function on
> that column.
>
> The table is selected via a public synonym and was dropped/recreated but no
> luck. We tried using the full explicit path to the table (
> schema.table.column) and it somehow works. Makes me think it's still related
> to the public synonym...
>
> Any change to the full SQL also makes the error not occur. So changing the
> SQL where clause somehow does not trip the error.
>
> The error was found in testing of our client-server app, but is reproducible
> even within SQL*Plus. We tried running a trace during SQL*Plus execution but
> the trace session crashed(?); so no trace file. We've used trace many times
> to diagnose problems so I think the DBAs have some experience in doing it.
>
> Am I missing something?
>
> Cliff
>
>
>
Do you have any implicit data type conversions within the WHERE clause?
WHERE MY_VAR = 1
when MY_VAR is actually a VARCHAR2 & the correct clause should be
WHERE MY_VAR = '1'
Received on Sat Jun 14 2003 - 08:43:27 CDT
