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;
In the flush requested portion of your code why worry about l_vc_buffer_flushed? By the time you've gotten to that part of your code, p_vc_buffer contains everything you want to flush, so you can simplify it to:
ReplyDelete-- 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;
Hi,
DeleteI think you're correct.
Let me run some tests today and update the code.
That part is most probably a leftover from previous versions of the code.
Regards,
Moritz