Re: concatenating clob with string in SELECT

On 3 Aug 2006 12:47:07 -0700, mikeg13_at_gmail.com wrote:

>I have dynamically created an INSERT statement that is populating a
>table with a CLOB field. The value being inserted into the CLOB field
>is generated by concatenating a bunch of strings. In some case, it
>exceeds the 4K limit for string concatenation. The concatenation will
>have the 4G limit of a CLOB, if one of the fields being concatenated is
>of a clob data type. How can I create a clob field to be used in the
>concatenation in the SELECT statement?
>
>For example,
>
>insert into table1 (field1, clobfield) SELECT field1, <4k
>string>||<4kstring> from table2;
>
>I would like to put a clob in the concatenation string to make this
>work. Something like ...
>
>insert into table1 (field1, clobfield) SELECT field1, <4k
>string>||<4kstring>||<clob literal> from table2;

I would
- convert the insert statement to an anonymous pl/sql block with one variable a CLOB record. I would make sure the variable would get an EMPTY_LOB() assigned, and concatenate the strings in that variable - secondly I would get rid of the dynamically created INSERT. Your application is not going to scale, and either you will be lynched (by end-users) or sued (by their employers).

--
Sybrand Bakker, Senior Oracle DBA