How to free temporary lob used in result set of SQL SELECT in Oracle A goal is to aggregate rows into very large strings.
I cannot use LISTAGG(XMLAGG,etc.) that support only up to 4000 bytes. 
I looked into using PIVOT XML option but we have a strict requirement on using memory(XML is very bloated).

I tried using COLLECT along with to_string function to return CLOB value

CREATE TYPE vc4000_tt AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION to_string (
  coll           IN vc4000_tt,
  delimiter      IN VARCHAR2 DEFAULT ',',
  enclosed_by    IN VARCHAR2 DEFAULT NULL
) 
RETURN CLOB
IS 
  lv_clob         CLOB;
  delimiter_len   CONSTANT PLS_INTEGER := length(delimiter);
  enclosed_by_len CONSTANT PLS_INTEGER := length(enclosed_by);
BEGIN

  -- Create CLOB
  dbms_lob.CREATETEMPORARY(
    lob_loc =&gt; lv_clob,
    cache   =&gt; true,
    dur     =&gt; DBMS_LOB.SESSION
  );

  -- Open it for writing
  DBMS_LOB.OPEN (
    lob_loc =&gt; lv_clob,
    open_mode =&gt; DBMS_LOB.LOB_READWRITE
  ); 

  -- Build CLOB from collections
  FOR i IN coll.FIRST..coll.LAST
  LOOP

    IF enclosed_by_len &gt; 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc =&gt; lv_clob,
        amount =&gt; enclosed_by_len,
        buffer =&gt; enclosed_by
      );
    END IF;

    DBMS_LOB.WRITEAPPEND (
      lob_loc =&gt; lv_clob,
      amount =&gt; length(coll(i)),
      buffer =&gt; coll(i)
    );

    IF enclosed_by_len &gt; 0
    THEN --write enclosed by
      DBMS_LOB.WRITEAPPEND (
        lob_loc =&gt; lv_clob,
        amount =&gt; enclosed_by_len,
        buffer =&gt; enclosed_by
      );
    END IF;

    IF delimiter_len &gt; 0
    THEN --write delimiter
      DBMS_LOB.WRITEAPPEND (
        lob_loc =&gt; lv_clob,
        amount =&gt; delimiter_len,
        buffer =&gt; delimiter
      );
    END IF;

  END LOOP;

  --Remove last delimiter
  IF delimiter_len &gt; 0
  THEN
    DBMS_LOB.TRIM (
      lob_loc =&gt; lv_clob,
      newlen =&gt; DBMS_LOB.GETLENGTH( lv_clob ) - delimiter_len
    );
  END IF;

  DBMS_LOB.CLOSE( lob_loc =&gt; lv_clob );

  RETURN lv_clob;

EXCEPTION
  WHEN OTHERS THEN
    dbms_lob.FREETEMPORARY(lv_clob);
    RAISE_APPLICATION_ERROR(-20900, sqlerrm||dbms_utility.format_error_backtrace );

END to_string;
/

select 
  object_type, 
  to_string(
    cast(collect(object_name) as vc4000_tt),
    ',',
    '"'
  ) 
from user_objects 
group by object_type;


When Java app executes the query through a connection pool, that is a session stays forever, or when I execute it in SQL*Plus the number of temporary lobs goes up and up. 

SELECT * FROM v$temporary_lobs;


I do not have any locators to use in dbms_lob.freetemporary call.
How can I free up these temporary lobs?

Is there an alternative way to return very large strings to a client app?
Before executing DBMS_LOB.CLOSE( lob_loc =&gt; lv_clob ); you need to execute dbms_lob.FREETEMPORARY(lv_clob); 

According to the Oracle documentation it is required to free lob before closing.