string buffer too small when calling DBMS_OUTPUT.get_line

[Worker]

I get this error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_OUTPUT", line 151
ORA-06512: at line 2

when I run the following in a test window:

begin
	dbms_output.put_line(rpad('x',4000,'x'));
	dbms_output.put_line(rpad('x',4001,'x'));
end;

Commenting out the second line makes the problem go away. It looks like when PL/SQL Developer fetches the output for the DBMS Output tab, it does so by calling DBMS_OUTPUT.get_line with a 4000-character buffer when really it should use a 32767-byte buffer according to the DBMS_OUTPUT package header comments for get_line:

-- Output parameters:
-- line
-- This line will hold the line - it may be up to 32767 bytes long.

 

[Marco Kalter]

That is indeed not right. We'll see if we can fix this.

 

[protos]

Hi.
Is this PL/SQL developer restriction for output string with length more than 4000 characters?
In next example I see only first 4001 characters in output:

BEGIN
dbms_output.put_line(LPAD(' ',4000,'1')||LPAD(' ',4000,'2'));
END;

 

[Marco Kalter]

Can you let me know your PL/SQL Developer version? You will need 9.0.2 or later to see output lines longer than 4000 characters.

 

[protos]

I have 9.0.6. But output truncated. And my DB use UNICODE, if it is important in this case.

 

[Worker]

I am unable to reproduce this. Using a test window on 9.0.6 I see all 8000 characters both on AL32UTF8 and WE8MSWIN1252 databases.

 

[protos]

Output truncating not reproduce, when I change nls_lang from "AMERICAN_AMERICA.CL8MSWIN1251" to "AMERICAN_AMERICA.AL32UTF8" on my PC. But it is not a solution, unfortunately.

 

[Marco Kalter]

We'll check it out.