title: How to dynamically add column names in a select statement (Oracle)
body: <p>My table contains a <code>CLOB</code> field which can have a length of more than 32767 bytes or characters. This would mean that, if I want to extract all the characters from this <code>CLOB</code> field, I have to split the <code>CLOB</code> into chunks and pass each chunk as a different column in a <code>SELECT</code> query statement.</p>
  
  <p>So I have this query:</p>
  
  <pre><code>SELECT dbms_lob.Substr(clob_field, dbms_lob.Getlength(clob_field), 1) AS data
  FROM   some_table
  </code></pre>
  
  <p>This won't work if <code>clob_field</code> is too large. My idea is to make chunks of 2000 in length and, for each chunk, to add a column in the <code>SELECT</code> statement.</p>
  
  <p>Idea concept:</p>
  
  <pre><code>var a = 1
  var b = 2000
  chunkAmountList = 1+(dbms_lob.Getlength(clob_field) mod 2000)
  statement = "SELECT "
  
  BEGIN
  for chunk in chunkAmountList
      statement = statement + "dbms_lob.Substr(clob_field, b, a) as data"
      a = a + 2000
      b = b + 2000
  endloop
  END
  
  statement = statement + "FROM some_table"
  statement.execute()
  </code></pre>
  
  <p>This will result in different columns with the same name (which is what I want). So if clob_field has a length of 5000, I would want this as a result:</p>
  
  <pre><code>data                  data                   data
  ----                  ----                   ----
  "first 2000 chars"    "second 2000 chars"    "last 1000 chars"
  </code></pre>
  
  <p>I want to do this in oracle, but don't know how to. Any help is really appreciated. Thanks!</p>