oracle pl/sql bug: can't put_line more than 2000 characters

Question

Has anyone else noticed this phenomenon where dbms_output.put_line is unable to print more than 2000 characters at a time?

Script is:

set serveroutput on size 100000;

declare
    big_str varchar2(2009);
begin
    for i in 1..2009 loop
        big_str := big_str||'x';
    end loop;

    dbms_output.put_line(length(big_str));
    dbms_output.put_line(big_str);
end;

/

I copied and pasted the output into an editor (Notepad++) which told me there were only 2000 characters, not 2009 which is what I think should have been pasted. This also happens with a few of my test scripts - only 2000 characters get printed.

I have a workaround to print like this:

dbms_output.put_line(length(big_str));
dbms_output.put_line(substr(big_str,1,1999));
dbms_output.put_line(substr(big_str,2000));

This adds new lines to the output, makes it hard to read when the text you're working with is preformatted.

Has anyone else noticed this? Is it really a bug or some sort of obscure feature? Is there a better workaround? Is there any other information on this out there?

Oracle version is: 10.2.0.3.0, using PL/SQL Developer (from Allround Automation).

Solution 1

This not a limitation to Oracle or put_line, this is a limitation with your IDE.

From the Oracle documentation : http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm#ARPLS036

Rules and Limits

• The maximum line size is 32767 bytes.
• The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

You can test this using SQL*Plus.

The fact you are seeing 2000 leads me to believe there's a setting in your IDE that's setting it to the minimum.

Solution 2

I don't know any better workaround than splitting it like that. To avoid the new line make every call except the last one dbms_output.put instead of dbms_output.put_line

Solution 3

According to TFM, the maximum line size should be 32767. I propose you use DBMS_OUTPUT.GET_LINE to read the long line; if is truncated, it's a bug in DBMS_OUTPUT. If not, it's a bug in PL/SQL Developer.