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;
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Tino
17 September 2014 - 10:55 pm
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
Jeffrey Kemp
17 September 2014 - 11:04 pm
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.
Koen Lostrie
29 September 2014 - 3:49 pm
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
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…
Jeffrey Kemp
9 February 2021 - 11:36 am
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;
Radek Hrib
12 October 2024 - 12:02 am
hmm, in my case also didn’t work. testing with long single-line CLOB I am getting ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line.
tried the original procedure from the article and got ORA-21560: argument 2 is null, invalid, or out of range
ended up with my routine
within the given chunk size it seeks for the last occurrence of CHR(10)
– if found it prints offset-to-last_chr10_position and moves offset after CHR(10)
– if not found it prints the chunk size long part starting from the offset and moves the offset to the start of another chunk
repeats until offset < clob_len