Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to cope with Oracle's bad mood
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:fss9tv4nkpkh44olc3o8uiauqq0cetgq8s_at_4ax.com...
> A developer came to me today to ask me what I changed in the DB
> because one of his queries (which runs fine otherwise) is being
> suddenly rejected by Oracle, giving "ORA-00979: Not a group by
> expression" error:
>
> select to_char(last_on, 'IW') week,
> round(avg(elapsed),2) mean
> from dims_overload.dr_cola
> where elapsed < 1000
> group by to_char(last_on, 'IW')
> order by to_char(last_on, 'IW');
>
> (last_on is a date). Executing the query on a second test DB works
> (containing similar data); removing the "order by" renders the query
> syntactically correct, from Oracle's point of view.
>
> This seems to be some kind of Oracle idiosyncracy, and yes, I did
> changed something in the DB, namely I set CURSOR_SHARING=force, but I
> can't imagine this would be the cause.
>
> In any case, sql_tracing is useless (since the query is not executed
> at all). How can I analyze what is happening in such a case? How can I
> dig into it? The problem could be too trivial in order to make a TAR
> out of it.
>
> Any clou?
>
> Rick Denoire
>
No idea. If I had to guess, I would say that turning cursor_sharing to force is rewriting
that query in a different way.
Actually turning sql_trace for this query should help. It might sow you how Oracle wrote the query
(or you can just look in v$sqlarea).
cursor_sharing parameter has some bugs in 8i. So you might be hitting one of them. Open a TAR.
Anurag Received on Mon Dec 08 2003 - 18:09:55 CST