title: using sequence in bulk load using listargg
body: <p>Below is an Oracle select/insert for a bulk load from XML.  It turns out that the text in the XML is limited to 80 chars so a listargg is used to group all the text into one row.  The problem comes in using the sequence to generate the PK.</p>
  <pre><code>insert into rule_table (rule_nbr, rule_text, eff_start_dt, rule_id)
  select code as rule_nbr
        , listagg(text, chr(10)) within group (order by line) as rule_text
        , to_date( creationdate, 'yyyy-mm-dd') as eff_start_dt
        , rule_id_seq.nextval
  from xmltable('/table/row'
                passing
                xmltype(xml_data)
                columns
                    code varchar2(6) path './code',
                    line varchar2(5) path './line',
                    text varchar2(80) path './text',
                    creationdate varchar2(10) path './date'
           ) xmlt
  group by code, creationdate order by code;                 
  </code></pre>
  <p>Sample Input</p>
  <pre><code>&lt;table&gt;
    &lt;row&gt;
      &lt;code&gt;000110&lt;/code&gt;
      &lt;line&gt;00000&lt;/line&gt;
      &lt;text&gt;Amendment I                                                                     &lt;/text&gt;
      &lt;date&gt;2019-08-24&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000110&lt;/code&gt;
      &lt;line&gt;00001&lt;/line&gt;
      &lt;text&gt;   Congress shall make no law respecting an establishment of religion, or       &lt;/text&gt;
      &lt;date&gt;2019-08-24&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000110&lt;/code&gt;
      &lt;line&gt;00002&lt;/line&gt;
      &lt;text&gt;prohibiting the free exercise thereof; or abridging the freedom of speech, or of&lt;/text&gt;
      &lt;date&gt;2019-08-24&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000110&lt;/code&gt;
      &lt;line&gt;00003&lt;/line&gt;
      &lt;text&gt;the press; or the right of the people peaceably to assemble, and to petition    &lt;/text&gt;
      &lt;date&gt;2019-08-24&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000110&lt;/code&gt;
      &lt;line&gt;00004&lt;/line&gt;
      &lt;text&gt;the government for a redress of grievances.                                     &lt;/text&gt;
      &lt;date&gt;2019-08-24&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000112&lt;/code&gt;
      &lt;line&gt;00000&lt;/line&gt;
      &lt;text&gt;Amendment II                                                                    &lt;/text&gt;
      &lt;date&gt;1986-01-01&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000112&lt;/code&gt;
      &lt;line&gt;00001&lt;/line&gt;
      &lt;text&gt;A well-regulated militia, being necessary to the security of a free state, the  &lt;/text&gt;
      &lt;date&gt;1986-01-01&lt;/date&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;code&gt;000112&lt;/code&gt;
      &lt;line&gt;00002&lt;/line&gt;
      &lt;text&gt;right of the people to keep and bear arms, shall not be infringed.              &lt;/text&gt;
      &lt;date&gt;1986-01-01&lt;/date&gt;
    &lt;/row&gt;
  &lt;/table&gt;
  </code></pre>