Re: Refcursor intermittently returns no rows when it should

  • From: William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 17 Sep 2015 14:29:13 +0100

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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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.

Other related posts: