Re: dbms_output max buffer limit 1000000 overflows

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Thu, 24 Apr 2003 18:31:38 -0800
Message-ID: <F001.0058917D.20030424183138@fatcity.com>

Hi Sam,

I had to deal with a similar problem recently. I did some playing around and here is the workaround I devised. This may not be the "most optimal" way to do it, but the benefit is that you DO NOT need to use UTL_FILE. You already have most of the infrastructure required to do the job.

So here is how it works:

1. I have a .sql script - Main_Run.sql that is run in SQL*Plus that calls another .sql script - LongSQL_Proc_Run.sql. This is just a personal preference for code maintainability purposes and is really not required. They can be potentially combined. Main_Run.sql captures all of the output in 1 spool file - millionplus.txt.

Main_Run.sql

---

set echo off
set feedback off
set pagesize 0
set termout off
set serveroutput on

spool millionplus.txt

@longsql_proc_run

spool off
set echo on
set feedback on
set pagesize 23
set serveroutput off
set termout on

2) As you can see above, Main_Run.sql calls LongSQL_Proc_Run.sql

LongSQL_Proc_Run.sql

---

declare
begin
longsql_proc1;
end;
/
declare
begin
longsql_proc2;
end;
/

3) As you can see above, LongSQL_Proc_Run.sql calls 2 procedures (longsql_proc1 and longsql_proc2). But it does it in 2 different "blocks of code". This is the secret sauce for working around the 1-million character limitation.

Here is the code for those 2 procedures. They are identical except for their names and a couple of output lines.

Procedure - longsql_proc1

---

create or replace procedure longsql_proc1 IS
text VARCHAR2(100)
:='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('longsql_proc1 start');  

  for i in 1..6000
  loop
    dbms_output.put_line(text);
  end loop;

  dbms_output.put_line('longsql_proc1 end'); end;
/

create or replace procedure longsql_proc2 IS
text VARCHAR2(100)
:='1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('longsql_proc2 start');  

  for i in 1..6000
  loop
    dbms_output.put_line(text);
  end loop;

  dbms_output.put_line('longsql_proc2 end'); end;
/

4) As you can see above, procedure longsql_proc1 has calls to dbms_output.enable(1000000) within its code. This 1 million characters is relevant to THIS block of PL/SQL code. It generates approximately 0.8 million characters here.

5) Similarly, procedure longsql_proc2 has calls to dbms_output.enable(1000000) within its code. This 1 million characters is relevant to THIS block of PL/SQL code. It generates approximately 0.8 million characters here.

6) Using the above method, I am thus able to generate approximately 1608704 characters by dbms_output, capture it in one spool file. I think this was enough for me to prove to myself that the 1-million character barrier was surpassable.

One of the limitations to this approach is that each block of code SHOULD NOT generate more than 1 million characters. If you can break up your procedures that way, it will work.

Hope this helps and is what you are looking for.

Cheers,

Gaja

• sam d <sam_orafan_at_yahoo.com> wrote:
  > Hi people,
  > We have a .sql which runs in sqlplus.
  > This .sql calls the StoreProc.
  > These storeprocs have dbms_output.put_line
  > statement which are logged using spool command in
  > sqlplus.
  >
  > Now the problem is even after setting max limit
  > for dbms_output.enable(1000000) , there is a
  > error "ORA-20000: ORU-10027: buffer overflow, limit
  > of
  > 1000000 bytes" .
  >
  > any workaround to this.
  > we want to log all the dbms_output.put_line
  > messages.
  >
  > Thx
  > Sam
  >
  >
  

  ---

---

Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo http://search.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 24 2003 - 21:31:38 CDT