Re: Refcursor intermittently returns no rows when it should
Date: Wed, 16 Sep 2015 21:33:29 +0700
Message-ID: <CAP50yQ9eAFqBm4E8yTX-D4fob6bdo7+u-sK_oOViqUQM16_L-A_at_mail.gmail.com>
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-lReceived on Wed Sep 16 2015 - 16:33:29 CEST