Hi All,
We have the server job which used to check the saletodateagainstdmsale tables. We usually ran the query with parallel hint SELECT /*+ parallel (SALETODATE,8 ) */ (...). The job ran quite good for couple of months but now the job was aborted by below warnings and error
ORA-24347: Warning of a NULL column in an aggregate function
ORA-12842: Cursor invalidated during parallel execution
When we forced the job after sometime it completed with the warning of a NULL column. Need your approach in order to resolve this issue.
Thanks,
Satheesh
ORA-12842: Cursor invalidated during parallel execution
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Stop trying to aggregate (group by) a column that might contain null. You've been lucky for two months, now your luck's turned bad - there's now a null in one of the grouping or aggregating columns in the data.
Last edited by ray.wurlod on Fri Oct 19, 2012 6:43 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I have repeatedly run into this error and for me it is not because of a null in a column that I am grouping on. It is a null in a column that is the subject of an aggregate function, such as MAX.
Normally, aggregate functions like MAX ignore nulls. However, when you use KEEP FIRST, it is different.
eg
max(my_column) keep (dense_rank first order by .......)
if my_column returns a null, I get the ORA-12842 error. If I use the following, I don't get the error:
max(nvl(my_column,' ') keep (dense_rank first order by .......)
However, the null is meaningful.
The error is also related to how the Oracle Connector operates. If I run the same query in Oracle SQL Developer, it does not return the error.
Admitted, it is just a warning, but I'd really prefer jobs not to routinely return warnings if they are operating as intended.
Normally, aggregate functions like MAX ignore nulls. However, when you use KEEP FIRST, it is different.
eg
max(my_column) keep (dense_rank first order by .......)
if my_column returns a null, I get the ORA-12842 error. If I use the following, I don't get the error:
max(nvl(my_column,' ') keep (dense_rank first order by .......)
However, the null is meaningful.
The error is also related to how the Oracle Connector operates. If I run the same query in Oracle SQL Developer, it does not return the error.
Admitted, it is just a warning, but I'd really prefer jobs not to routinely return warnings if they are operating as intended.