Re: Refcursor intermittently returns no rows when it should

Thanks for your time, I have checked the code and the exception handler returns error message to calling environment, so it is not the case here.

Regards,
Vijay Sehgal
On 16 Sep 2015 20:03, "Stefan Knecht" <knecht.stefan_at_gmail.com> wrote:

> Is there perhaps an exception handler involved that suppresses an error
> raised when the procedure is called ?
>
> On Wed, Sep 16, 2015 at 8:48 PM, vijay sehgal <vijaysehgal21_at_gmail.com>
> wrote:
>
>> Dear Experts,
>>
>> Missed to mention the procedure is being called from Java.
>>
>> Please suggest ways to zero to down on what could be causing this issue.
>>
>> Thank you for your time and help on this.
>>
>> Regards,
>> Vijay Sehgal
>> On 16 Sep 2015 12:43, "vijay sehgal" <vijaysehgal21_at_gmail.com> wrote:
>>
>>>
>>> Dear Experts,
>>>
>>> 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