Thanks for the question, Nagendra.

Asked: December 27, 2004 - 10:11 pm UTC

Last updated: June 24, 2005 - 6:20 pm UTC

Version: 9

Viewed 10K+ times! This question is

You Asked

declare

fileHandler UTL_FILE.FILE_TYPE;
l_str VARCHAR2(1000);
err_msg VARCHAR2(100);

BEGIN
fileHandler := UTL_FILE.FOPEN('d:\mydir', 'data.txt', 'r',32767);
LOOP
begin
utl_file.get_line(filehandler,l_str);
dbms_output.put_line('Data of the file is :'||l_str);
exception
when no_data_found then
exit;
end;
END LOOP;
utl_file.fclose(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
DBMS_OUTPUT.PUT_LINE('Error: invalid path ' || SQLERRM);
WHEN others then
DBMS_output.put_line('other error : '||SQLERRM); UTL_FILE.FCLOSE(fileHandler);
END;

The above code is working fine if the content of text file (data.txt) is less, but i am getting problem when the content of the text file is more than 32767 characters i.e. while i would like to display content using DBMS_OUTPUT.PUT_LINE, the error which it displays is

" ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139 "

Can u give me a solution for the above.

Thanks in advance.

and Tom said...

dbms_output will have two limits for you

a) max output linesize of 255 bytes.
b) max output buffer size of 1,000,000 bytes (actually, somewhat less due to the packing overhead they have)

you can

SQL> set serveroutput on size 1000000

but you'll have those limits regardless. Maybe you wanted to use an external table instead -- if you are just trying to get it line by line by line.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not

A reader, December 28, 2004 - 10:15 am UTC

Hello Sir,
Just a small suggestion/request for Oracle. Why do they not enhance dbms_output to overcome these limitations ?

Since I was born I am hearing about these limitations and now its Oracle version 10g and in the course of time Oracle has improvised/enhanced on many of its existing packages so why the neglect for dbms_output ?
Any technical reasons for not to ?
Any sugggestions ?

December 28, 2004 - 10:57 am UTC

10gr2

regarding "Buffer overflow, limit of 20000 bytes"

Sushil, January 06, 2005 - 12:10 am UTC

well, Thanks for the solution provided.
It works well.That was agood solution indeed.

I just came to know about ASKTOM.
I have some more question to pose how shall i go about it..

January 06, 2005 - 10:30 am UTC

when the home page says "click here to ask a question..."

So what's going to happen to DBMS_OUTPUT in 10GR2

Gary, January 06, 2005 - 4:50 pm UTC

Okay, so no-one else jumped.
Can you give us an idea of what the changes/replacement to DBMS_OUTPUT is in 10gr2

I use DBMS_OUTPUT for debug messages within my code (with wrappers to cope with buffer overflows, long lines etc).

There are things I would like to see are :
An automatic 'ageing' out of the oldest data in the buffer rather than failing on an overflow exception
A way to see how many bytes are in the buffer (so you can 'GET_LINES' to free up space prior to a PUT_LINE)
Multiple output buffers, or some form of 'Labelling' so I can just PUT_LINE and GET_LINES specific to a procedure without affecting entries from other triggers/called functions etc.
Exposure of the entire output array so it can be manipulated by PL/SQL.

Knowing what will be on offer means that any wrappers I write can anticipate it (eg passing extra parameters which are unused until 10gr2)

January 06, 2005 - 7:20 pm UTC

dbms_output is to be as large and as wide as you wide in the future.....

sort of short circuts your needs, leaves it as a black box without the 1,000,000 byte by 255 wide limit.

A response to the follow-up questions would be so nice . . .

Gary Kohls, January 07, 2005 - 12:02 am UTC

I've often wondered why DBMS_OUTPUT was neglected as well. As an example, I'd like to be able to lock the buffer size at 1000000 bytes (or a future, larger maximum if that transpires). I must interact with wrapped packages that will (unbeknowst to me until I run into an early overflow), resize the buffer down to a smaller size. I believe I read once in an AskTom article that the buffer size can't be decreased but that isn't true.

January 07, 2005 - 9:08 am UTC

what followup questions?

I saw one question:

Can you give us an idea of what the changes/replacement to DBMS_OUTPUT is in 
10gr2  

and I answered it:

dbms_output is to be as large and as wide as you wide in the future.....



meaning -- there are to be no restrictions on the size/shape of the buffer into the future with 10gr2.


If you believe you read something incorrect on the site -- please don't say things like "I believe I read once..." -- find it, and we'll fix it.

Otherwise you are just starting mythology.  I don't believe I've ever said anything like that.  I've described precisely how dbms_output works (in Expert one on one Oracle down to the mechanism for packing data in there) but I don't think I've ever set you cannot decrease the buffer size. If I did, please point it out.



ops$tkyte@ORA9IR2> set serveroutput on size 1
SP2-0547: size option 1 out of range (2000 through 1000000)
ops$tkyte@ORA9IR2>

the response about asking a question

A reader, January 07, 2005 - 8:24 am UTC

That response was funny, in a sad sort of way. I do not remember seeing anything except "sorry, I have too many question already, try again later" in the 3 years that I have been looking at AskTom.

I understand that you are just one (or at least a small cadre).

January 07, 2005 - 9:30 am UTC

do you see the counts on the home page?

To the reader above

A reader, January 07, 2005 - 3:30 pm UTC

Hi,
Its strange that you have responded like this. But to be honest you get a chance to ask a question. I have had a few successful attempts. But like anything else you may need to make few attempts to post a question.

Thanks

Tom where is your example ...........

CG, June 24, 2005 - 9:49 am UTC

...... of using a piplined function to avoid the 1MB limit of dbms_output? I think it used a temp clob as a buffer?

Thanks for your time.

June 24, 2005 - 6:20 pm UTC

no, it wouldn't need a clob, it would just pipe rows.

create or replace type myArray as table of varchar2(4000);

create or replace function your_function( ... ) return myArray
pipelined
as
begin
....
pipe row( 'whatever' );
....
return;
end;
/

select * from table( your_function() );

Breadcrumb

Question and Answer