Oracle PL/SQL: workarounds around DBMS output size limitation

November 23, 2023

This question follows from this, but I ran into another different issue.

I am generating a large text output using PL/SQL

set serveroutput on size 1000000; -- maximum limit
declare my_text clob;    
begin
 for c in ( select letter from dummy_table ) 
  loop
    my_text := my_text  || c.letter || chr(10);
  end loop;
  dbms_output.put_line(my_text);
end;

In the real code, I am of course not just concatenating single letters, the strings are longer and the concatenation has more elements and is longer overall. I have already increased the output buffer size to 1000000 (which is maximum), which helped, but as I expanded the concatenation further, is started failing again – but now with a different error message that has nothing to do with size of the output, but I know experimentally that it does, since if I reduce the number of lines it works.

Is it possible to save the clob into a temporary table? Is clob the right data type to use here?

>Solution :

This feels like an XY-problem and the solution is to not use DBMS_OUTPUT rather than trying to work around something you probably cannot change.

Create a table:

CREATE TABLE table_name (datetime DATE, value CLOB);

Then just INSERT into the table:

DECLARE
  my_text clob;    
BEGIN
  FOR c IN (SELECT letter FROM dummy_table)
  LOOP
    my_text := my_text  || c.letter || chr(10);
  END LOOP;
  INSERT INTO table_name (datetime, value) VALUES (SYSDATE, my_text);
END;
/

You could, equivalently, write the CLOB to a file.

Then use whatever client application (SQL Developer, Java, PHP, etc.) you are using to read the CLOB value from the table.

fiddle