Oracle-L: Re: dbms_output max buffer limit 1000000 overflows

From: sam d <sam_orafan_at_yahoo.com>
Date: Thu, 24 Apr 2003 21:51:40 -0800
Message-ID: <F001.00589202.20030424215140@fatcity.com>

Thanks Gaja,that was very meticulous.
And Thanks all of u.

As u mentioned "block of code SHOULD NOT generate more than 1 million characters." I had this limitation. One of my storeProc was returning more than 1 million characters (couldn't break it).So I had to do as suggested by AK.
i.e INSERT messages in table.

In the main.sql script I've
"select ERROR_TEXT from ERROR_LOG order by seq_no" This goes in the spool file.

Actually all this was part of ACCESS to ORACLE migration(I posted mail few weeks back sub:"Migrating from MS Access to oracle").

With given "time line" and kind of
weirdly INSERT conditions that we had ,
We ruled out the utl_file and had no idea about perl.

We took following approach.

1.Export table as a flat files from ACCESS.
2.Create same tables in ORACLE
3.Import data in these ORACLE table
4.Use PL/SQL to Migrate data to new ORACLE TABLE

structure.
5.Log all the error as a text file.

All this sounds like anything but migration :>. Ya I know , we have to takecare that we don't mess up the data at any abnormal condition, and clenup. It's under testing , Hope it goes fine.

Thx again all of u responded.
Sam
-----Original Message-----
[mailto:oraperfman_at_yahoo.com]
Sent: Friday, April 25, 2003 8:02 AM
To: Multiple recipients of list ORACLE-L overflows

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:

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).


__________________________________________________
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: sam d
  INET: sam_orafan_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 Fri Apr 25 2003 - 00:51:40 CDT