When builing large strings with lots of concatenations in PL/SQL, custom approach like "use varchar2(32000) buffer to reduce the number of dbms_lob calls" makes sense even for Oracle 12.1.0.2.0.
The response time improvement is 4..300+ times :) depending on the DB version and API you use.
Note that simple v_clob || TO_CHAR (SYSTIMESTAMP) || ', '
==> v_clob || TO_CLOB(TO_CHAR (SYSTIMESTAMP) || ', ')
trick
(note the extra to_clob
) makes 24 times improvement for 11g. The trick is bad for 12c where results in 1.5x degradation.
The code was published 4 year ago as an answer to "How to Quickly Append VARCHAR2 to CLOB": http://www.talkapex.com/2009/06/how-to-quickly-append-varchar2-to-clob.html?showComment=1343235921606#c9077980873875311325
Gist formating makes the answer cleaner.
Updates:
18 July 2018: added dbms_lob.writeappend
as it is faster in both 11g and 12c. Note that app
results are not improved much since the number of dbms_lob
calls there is low.
20 March 2024: added Oracle 19.9
You should have included a DBMS_LOB.writeappend in addition to the DBMS_LOB.append. They are much faster (at least on 11.2) as the LOB.append does conversion of charbuffer to CLOB first (creating an additional temporary CLOB) and then copies this CLOB contents to the main CLOB which is much less efficent than instruct Oracle to directly copy content of char buffer to end of CLOB.