title: split CLOB field into table like structure
body: <p>I have field in a Oracle database of type CLOB. I would like to split this field in several columns and rows. Here's an example of the content:</p>
  
  <pre><code>4:true5:false24:&lt;p&gt;option sample 1.&lt;/p&gt;4:true22:&lt;p&gt;option sample 2.&lt;/p&gt;5:false23:&lt;p&gt;option sample 3.&lt;/p&gt;5:false22:&lt;p&gt;option sample 4.&lt;/p&gt;5:false 
  </code></pre>
  
  <p>The result should look like this:</p>
  
  <pre>ID LEVEL ANSWER_OPTION VALUE
  1   3   option sample 3 false
  1   4   option sample 4 false
  2   3   option sample 3 false
  4   3   option sample 3 true
  3   2   option sample 2 false
  1   2   option sample 2 true
  2   1   option sample 1 true
  2   4   option sample 4 false
  4   1   option sample 1 false
  2   2   option sample 2 false
  4   2   option sample 2 false
  1   1   option sample 1 false
  3   4   option sample 4 false
  4   4   option sample 4 false
  3   3   option sample 3 false
  3   1   option sample 1 true</pre>
  
  <p>We have made the following statement which created the result above. </p>
  
  <pre><code>with guest_string as 
  
  (    select qsn.id                                id 
  
       ,      dbms_lob.substr( qsn.guest, 2000, 1 ) answer_options
  
       from   mneme_question qsn
  
       where qsn.id &lt; 10 
  
  )
  
  select distinct id
  ,      level
  ,      substr(regexp_substr( answer_options'&lt;p&gt;[^&lt;]+', 1, level, 'i'), 4) ANSWER_OPTION
  ,      substr(regexp_substr( answer_options, '(true|false)', regexp_instr( answer_options, '&lt;/p&gt;', 1,  1), level, 'i'), 1) VALUE
  from   guest_string
  connect by regexp_substr( answer_options, '&lt;p&gt;[^&lt;]+', 1, level, 'i') is not null
  </code></pre>
  
  <p>The problem with this code is it takes way to long to split all records we have. We had to cut it off at 10 rows (5 row take 0.25 sec, 10 takes 16 seconds, 15 rows takes about over 2,5 minutes). We currently have 30000 rows and they will grow. At the moment we cannot change the software to change the datamodel, so we will have to do this ad hoc. </p>
  
  <p>Our current approach is to create a procedure that will be called for each record, but it would be better to have a faster parsing. Does anybody have a suggestion how to create a script that can do this in a reasonable time. We can do this at night time, but preferably it shouldn't take longer than 2 minutes. </p>
  
  <p>BTW in the future we could build some sort of mechanism to determine which records have already been parsed, but that also requires some form of detecting already parsed fields that have changed in the mean time. We don't have time to do that yet, so for now we need crude parsing as fast as possible.</p>
  
  <p>Thanks</p>