It was a very nice weekend (Saturday), was enjoying 'Bhel' with family and suddenly the phone rang, It was very hectic, to see office number on mobile screen that too on fine weekend.
He : There is a problem with UAT environment, functionality is not working properly.
Me : Are you able to analyze the the root cause ?
He : Not really, but there is one pl-sql query which was running fine in test environment and the same is not running UAT environment.
Me : There must be some mismatch in query, try copying test environment query in UAT environment.
After 10 minutes
Same number call again :(
He : Still it is not working. Might be you will have to take a look. (Ohh God.. one more Sunday in office.. )
Me : Okay..will take a look tomorrow.
next day morning..I come to office, open the query in UAT environment..oops it's not working..and giving Ora-00979-Not a GROUP BY expression.. but it was working while testing on Friday. I try to analyze it what could have changed why it is not working. I explore test environment query..it is working in test..and surprisingly there is no difference in two queries.
I keep thinking for a while..and take look at versions of Oracle.
For test it is 10.2.0.5 and for UAT it is 11.2.0.1 and unfortunately my team missed to tell me that they are shifting UAT environment to 11g.
When I searched web for this error, I could find that this is a bug with Oracle 11.2.0.1
Thanks !!! http://www.oracle-class.com/?p=1337
And this has been fixed in 11.2.0.2
The query is (sample)
select x.dept_id, x.dept_name, sum(x.emp_sal) from (
select e.dept_id, 'BOM' dept_name, e.emp_sal from emp e ) x
group by x.dept_id;
For 11g, you have to add e.dept_name group by expression.
Still you can fin the possible fixes for oracle here http://oracle-tns.com/how-to-fix-ora-979-not-a-group-by-expression-in-11-2-0-1-version/
No comments:
Post a Comment