Re: help with xmltype column larger than 4000 bytes

Hello, roger,

near 01:08 14-Jul from xrsr_at_rogerware.com:

> 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?

xmltype defined as CLOB. CLOB stored internally (like a varchar2) for data with length < 4000. So there is no any "conversion".

> 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.

This is an algorythm (in the pretty good defined cursor):

1. get clob value into varchar2(32000) variable.
2. use replace function against that variable.
3. put that variable back.

You may strict cursor with length(xmltype column) > 4000 rows. Other rows can be modified with update statement.

> 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
> >
> >
>

-- 
mbr Waldhausen

P.S. Die Ansichten, hier gefassen, sind nur meine
und haben keine Verhaeltnis zu meinem Arbeitegeber.