Re: Urgent !!!!!!!!!! Urgent

From: <brendan_o'brien_at_wrightexpress.com>
Date: 1998/04/27
Message-ID: <6i20u0$f10$1@nnrp1.dejanews.com>#1/1

Hi Syed.

Two questions (first one probably doesn't relate to your problem, but I'll ask it anyway!):

Are you doing a commit on every delete, a single mass commit after you close your cursor loop, or a commit based on an incremented counter (e.g. every 50 deletes)? The third method is best, but make sure you also include an additional un-conditional commit immediately after you close your cursor to take care of stragglers.
Are you sending output to some sort of log file? If so, and you are using DBMS_OUTPUT, I believe that you may be hitting the default buffer size for DBMS_OUTPUT, which buffers *all* ouput before actually doing a write and will die with a message similar to the one you mentioned as soon as you exceed that buffer. If so, add the following line to your procedure before doing any output:

DBMS_OUTPUT.ENABLE(100000); That's the max buffer size. When your procedure ends it will flush the buffer and write everything to your log. You'll then only run into problems when you exceed 100000 (as opposed to 2000) bytes, and there's no way around that (until, I understand, version 8 of Oracle and PL/SQL).

Hope this helps. File output is the only thing I can think of that may be being buffered...

-Brendan

In article <3541EB77.D24AAB7F_at_cyber.net.pk>, Syed Faisal Shah <faisal_at_cyber.net.pk> wrote:
>
> Help Needed
>
> We have prepared a PL/SQL procedure in which we call 1343 records in
> cursor. The data that comes into the cursor in is numeric and we are
> deleting records from another table on the basis of value fetched from
> cusor.
>
> After deleting 48 records, it gives the following error.
>
> ORA - 20000 ORU-10027 Buffer overflow, exceed 2000 bytes limit.
>
> Please reply if anyone could help.
>
> --
>

>
> Syed Faisal Shah
> Systems Engineer
> Cyber Internet Services (Pvt.) Limited
> A-904, Lakson Square Building No. 3,
> Sarwar Shaheed Road,
> Karachi - 74200
> Pakistan
> Phone...: +92 21 111 44 55 66
> Fax.....: +92 21 5686745
> E-Mail..: faisal_at_cyber.net.pk
> URL.....: http://www.cyber.net.pk
>

>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Apr 27 1998 - 00:00:00 CDT