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 := 32767; 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;