How to dynamically add column names in a select statement (Oracle)
<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>