Re: concatenating clob with string in SELECT
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 04 Aug 2006 09:01:09 -0700
Message-ID: <1154707270.151468_at_bubbleator.drizzle.com>
>> 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.
Date: Fri, 04 Aug 2006 09:01:09 -0700
Message-ID: <1154707270.151468_at_bubbleator.drizzle.com>
Maxim Demenko wrote:
> 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
Literal? Someone is actually going to key in more 4GB character by character? Seems to me it must be a case of concatenating variables but stranger things have happened.
-- [Quoted] Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Aug 04 2006 - 18:01:09 CEST