Re: concatenating clob with string in SELECT
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 04 Aug 2006 07:57:21 +0200
Message-ID: <eaunjv$k79$03$1_at_news.t-online.com>
>> 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;
Date: Fri, 04 Aug 2006 07:57:21 +0200
Message-ID: <eaunjv$k79$03$1_at_news.t-online.com>
DA Morgan schrieb:
> 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;
> > There is no 4K limit for string concatenation. > > A VARCHAR2 variable is 32K and there is nothing that stops you from > concatenating multiple 32K VARCHAR2s into a CLOB.
Daniel, there is a limit 4000 for an string literal (iirc, it was 2000 in earlier versions). Declaring a variable as CLOB and concatenating in it several chunks will make able to put 32k into varable (every PL SQL variable is limited to 32k) . If OP will go up to 4Gb, he should do as Sybrand said and use EMPTY_CLOB or alternatively use dbms_lob package.
Best regards
Maxim Received on Fri Aug 04 2006 - 07:57:21 CEST