Help - Search - Members - Calendar
Full Version: Help with a Workaround
Oracle DBA Forums > Oracle > Oracle Forum
D Liske
I could use some help to find a workaround for bug 6823287. The workaround given (set SESSION_CACHED_CURSORS = 0) does not seem to work. I can't seem to find the original cause of error (it occurs once a month in a system with 800 users) so I'm hoping maybe I can find a way to work around this bug once it occurs. Here is the code to create the bug (I am on 10.2.0.3).

CODE
create or replace package BUG is

    function GetCursor(GuidID IN varchar2)
    return varchar2;
    procedure CallFunction;
    procedure CauseError;

end BUG;

create or replace package body BUG is

  -- Function and procedure implementations
  function GetCursor(GuidID IN varchar2) return varchar2 is
    cursor TestCursor is
      select 0 as TestCursorValue
      from   dual where dummy = GuidID;

  begin
   for recTestCursor in TestCursor
    loop
      null;
    end loop;
    return 'Hello World';
  end GetCursor;

  procedure CallFunction is
    r varchar2(40);
  begin
      r := Bug.GetCursor('Test');
  end CallFunction;
  
  procedure CauseError is
    str varchar2(5000) := 'xy';
  begin
    str := rpad(str,4000,'xy');
    str := Bug.GetCursor(str||'Too_many_items');
    exception
      when others then
        null;
  end CauseError;
  
begin
null;
end BUG;


To Get the error,

CODE
begin
Bug.CauseError();
Bug.CallFunction();
Bug.CallFunction();
end;


Once the CauseError function has been called, all calls of CallFunction() will fail with an Ora-01001 error.

Any suggestions on how to avoid the error would be helpful. I'm not sure which error is triggering the problem in my system --- here I'm using an ORA-01460 to cause the error.

Thanks a lot for your help
Dean
burleson
Hi Dean,

>> I could use some help to find a workaround for bug 6823287.

Only Oracle Corporation knows the details. Did you open an SR?

Do you have the bug number, I can look it up.

You can always insist that Oracle write you a patch, that's what your support money is for!
D Liske
Hi Donald,

I did look at the bug, the workaround suggested was to close the session. I suppose rebooting the server on this error would work too as a workaround biggrin.gif There was also a suggestion to set SESSION_CACHED_CURSORS to 0 which didn't seem to work for me.

I have put an SR in, with my test code, but I was hoping maybe someone could suggest a temporary fix for me while I wait.

In my messing around I noticed something interesting. The error only occurs when the cursor error is handled in an exception. Unfortunately, the guy who designed this code had a few:

CODE
exception
when others then
null
end


Those make me a little nervous, just their existense seems to indicate there were some problems occurring in the past.

More investigation has revealed that the Error handling does not affect this as I orginally thought. The bug can be recreated regardless of how the errors are trapped (if at all)

Would Oracle seriously write me a patch for this error? Does that actually happen?

As always, thanks for your time.
Dean


QUOTE (burleson @ Jul 25 2008, 09:29 AM) *
Hi Dean,

>> I could use some help to find a workaround for bug 6823287.

Only Oracle Corporation knows the details. Did you open an SR?

Do you have the bug number, I can look it up.

You can always insist that Oracle write you a patch, that's what your support money is for!
SteveC
Oracle has been known to produce one-off patches. You can see them all over the place on MetaLink.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.