ORA-12842: Cursor invalidated during parallel execution

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
satheesh_color
Participant
Posts: 182
Joined: Thu Jun 16, 2005 2:05 am

ORA-12842: Cursor invalidated during parallel execution

Post by satheesh_color »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
djbarham
Participant
Posts: 34
Joined: Wed May 07, 2003 4:39 pm
Location: Brisbane, Australia

Post by djbarham »

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.
akarsh
Participant
Posts: 51
Joined: Fri May 09, 2008 4:03 am
Location: Pune

Post by akarsh »

Thanks djbarham,

It worked for me, :D
Thanks,
Akarsh Kapoor
Post Reply