dbms_output.put_line() - output buffer size ignored?

[NK]

-- I am using PL/SQL Developer v7.1.5 against Oracle 10.2.0.4
-- dbms_output.put_line() is supposed to handle 32K
-- The second put_line() below fails, regardless of what I set the output buffer size to (in SQL Window).

declare
s varchar2(1001);
begin
s := rpad('x', 1001, 'x');
dbms_output.put_line(length(s));
dbms_output.put_line(s);
end;

 

[Marco van der Linden]

This has nothing to do with buffer size. The buffer size indicates how many characters can be 'stored' in memory. I assume you get the following error:

ORU-2000: line length overflow, limit of 255 chars per line

To the best of my knowledge this has _always_ been the limitation of dbms_output. You can only print a maximum of 255 characters per dbms_output.put_line command. Note: if you have issued dbms_output.put commands prior to dbms_output.put_line these characters will add up to the limit.

The following code section will complete successfully, however if you comment out the dbms_output.new_line command, the total string length exceeds 255 and the code will fail.

DECLARE
  --
  l_text VARCHAR2(2001) := rpad('x', 255, 'x');
  --
BEGIN
  --
  dbms_output.put('x');
  dbms_output.new_line;
  dbms_output.put_line(l_text);
  --
END;

 

[ScottMattes]

The line length restrictions, as well as total buffer size, depends on the release that you are on.
http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

 

[NK]

This has nothing to do with buffer size. The buffer size indicates how many characters can be 'stored' in memory. I assume you get the following error:

ORU-2000: line length overflow, limit of 255 chars per line

The error I receive is:

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

To the best of my knowledge this has _always_ been the limitation of dbms_output. You can only print a maximum of 255 characters per dbms_output.put_line command. Note: if you have issued dbms_output.put commands prior to dbms_output.put_line these characters will add up to the limit.

The following code section will complete successfully, however if you comment out the dbms_output.new_line command, the total string length exceeds 255 and the code will fail.

DECLARE
  --
  l_text VARCHAR2(2001) := rpad('x', 255, 'x');
  --
BEGIN
  --
  dbms_output.put('x');
  dbms_output.new_line;
  dbms_output.put_line(l_text);
  --
END;

I can even use 1000 instead of 255 with your example above, and it still works.
But using 1001 causes it to fail.

 

[NK]

The line length restrictions, as well as total buffer size, depends on the release that you are on.
http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

You are right.
I am using 10.2.0.4 and it should support 32K

I tried my sample code using 32762 instead of 1001, and it works using
Oracle's SQL Developer v1.5.3 under Linux, going against the same database instance.

 

[NK]

I wonder what is is about OCI in development tools...
Using a value of 5000 crashed Toad 9.0

Using 32K from SQLPlus works fine.

But SQL Developer (uses JDBC not OCI) can handle 32K.

I am trying to convert blobs (1K - 2K) to a hexadecimal string (2K - 4K), to be used in an INSERT statement.

 

[NK]

Workaround:

CREATE OR REPLACE PROCEDURE put_line(p_longLine IN VARCHAR2) IS

  -- Got most of this from a Google search
  -- Added maxLength to suit PL/SQL Developer 7.1.5 with Oracle 10.2.0.4
  --
  -- Now you can output a long string using this put_line,
  --   if DBMS_OUTPUT.PUT_LINE doesn't work e.g.
  --
  -- declare
  --   s varchar2(32767) := rpad('x', 32767, 'x');
  -- begin
  --   put_line(length(s));
  --   put_line(s);
  --   put_line(length(s));
  -- end;

  maxLength NUMBER := 1000;
BEGIN
  IF LENGTH(p_longLine) > maxLength THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR(p_longLine, 1, maxLength));
    put_line(SUBSTR(p_longLine, maxLength + 1, LENGTH(p_longLine)));
  ELSE
    DBMS_OUTPUT.PUT_LINE(p_longLine);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.DISABLE;
    DBMS_OUTPUT.ENABLE(1000000);
    put_line(SUBSTR(p_longLine, maxLength + 1, LENGTH(p_longLine)));
END put_line;

 

[Worker]

I wonder what is is about OCI in development tools...
Using a value of 5000 crashed Toad 9.0

According to this example of how a program gets data from DBMS_OUTPUT using OCI, the program needs to allocate a big enough buffer (32k) to get the results into. If it doesn't, a "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" seems likely.

Snooping my network traffic, it looks like PL/SQL Developer gets data from DBMS_OUTPUT in this same way (using sys.dbms_output.get_line with two binds). Maybe the buffer for :line is too small?