help with xmltype column larger than 4000 bytes

[roger]

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.

[Daniel Morgan]

Post by roger
Help please...
I'm on 9.2.0.3.0, on solaris.
I've got a table with a sys.xmltype colum,
update table mytable
set myxmlcol = replace(myxmlcol, oldval, newval)
where ...
The problem is that if the myxmlcol contains more
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

[roger]

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.

Post by Daniel Morgan

Post by roger
Help please...
I'm on 9.2.0.3.0, on solaris.
I've got a table with a sys.xmltype colum,
update table mytable
set myxmlcol = replace(myxmlcol, oldval, newval)
where ...
The problem is that if the myxmlcol contains more
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

[Daniel Morgan]

Post by roger
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.

VARCHAR2 columns max at 4K bytes. VARCHAR2 variables at 32K.

XML should be stored as a CLOB or using the internal XML data type.

Daniel Morgan

[roger]

Post by Daniel Morgan
VARCHAR2 columns max at 4K bytes. VARCHAR2 variables at 32K.

Huh? Did you mean VARCHAR at 4k and VARCHAR2 at 32k?

Post by Daniel Morgan
XML should be stored as a CLOB or using the internal XML data type.

Yes, I am using sys.xmltype as the column definition, and as I
say, I have no problem storing xml documents of any size.


But, if I do
select cast(xlmcol as varchar2(8000)) xml from mytable

I get an error:
ORA-00910: specified length too long for its datatype

If I use varchar2(4000) then all the pretty xml comes out.

So, if there's some way to use a varchar or varchar2 or
whatever, that is bigger than 4K, please help me see it.

Thanks again.

[Daniel Morgan]

Comments in-line.

Post by roger

Post by Daniel Morgan
VARCHAR2 columns max at 4K bytes. VARCHAR2 variables at 32K.

Huh? Did you mean VARCHAR at 4k and VARCHAR2 at 32k?

No. I mean't exactly what I said.

Post by roger

Post by Daniel Morgan
XML should be stored as a CLOB or using the internal XML data type.

Yes, I am using sys.xmltype as the column definition, and as I
say, I have no problem storing xml documents of any size.
But, if I do
select cast(xlmcol as varchar2(8000)) xml from mytable
ORA-00910: specified length too long for its datatype
If I use varchar2(4000) then all the pretty xml comes out.
So, if there's some way to use a varchar or varchar2 or
whatever, that is bigger than 4K, please help me see it.
Thanks again.

Try getting it out as a CLOB. Does that work?

Daniel Morgan
***@x.washington.edu
(replace 'x' with 'u' to reply)

[Wit Serdakovskij]

Hello, roger,

Post by roger
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".

Post by roger
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.

Post by roger
Thanks a bunch.

Post by Daniel Morgan

Post by roger
Help please...
I'm on 9.2.0.3.0, on solaris.
I've got a table with a sys.xmltype colum,
update table mytable
set myxmlcol = replace(myxmlcol, oldval, newval)
where ...
The problem is that if the myxmlcol contains more
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

[Galen Boyer]

Post by roger
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?

I believe you have to roll your own "replace".

The XMLType can return a clob. The CLOB has functions, INSTR()
to get at the offsets you want to replace and WRITE() to write
characters to offsets.

Thats what I'd play with as my second choice. My first choice
would be to check out asktom and see if he has examples.

http://asktom.oracle.com/pls/ask

[roger]

I've implemented what I need to do using the dbms_lob functions.
Thanks for the suggestion.

Post by Galen Boyer

Post by roger
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?

I believe you have to roll your own "replace".
The XMLType can return a clob. The CLOB has functions, INSTR()
to get at the offsets you want to replace and WRITE() to write
characters to offsets.
Thats what I'd play with as my second choice. My first choice
would be to check out asktom and see if he has examples.
http://asktom.oracle.com/pls/ask