Re: Refcursor intermittently returns no rows when it should

Thanks again for reverting. First thing I checked was "when others then" but the team is returning error message and error code even in that scenario.

We are planning to modify the stored procedure to have logging, but this issue is only in Live. To get all the approvals and testing will take time, till such time I was thinking if there are any alternatives to enable some tracing and see if Database is behaving fine and problem is with client side.

Regards,
Vijay Sehgal.

On Thu, Sep 17, 2015 at 8:29 PM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:

> Just checking but you're not doing something along the lines of
>
> begin
> ...
> exception
> when no_data_found then ....
> when dup_val_on_index then ....
> *when others then null;*
> end;
>
>
> Another thing you could do - particularly if the procedure is complex - is
> to scatter several calls to a logger procedure in between the code. It
> would look something like this:
>
> create or replace procedure...
> as
> procedure log_msg (msg in varchar2)
> is
> pragma autonomous_transaction;
> begin
> insert into my_log_table (dt, text) values (sysdate, msg);
> commit;
> end;
> begin
> log_msg('Start procedure');
> ...
> log_msg('Step 1');
> ...
> log_msg('End procedure');
> end;
> /
>
> This then gives you the ability to investigate when a user reports that
> the procedure hasn't returned any data for him, based on the timestamp of
> when it occurred.
>
> Stefan
>
>
> On Thu, Sep 17, 2015 at 9:22 PM, vijay sehgal <vijaysehgal21_at_gmail.com>
> wrote:
>
>> William,
>>
>> Thank you for taking out time and looking into this. You have all the
>> rights to be suspicious, after I have not shared the code and unfortunately
>> it is not possible for me to share the code.
>>
>> The procedure has out parameters which are populated with error message
>> and code if there's any exception raised. The application ( Java ) logs
>> these into application log file. Whenever these problems are encountered we
>> do not see any error message logged.
>>
>> Regards,
>> Vijay Sehgal.
>> On 17 Sep 2015 18:59, "William Robertson" <william_at_williamrobertson.net>
>> wrote:
>>
>>> Forgive me for being suspicious, but is the exception handler getting
>>> called? Does it log anything? How does it return the error message? What
>>> does the client app do with it?
>>>
>>> I've seen a lot of exception handlers that looked as though they should
>>> have worked but actually didn't.
>>>
>>> William Robertson
>>>
>>> Sent from my iPhone
>>>
>>> On 16 Sep 2015, at 15:36, vijay sehgal <vijaysehgal21_at_gmail.com> wrote:
>>>
>>> Stefan,
>>>
>>> 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