Re: Oracle 9i strange behavior (ORA-600)

Thanks for all that answered my post. I found the solution. I got this text from the web:

"A bug in Oracle9i is causing UNION ALL to fail when using bind variables. The Oracle bug number is 2105414. The Oracle TAR for this bug is 1877212.995. Oracle has stated that the bug has been scheduled to be fixed in the Oracle 9.2 release.

Oracle has suggested a work-around for the problem: setting the hidden initialization parameter _push_join_union_view to false. At Oracle 9i, the default for this parameter is true. It is suggested that this be set in the initialization file so that the change takes effect for the entire database:

_push_join_union_view=false

After making this change to the initialization file, you will have to restart Oracle in order for the change to take effect.

(...)"

Again, thanks!!!

Zed.

"emerald" <emerald_at_net.hr> wrote in message news:<actsu2$13s$1_at_sunce.iskon.hr>...
> I remember having simmilar thing, but on 8i (8.1.6). On TAR i was told that
> the problem was that I had union query with order by at the end. And that
> the bug was fixed in 8.1.7. Is it possible that it appeared in 9i again?
> Anyway, TAR will tell you to upgrade to 9.0.2. They don't like to mess with
> ORA-600, unless it is on the latest release.
>
> TECNISA Development Team <desenvolvimento_at_tecnisainfor.com.br> wrote in
> message news:344fb668.0205270550.19150f48_at_posting.google.com...
> > Hi everyone!
> >
> > I'm facing a strange behavior on Oracle 9i. We have a SELECT statement
> > with subqueries, that when we ran it against the Oracle Database from
> > one of our clients we got an ORA-00600 error!
> >
> > I thought it could be some problem on the database, server, OS, but
> > another client got the same error on the same statement.
> >
> > We have an Oracle 9.0.1.0.0 running in a Linux box, our clients have
> > 9.0.1.0.2. We cant reproduce the error on our server.
> >
> > The DBA from the first client said that the problems was on the UNION
> > ALL that exists on the subquery. When I take it off the problem stops,
> > but my performance goes down!!! He suggested including a ROWNUM >
> > 1000000000000000 on the statement to fix the error. But I didn't like
> > this solution.
> >
> > This is an example of the SELECT statement:
> >
> > SELECT FIELD, FIELD, FIELD,
> > FROM
> > TABLE1, TABLE2,
> > (SELECT FIELD, FIELD
> > FROM TABLE3
> >
> > UNION ALL
> >
> > SELECT FIELD, FIELD
> > FROM TABLE4
> > WHERE NOT EXISTS (SELECT FIELD, FIELD
> > FROM TABLE5)
> >
> > UNION ALL
> >
> > SELECT FIELD, FIELD
> > FROM TABLE6
> > WHERE NOT EXISTS (SELECT FIELD, FIELD
> > FROM TABLE7))
> > WHERE
> > CONDITIONS...
> >
> > Can anybody help me? Does anyone know why this is happening?
> >
> > Thanks a lot!!!!
> >
> > Zed.
Received on Tue Jun 04 2002 - 11:54:39 CDT