Refcursor intermittently returns no rows when it should

one of the project teams is facing an issue, refcursor being returned from database intermittently returns no rows when it should.

The error is not reproducible at will and happens infrequently, sometimes we do not hit the problem for months, but at times it happens 3-4 times a day and things are back to normal after these failures. When it starts to fail the team has to manually trigger the process to ensure it completes, the procedure is part of batch job which returns few rows on which another application acts.

The out variable sys_refcursor is having nocopy, this should not have been used and I have already requested the team to remove nocopy.

I have requested the team to enable trace only on the query using below method.

alter system set events

'

    sql_trace[SQL: &m_sql_id ]
    plan_stat=all_executions,
    wait=true,
    bind=true
'

I would like to hear if anyone has faced such an issue and is there any method to trace and resolve the issue. The team is still on Oracle Database release 11.1.0.7.0 - 64 bit on AIX.

Thanking you all for your time and help on this.

Regards,
Vijay Sehgal.

--
http://www.freelists.org/webpage/oracle-l