title: repeatable blocks with range in oracle
body: <p>Please find the below table. The entire data is within DOCSTART and DOCEND. The data is further enclosed within BACCSTART and BACCEND. This type of block is repeatable. I have to pick up any of ABCD which is also repeatable and TOTAL(occurring once per block) and ACCNAME (occurring once per block) for each block within BACCSTART and BACCEND and form an xml like </p>
  
  <pre><code>&lt;BACCSTART&gt;
  &lt;TOTAL&gt;100&lt;/TOTAL&gt;
  &lt;ABCD&gt;abcd&lt;/ABCD&gt;
  &lt;ACCNAME&gt;name&lt;/ACCNAME&gt;
  &lt;/BACCSTART&gt;
  </code></pre>
  
  <p>for each such block. Presently I am using a for loop, but the performance is not up to the mark. It will have around 200 such blocks for which I have to form the xmls within 15 seconds. Presently the for loop is taking around 53 secs.</p>
  
  <pre><code>ROWNUM   NAME     VALUE
   1      DOCSTART  null
   2      BACCSTART null
   3      ABCD      abcd
   4      ABCD      abcd2
   5      PQRS      pqrs
   6      PQRS      pqrs2
   7      TOTAL     100
   8      ACCNAME   name
   9      BACCEND   null
   10     BACCSTART null
   11     ABCD      abcd
   12     ABCD      abcd2
   13     PQRS      pqrs3
   14     PQRS      pqrs4
   15     TOTAL     150
   16     ACCNAME   name
   17     BACCEND   null
   18     DOCEND    null
  </code></pre>
  
  <p>Please help me out with this. If this is possible with a time effective query.</p>
  
  <p>It is oracle 10g.</p>
  
  <p>I have just across a modification in the requirement. The value of ACCNAME is present in another cofig table of around 90 rows. From there I have to pick up three values say value1 for ACCNAME and corresponding value2 and value3 and populate as tags in the xml. If I join that table performance would impact. Please suggest. The config table is like</p>
  
  <pre><code>HEADER1      HEADER2        HEADER3        HEADER4 ........
  &lt;ACCNAME&gt;    value2          value3
  .............
  .............
  .............
  .............
  </code></pre>
  
  <p>The data which I presented above, I get it from a table which contains it as a long. After converting it into clob and using xmltable I am getting the above data. Will it be better to use a global temporary table to insert the data before proceeding. Like</p>
  
  <pre><code>insert  into data(row_num,name,value)
  select /*+ no_xml_query_rewrite , parallel(x,8) */ rownum rn, substr(extractvalue(x.column_value,'/e'),1,instr(extractvalue(x.column_value,'/e'),' ')-1) name,
   substr(extractvalue(x.column_value,'/e'),instr(extractvalue(x.column_value,'/e'),' ')+1)  value
   from dual,
  xmltable('e' passing xmltype('&lt;e&gt;&lt;e&gt;' || 
  replace(long2clob('select longdata from billedacc order by segment_number'), 
  '|'||chr(10), '&lt;/e&gt;&lt;e&gt;')    ||  '&lt;/e&gt;&lt;/e&gt;').extract('e/e')
                         ) x;
  </code></pre>
  
  <p>Also suggest if there is any better way to handle this.</p>