After an inspiring DOAG 2014 I was thinking about some performance improvements for my APEX IR XLSX Downloader.
The actual size of all the single files that need to be build isn't known beforehand but you have to plan for more than the maximum size of a VARCHAR2 variable so you need a CLOB.
However building a CLOB can be really slow if you treat it like a VARCHAR2 and just concat values together.
In addition if you are doing that multiple times you're actually asking for some performance trouble.
I remembered reading a
blog post by Carsten Czarski a while ago and got the inspiration from there.
He also reviewed my initial code and helped me make it better.
Maybe you have similar tasks at hand and can benefit from the code.
How it works and how to use is enclosed in the code comments.
Update: Thanks to the comment by SSentinel below I could clean up the code.
/**
* Procedure concatenates a VARCHAR2 to a CLOB.
* It uses another VARCHAR2 as a buffer until it reaches 32767 characters.
* Then it flushes the current buffer to the CLOB and resets the buffer using
* the actual VARCHAR2 to add.
* Your final call needs to be done setting p_eof to TRUE in order to
* flush everything to the CLOB.
*
* @param p_clob The CLOB buffer.
* @param p_vc_buffer The intermediate VARCHAR2 buffer. (must be VARCHAR2(32767))
* @param p_vc_addition The VARCHAR2 value you want to append.
* @param p_eof Indicates if complete buffer should be flushed to CLOB.
*/
PROCEDURE clob_vc_concat( p_clob IN OUT NOCOPY CLOB
, p_vc_buffer IN OUT NOCOPY VARCHAR2
, p_vc_addition IN VARCHAR2
, p_eof IN BOOLEAN DEFAULT FALSE
)
AS
BEGIN
-- Standard Flow
IF NVL(LENGTHB(p_vc_buffer), 0) + NVL(LENGTHB(p_vc_addition), 0) < 32767 THEN
p_vc_buffer := p_vc_buffer || p_vc_addition;
ELSE
IF p_clob IS NULL THEN
dbms_lob.createtemporary(p_clob, TRUE);
END IF;
dbms_lob.writeappend(p_clob, length(p_vc_buffer), p_vc_buffer);
p_vc_buffer := p_vc_addition;
END IF;
-- Full Flush requested
IF p_eof THEN
IF p_clob IS NULL THEN
p_clob := p_vc_buffer;
ELSE
dbms_lob.writeappend(p_clob, length(p_vc_buffer), p_vc_buffer);
END IF;
p_vc_buffer := NULL;
END IF;
END clob_vc_concat;
