title: SYS_CONNECT_BY_PATH leads to "ORA-01489: result of string concatenation is too long"
body: <p>I'm using SYS_CONNECT_BY_PATH to do string aggregation. The general shape of the query is something like this:</p>
  
  <pre><code>select /*a bunch of fields unrelated to the problem*/,
  --Use SYS_CONNECT_BY_PATH to glue together all the chunks of XML. 
  --The XSL header and footer are prepended and appended here.
   , XMLType(to_clob('&lt;?xml version="1.0"?&gt;&lt;!-- begining of XSL file --&gt;,'&lt;!-- Next Section --&gt;'))||'&lt;/xsl:stylesheet&gt;')) AS XSL
  from (
    select /*a bunch of fields unrelated to the problem*/
      case when x = 1 then to_clob('
      /*a bunch of XSL*/
       &lt;xsl:text&gt;'||subq.new_c_value||'&lt;/xsl:text&gt;
      /*a whole bunch more xsl*/')
      else
       to_clob('/*a bunch of different XSL*/             
       &lt;xsl:text&gt;'||subq.new_f_value||'&lt;/xsl:text&gt;
      /*a whole bunch more xsl*/')
      end as xsl,
    --curr and prev are to help with using sys_connect_by_path do string aggregation.
      rownum AS curr,
      rownum -1 AS prev
    from (Select /* details of subq not relevant */ ) as subq
  )
  CONNECT BY prev = PRIOR curr 
  START WITH curr = 1;
  </code></pre>
  
  <p>Basically, I'm running a query to generate XSL that is used to correct XML files. I'm using sys_connect_by_path to combine the strings together into one single chunk, which is easier than copying and pasting many values from many rows. I can't use any custom string aggregate functions because this query runs on a production database where I can't just go and create functions as I want.</p>
  
  <p>The problem is that running my query will return: </p>
  
  <pre>
  ORA-01489: result of string concatenation is too long
  01489. 00000 -  "result of string concatenation is too long"
  *Cause:    String concatenation result is more than the maximum size.
  *Action:   Make sure that the result is less than the maximum size.
  </pre>
  
  <p>...in cases where there is too much data. As you can see, I've been applying the <code>to_clob()</code> function to anywhere I think it might help, but it doesn't seem to have made much difference. Are there any other ways to handle this besides resorting to PL/SQL? I'd prefer to keep this as a query because the result of this query is exported to a report template which shows a number of useful pieces of information side-by-side with the XSL. It would be nice to be able to do all of this in one single step rather than in several steps.</p>
  
  <p>(Oracle 10g)</p>
  
  <hr>
  
  <p>Eventually, I found this page:</p>
  
  <p><a href="http://www.sqlsnippets.com/en/topic-11787.html" rel="nofollow">http://www.sqlsnippets.com/en/topic-11787.html</a></p>
  
  <p>about string aggregation techniques in Oracle. I suspect the only ones that will work in my situation are the XML method and the Model methods. I couldn't get the model methods to work quite right so I just went with the XML method.</p>