Re: concatenating clob with string in SELECT

Right after writing the question, the answer occurred to me. I just need to concatenate a null CLOB onto the string and the function I need is TO_CLOB(). I did not remember this function until I told myself I need something to convert a string to a clob. Duh.

select LENGTH(RPAD('*',4000)||RPAD('*',4000)) from dual

• does not work because of concatentation too long

select LENGTH(to_clob('')||RPAD('*',4000)||RPAD('*',4000)) from dual

This returns the result of 8000.

And it also worked for what I needed it to do.

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;
Received on Thu Aug 03 2006 - 23:20:55 CEST