Re: Buffer Size and DBMS

From: Chris Eastwood <c.undieseastwood_at_gu.edu.au>
Date: 1998/11/16
Message-ID: <72npec$s25$1@kraken.itc.gu.edu.au>#1/1

HiYa

are you sure of this??

I just tried a small experiment with one of our tables here and it failed.

using the table

SQL_at_FIN_TEST>desc glf_ldg_acct_pbal;

 Name                            Null?    Type
 ------------------------------- -------- ----
 VERS                                     NUMBER(4)
 LDG_NAME                                 VARCHAR2(8)
 ACCNBRI                                  VARCHAR2(32)
 PERIOD                                   NUMBER(2)
 BAL_AMT1                                 NUMBER(14,2)
 BAL_AMT2                                 NUMBER(14,2)
 BAL_UNITS1                               NUMBER(18,6)
 BAL_UNITS2                               NUMBER(18,6)
 LAST_SEQ                                 NUMBER(9)
 LPDATEI                                  DATE
 LPTIMEI                                  NUMBER(6)

SQL_at_FIN_TEST>select count(*) from glf_ldg_acct_pbal; -- more >

COUNT(*)

302389

I used this code:

spool info.txt
declare

cursor maxed is
select LDG_NAME, ACCNBRI
from glf_ldg_acct_pbal;

procedure boing(info varchar2) is
begin
dbms_output.put_line('boing '||info);
end;

begin
dbms_output.enable(1000000);

for wot in maxed loop
dbms_output.put_line(wot.LDG_NAME||'and '||wot.ACCNBRI); commit;
end loop;
end;
/
~
"output.sql" line 18 of 21 --85%--

and got

User <OPS$CHRISE> - You are currently connected to FIN_TEST database

SQL_at_FIN_TEST>@output
declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes ORA-06512: at line 13

SQL_at_FIN_TEST> In article <72i8h1$bmh$1_at_news1.DInet.de>, "Klaus Klöser" <k.kloeser_at_ct.dinet.de> wrote:
>After every commit; the buffer is empty, thus giving you indefinite space
>!!!
>
>
>hope it helps
>
>Klaus Kloeser
>k.kloeser_at_consultingteam.de
>Jose Luis Perez schrieb in Nachricht <3645DD86.3FA44C08_at_mx2.redestb.es>...
>>
>>
>>Kevin Bass wrote:
>>
>>> The maximum buff size for DBMS_OUTPUT is 1,000,000. I would like to get
>>> around this maximum buff size without using UTL_FILE.
>>>
>>> (1) How can I determine and/or review the buff size of DBMS_OUTPUT before
it
>>> is reached?
>>>
>>> (2) Is there a function or tool that can display the buff size before the
>>> maximum is reached.
>>>
>>> (3) Are there tools created that will get around the maximum buff size of
>>> DBMS_OUPUT?
>>>
>>> Kevin
>>
>>Hello Kevin
>>
>>You can use set the buffer size to 1,000,000, default size is 2000 bytes
>>
>>If you do the following
>>
>>set serveroutput on size 1000000
>>@my_plsql_script
>>set serveroutput off
>>
>>you can see also the package spec of dbms_output that includes a litle
>>documentation
>>
>>set heading off
>>set linesize 132
>>select text
>>from all_source
>>where name='DBMS_OUTPUT'
>>
>>
>>Hope this can help you !!
>>
>>Kind Regards
>>Jose Luis Perez
>>perezjl_at_mx2.redestb.es
>>
>>
>>
>>
>
>

See Ya
(when bandwidth gets better ;-)

Chris Eastwood

Photographer, Programmer               email    ua.ude.ug.cti_at_doowtsae.c
Motorcyclist and dingbat               WWW   http://chrise.itc.gu.edu.au

please remove undies for reply

Ohhh ... you work all day, slave over a hot stove all night yet you *still* have time for sadomasochism ... how do you do it?

A little man hurts me.

<ding>

    Oh Mister Mean ... Oh Mister Mean
    15 Minutes with him and you'll be blue and green     Studded belts and leather whips
    Bondage trousers on his hips
    Wack and punish as you dust with Mister Mean   Ohhhh he whips me around the house in minutes ... lets get cracking ... Received on Mon Nov 16 1998 - 00:00:00 CST

Re: Buffer Size and DBMS_OUTPUT