Problem with DBMS_OUTPUT

[prasath n]

Hi PL \ SQL Developer Experts,
I work in PL/SQL developer version 7.1.5.1398 connecting to Oracle 10.2.0.1.0

The following snippet works fine

DECLARE
l_text VARCHAR2(4000) := rpad('1', 4000, '1');
BEGIN
dbms_output.put_line(l_text);
END;

But the following snippet fails

DECLARE
l_text VARCHAR2(4001) := rpad('1', 4001, '1');
BEGIN
dbms_output.put_line(l_text);
END;

with the error message
ora-06502: PL/SQL : numeric or value error:character string buffer too small
ora-06512: at "sys.dbms_output", line 148
ora-06512: at line 2

Though the PL-SQL character can support upto 32767 chanracters, why pl/sql developer is not supporting more than 4000, which is the maximum size of varchar in a table.

Is this a limitation of pl/sql developer tool or i am missing some options to be set? I have tried setting the buffer size to a big value (100000).

Regards,
Prasath N.

 

[Marco Kalter]

This is a limitation of the sys.dbms_output package.

 

[prasath n]

Hi Marco,
Thanks for your reply.

Still I have a clarification.

Starting with 10g R2, Oracle lifted these restrictions. Line limit is extended to 32767 bytes from 255 bytes. Also, by default, buffer limit is set to unlimited. When

 

[Marco Kalter]

I assumed the error occurred when calling dbms_output.put_line, but apparently this occurs when fetching a line. We'll fix it.