Re: How to cope with Oracle's bad mood

Setting cursor_sharing=force could be the exact cause of this. Try turning it back to "exact" in a session (i.e. "alter session ...") and see if the query works again, in that session. If it does, this would be a good case in point that developers should always use bind variables and DBAs should NOT always call for cusrosr_sharing=force to the rescue.

Rick Denoire (100.17706_at_germanynet.de) wrote:
: 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
Received on Mon Dec 08 2003 - 18:19:31 CST