Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to cope with Oracle's bad mood

Re: How to cope with Oracle's bad mood

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Tue, 09 Dec 2003 00:09:55 GMT
Message-ID: <np8Bb.4765$kY4.1670@news01.roc.ny>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US