Split CLOB into lines

Simple requirement – I’ve got a CLOB (e.g. after exporting an application from Apex from the command line) that I want to examine, and I’m running my script on my local client so I can’t use UTL_FILE to write it to a file. I just want to spit it out to DBMS_OUTPUT.

Strangely enough I couldn’t find a suitable working example on the web for how to do this, so wrote my own version. This was my first version – it’s verrrrrry slow because it calls DBMS_LOB for each individual line, regardless of how short the lines are. It was taking about a minute to dump a 3MB CLOB.

PROCEDURE dump_clob (clob IN OUT NOCOPY CLOB) IS
  offset NUMBER := 1;
  amount NUMBER;
  len    NUMBER := DBMS_LOB.getLength(clob);
  buf    VARCHAR2(32767);
BEGIN
  WHILE offset < len LOOP
    -- this is slowwwwww...
    amount := LEAST(DBMS_LOB.instr(clob, chr(10), offset)
                    - offset, 32767);
    IF amount < 0 THEN
      -- this is slow...
      DBMS_LOB.read(clob, amount, offset, buf);
      offset := offset + amount + 1;
    ELSE
      buf := NULL;
      offset := offset + 1;
    END IF;
    DBMS_OUTPUT.put_line(buf);
  END LOOP;
END dump_clob;

This is my final version, which is orders of magnitude faster – about 5 seconds for the same 3MB CLOB:

PROCEDURE dump_str (buf IN VARCHAR2) IS
  arr APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  arr := APEX_UTIL.string_to_table(buf, CHR(10));
  FOR i IN 1..arr.COUNT LOOP
    IF i < arr.COUNT THEN
      DBMS_OUTPUT.put_line(arr(i));
    ELSE
      DBMS_OUTPUT.put(arr(i));
    END IF;
  END LOOP;
END dump_str;

PROCEDURE dump_clob (clob IN OUT NOCOPY CLOB) IS
  offset NUMBER := 1;
  amount NUMBER := 8000;
  len    NUMBER := DBMS_LOB.getLength(clob);
  buf    VARCHAR2(32767);
BEGIN
  WHILE offset < len LOOP
    DBMS_LOB.read(clob, amount, offset, buf);
    offset := offset + amount;
    dump_str(buf);
  END LOOP;
  DBMS_OUTPUT.new_line;
END dump_clob;
Add a Dynamic Total to a Tabular Report
Show an animated “Please wait” indicator after page submit

Comments

  1. Hi Jeff,
    I think your code works well only if the clob is less then 32767
    otherwise there will be a truncated row in output
    Tino

    • Hi Tino. No, it works for large clobs as well. Doesn’t truncate anything – however, it will split long lines (>32k) across multiple lines – but this is a limitation of DBMS_OUTPUT anyway.

  2. Hello Jeff,
    The packaged app “APEX Application Archive” has code to print out an apex app line by line stored in a CLOB
    Rgds
    Koen

  3. João A. T. Marotti
    9 February 2021 - 4:18 am

    Jeff, thanks for your post, it helped me a lot

    I changed the code a little bit to insert the result into a table with a CLOB field, here is the result:

    — Table with clob field
    create table clob_table(
    ID NUMBER
    , PROCESS_ID NUMBER
    , LINE CLOB
    )

    PROCEDURE dump_str (buf IN VARCHAR2, process_id NUMBER, curr_clob IN OUT CLOB) IS
    arr APEX_APPLICATION_GLOBAL.VC_ARR2;
    v_tmp_clob CLOB;
    BEGIN
    arr := APEX_UTIL.string_to_table(buf, CHR(10));
    FOR i IN 1..arr.COUNT LOOP
    IF i < arr.COUNT THEN
    v_tmp_clob := arr(i);
    curr_clob := curr_clob || v_tmp_clob;
    INSERT INTO xxod.clob_table values (clob_table_s.nextval, process_id, curr_clob);
    curr_clob := '';
    ELSE
    v_tmp_clob := arr(i);
    curr_clob := curr_clob || v_tmp_clob;
    END IF;
    END LOOP;
    END dump_str;

    PROCEDURE dump_clob (p_clob IN OUT NOCOPY CLOB) IS
    offset NUMBER := 1;
    amount NUMBER := 30000;
    len NUMBER := DBMS_LOB.getLength(p_clob);
    buf VARCHAR2(30000 CHAR);
    curr_clob CLOB;
    process_id NUMBER := ROUND((sysdate – to_date('01/01/1970', 'DD/MM/YYYY')) * 24 * 60 * 60 * 1000);
    BEGIN
    curr_clob := '';

    WHILE offset 0 THEN
    INSERT INTO xxod.clob_table values (clob_table_s.nextval, process_id, curr_clob);
    END IF;
    END dump_clob;

    Worked very well

    ps 1: The changes does not split long lines (>32k)
    ps 2: I had to decrease the size of the amount to 30000 because I was facing some error of “string buffer too small” (lot of special characters in Portuguese) … I didn’t spent much time trying to solve this, probably there is a better aproach than change the amount to 30000…

    • Thanks João, that’s a good point. Reducing the amount of data loaded at a time should resolve the multibyte character problem, e.g.:

      amount NUMBER := 8000;

Leave a Reply

Your email address will not be published / Required fields are marked *