Re: 10g Rel 2 Problems, Possible DOS threat????

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 28 Sep 2006 09:36:42 -0700
Message-ID: <1159461402.248484@bubbleator.drizzle.com>

Bob Jones wrote:

> <Geek.Empire_at_gmail.com> wrote in message 
> news:1159379127.264537.44730_at_i3g2000cwc.googlegroups.com...

>> While upgrading a 9i database to 10g Rel 2 we came across some bad
>> code. When executed this code would cause that sql session to fully
>> consume one whole CPU and never return even if the user canceled the
>> session. I'm sure you can see where this would lead. We have been
>> able to recreate this with the following code:
>>
>> DECLARE
>> TYPE DOSTestTab IS TABLE OF dual.dummy%TYPE;
>> vDTab DOSTestTab;
>> CURSOR c_DOScursor IS
>> SELECT dummy BULK COLLECT INTO vDTab FROM dual;
>> BEGIN
>> OPEN c_DOScursor ;
>> FETCH c_DOScursor BULK COLLECT INTO vDTab;
>> CLOSE c_DOScursor ;
>> END;
>>
>> This, although very bad looking code, did work in 9.2.0.x.
>>

> 
> This code is not only bad but pointless. Why is there BULK COLLECT in the 
> DECLARE section? 

Thank you for pointing it out because I was so busy yesterday I just copied the example into a SQL> prompt and confirmed the observation. But if it is properly written it works perfectly.

DECLARE
  TYPE DOSTestTab IS TABLE OF dual.dummy%TYPE;   vDTab DOSTestTab;
  CURSOR c_DOScursor IS
  SELECT dummy FROM dual;
BEGIN
   OPEN c_DOScursor;
   FETCH c_DOScursor BULK COLLECT INTO vDTAB;    CLOSE c_DOScursor ;
END;
/

I should have paid more attention and caught that: Glad you did.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Thu Sep 28 2006 - 11:36:42 CDT