DSXchange

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

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.

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.

Thanks djbarham,

It worked for me, :D