output_clob DBMS_OUPUT buffer issue

[xendren]

When a CLOB parameter is greater than 32767, the procedure correctly chunks it up for the DBMS_PUT command, but it generates an error in the next iteration of the loop for going over the 32767 limit due to the DBMS_OUTPUT buffer not flushing. The DBMS_OUTPUT.new_line needs to be inside the loop to avoid generating the Oracle exception.

[osalvador]

Hi @xendren

Could you enumerate the steps to reproduce the issue and the sample code?

Thanks.

[MikeKutz]

Verified Bug Code

DECLARE
  P_CLOB CLOB;
    
BEGIN
  P_CLOB := lpad( 'X', 32767, 'X') || 'X';
--  P_CLOB := P_CLOB || P_CLOB ;

  TEPLSQL.output_clob (  P_CLOB => P_CLOB) ;  
END;

Code to correct

I believe the idea of using PUT instead of PUT_LINE was to ensure that you don't have a \n in the middle of a SELECT keyword. Moving the NEW_LINE call to the inside of the loop would undo that design philosophy.

The split will need to occur on the last \n prior to the 32767 barrier. PUT_LINE should be used instead.

Oh, that ENABLE( 1000000 ) should probably be changed to ENABLE( NULL ).

workarounds: (until fixed)

• Save the CLOB to a table and then run a SELECT statement.
• Use the "oddgen" plugin (another GitHub project) for SQL*Developer so that the output CLOB goes to a new worksheet.

[MikeKutz]

@xendren
Any time you report a bug, please include any ORA numbers and a simple example to reproduce.

When you can, please try this loop within the output_clob body. Make sure you remove the DBMS_OUTPUT.NEW_LINE call that's at the end.

loop
    exit when v_offset > dbms_lob.getlength(p_clob);

    -- find "near last" occurance of '\n' - assumes Code Lines are not > 767 bytes long.
    v_chunk_size := dbms_lob.instr(p_clob,chr(10),v_offset + 32000,1);

    -- calculate "correct" chunk size.
    -- a NULL or 0 value is "invalid" - use the entire length or Max size.
    v_chunk_size := least(nvl(nullif(v_chunk_size,0),99999),dbms_lob.getlength(p_clob),32767);

    dbms_output.put_line(dbms_lob.substr(p_clob,v_chunk_size,v_offset));
    v_offset := v_offset + v_chunk_size;
end loop;