Getting ORA-10028 with DBMS_OUTPUT.PUT even with small chunk size?-CodePudding

I need to get a DBMS_OUTPUT that has an extensive length (60K characters). I understand the limit is 32767, but the issue for me with using the common LOOP solution...

LOOP
EXIT WHEN OUTPUT_OFFSET > DBMS_LOB.GETLENGTH(VAROUTT);
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(VAROUTT, OUTPUT_CHUNKS, OUTPUT_OFFSET));
    OUTPUT_OFFSET := OUTPUT_OFFSET   OUTPUT_CHUNKS;
END LOOP;

... is that my string already contains new lines based on the calculation that is being done within the procedure. With PUT_LINE I'm getting the following (yellow) due to the PUT_LINE size:

So I need to make use of PUT, to ensure it's my procedure the one that determines when a NEW_LINE is to be inserted. I tried the following:

LOOP
EXIT WHEN OUTPUT_OFFSET > DBMS_LOB.GETLENGTH(VAROUTT);
    DBMS_OUTPUT.PUT(DBMS_LOB.SUBSTR(VAROUTT, OUTPUT_CHUNKS, OUTPUT_OFFSET));
    OUTPUT_OFFSET := OUTPUT_OFFSET   OUTPUT_CHUNKS;
END LOOP;

The only difference is that I'm calling command "PUT" instead of "PUT_LINE", to prevent my output from performing irrelevant new-line calls. The issue is that I'm getting error:

ORU-10028: line length overflow, limit of 32767 bytes per line.

This happens only if I use PUT instead of PUT_LINE, even if my chunk size is 10. My initial setup is:

SET SERVEROUTPUT ON SIZE UNLIMITED;
SET LINESIZE 200;

Not sure if the LINESIZE setup is relevant, still 200 is bigger than 10. Each line doesn't surpass 50 characters.

CodePudding user response：

LINESIZE is a sqlplus parameter that wraps text in the display; it has no bearing on PL/SQL code.

By definition, DBMS_OUTPUT.PUT "places a partial line in the buffer." It doesn't matter how you set up your chunk size because you're just appending into the same 32K line limit. You must call DBMS_OUTPUT.NEW_LINE or DBMS_OUTPUT.PUT_LINE to introduce separate lines.

Note that DBMS_OUTPUT is generally used for debugging messages, and not large-scale text reporting.

CodePudding user response：

my string already contains new lines

You can split your string on those newline characters (assuming each of those lines is then less than 32k, which seems to be the case - you said they're less than 50 characters each); i.e. varying OUTPUT_CHUNKS each time, and reading each chunk (minus its own newline) into a string buffer that you then print with put_line(). Something like:

...
BUFFER varchar2(32767);
...
WHILE OUTPUT_OFFSET < DBMS_LOB.GETLENGTH(VAROUTT) LOOP
  -- read to next newline if there is one, rest of CLOB if not
  IF dbms_lob.instr(VAROUTT, chr(10), OUTPUT_OFFSET) > 0 then
    OUTPUT_CHUNKS := dbms_lob.instr(VAROUTT, chr(10), OUTPUT_OFFSET) - OUTPUT_OFFSET;
    dbms_lob.read(VAROUTT, OUTPUT_CHUNKS, OUTPUT_OFFSET, BUFFER);
    OUTPUT_OFFSET := OUTPUT_OFFSET   OUTPUT_CHUNKS   1; -- skip newline character
  ELSE
    OUTPUT_CHUNKS := 32767;
    dbms_lob.read(VAROUTT, OUTPUT_CHUNKS, OUTPUT_OFFSET, BUFFER);
    OUTPUT_OFFSET := OUTPUT_OFFSET   OUTPUT_CHUNKS;
  END IF;

  dbms_output.put_line(BUFFER);
END LOOP;

You could store DBMS_LOB.GETLENGTH(VAROUTT) in a variable to avoid recalculating that thousands of times.