How to cope with Oracle's bad mood

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 - 16:30:23 CST