Re: help with xmltype column larger than 4000 bytes

From: roger <xrsr_at_rogerware.com>
Date: Wed, 14 Jul 2004 01:08:03 GMT
Message-ID: <Xns9525B8EE4CBErsrrogerwarecom@63.240.76.16>

OK...
So I take it that what is happening is that when I try to use the replace function, Oracle converts the xmltype col to varchar(4000) internally and that's where the problem arises?

So, there is no initialization parameter to increase the max size of a varchar column then I take it? If I could just raise that limit to 8000, I'd be set...

Otherwise, can you (or someone) please give me an indication of if it is even possible, and if so how, to use the dbms_lob package to accomplish what I'm trying to do, which is essentially, to replace all ocurrances of some string with some other string in an xmltype (or clob I guess) column that is larger than 4k.

Thanks a bunch.

Daniel Morgan <damorgan_at_x.washington.edu> wrote in news:1089763560.90028 @yasure:

> roger wrote:
> 

>> Help please...
>>
>> I'm on 9.2.0.3.0, on solaris.
>>
>> I've got a table with a sys.xmltype colum,
>> on which I need to do a string replace function like so:
>>
>> update table mytable
>> set myxmlcol = replace(myxmlcol, oldval, newval)
>> where ...
>>
>> The problem is that if the myxmlcol contains more
>> than 4000 characters, this fails with an oracle error:
>>
>> ORA-19011: Character string buffer too small
>>
>>
>> I have no problem creating and reading back the column
>> using sys.xmltype.createxml and sys.xmltype.getstringval.
>>
>> Is there some parameter that I can set to increase
>> the size of whatever internal buffer is causing me this problem.
>> 8000 would suffice in my case.
>>
>> Thanks.

> 
> You don't have more than 4000 bytes in a VARCHAR column. Likely
> it is a CLOB and you need to use the dbms_lob built-in package.
> 
> Daniel Morgan
> 
> 

Received on Tue Jul 13 2004 - 20:08:03 CDT