Thank you, thank you, thank you!

1. Hi Sean,

   Well done on winning.

   I was wondering if you could help me. I’m trying to write a PLSQL code for parsing a file. You responded saying you improved performance by parsing clob to 32K chunks per time. Long shot but I was wondering if you still had that code?

   https://christopherbeck.wordpress.com/2012/04/03/parsing-a-csv-file-in-plsql/

   Thanks in advance!

   1. Yep I still have it and still use it fairly regularly.

      It’s not quite the same thing as what’s in the link but hopefully you find something useful in it.

      https://seanstuber.wordpress.com/2015/10/28/splitting-a-clob-into-rows/

2. Here’s a little test case I ran

   declare
   l_clob clob;
   l_start_time number;
   begin
   for i in 1 .. 20000 loop
   l_clob := l_clob || ‘11111,22222,333333,444444,555555,’ ||
   ‘666666,777777,888888,999999,101010101010’ ||
   ‘aaaaa,bbbbb,ccccc,ddddd,eeeee,fffff,ggggg’ ||
   ‘hhhhh,iiiiiii,jj,kkkkkkkkkkkk,llllllllllll,m’ || chr(10);
   end loop;

   insert into etexts values (‘christopher beck’,l_clob);

   commit;
   end;

   Then using sqlplus

   SQL> set timing on
   SQL> set autotrace traceonly
   SQL> set arraysize 5000
   SQL> SELECT y.COLUMN_VALUE
   2 FROM etexts, TABLE(split_clob(text, CHR(10))) x, TABLE(str2tbl(x.COLUMN_VALUE)) y
   3 WHERE name = ‘christopher beck’;

   420000 rows selected.

   Elapsed: 00:00:01.17

   str2tbl is a function Tom Kyte published years ago. I’ve seen several variations but all are pretty similar. Here’s a link to one version: http://tkyte.blogspot.com/2010/04/something-new-i-learned-about-estimated.html

   The biggest time crunch is simply fetching 420K rows across the network, the parsing is subsecond.

Comments are closed.