Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ORA 9i Bug?
"Noodles" <noodles_at_aol.com> wrote in message
news:20030614082254.21356.00000691_at_mb-m05.aol.com...
> 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?
No but we are, like the table definitions and the sql statement.
I'd also check that the schema that is executing the sql doesn't own an identically named, but differently defined object.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ******************************************Received on Sat Jun 14 2003 - 08:14:22 CDT