Tuesday, November 25, 2014

Concatenating VARCHAR2 values into a CLOB

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;

2 comments:

  1. 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:

    -- 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;

    ReplyDelete
    Replies
    1. Hi,
      I 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

      Delete