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

Answer 1

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

Answer 2

Do dbms_output.enable(1000000);

Then try...

Answer 3

Your issue sounds like it is related to the Character Encoding Scheme of the Oracle database you are executing your script against.

Oracle supports single-byte character encoding schemes, which means (1 byte) = (1 character), and multibyte character encoding schemes which means (x bytes) = (1 character).

The VARCHAR2 datatype semantics is defaulted to BYTES which means that if the default was not changed on the database then VARCHAR2(2009) means the this datatype holds 2009 BYTES.

VARCHAR2 in Oracle has a max limit of 4000 bytes which of course means that if your database is using a single-byte character encoding scheme 4000 bytes = 4000 characters. Since your code is only producing 2000 characters it is probably a safe assumption that your database is using a double-byte character encoding scheme.

You can read more about this at http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i1835. This link is for the Oracle 11.1 database but I don't think much has changed when it comes to datatypes.

I hope you find this information helpful.

Answer 4

Try setting the linesize in SqlPlus.

set linesize 4000

You may also want to set the pagesize to a lower value.

Answer 5

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.

Answer 6

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%5F01/appdev.102/b14258/d%5Foutput.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.