OraFAQ Forum: SQL & PL/SQL » returning dbms_xmlgen.getxml into a varchar2

Home » SQL & PL/SQL » SQL & PL/SQL » returning dbms_xmlgen.getxml into a varchar2 (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

returning dbms_xmlgen.getxml into a varchar2 [message #452232]

Tue, 20 April 2010 16:11

apollo
Messages: 20
Registered: September 2007

Junior Member

I know that dbms_xmlgen.getxml returns a CLOB, however it can be returned into a VARCHAR2 as long as the returned value is not "too big". The thing that confuses me is the "too big" value seems to be 4043 characters rather than the expected 32768 characters. Does anyone know why this is? Thanks in advance.

Below are two code examples, one that works and one that doesn't. (note that we are in a multibyte environment).

This example successfully returns a 4042 character string into variable "s2". "function_gen_4000_characters" is just a function that returns 4000 characters as a VARCHAR2. The extra 42 characters are XML tags.

Code: [Select all] [Show/ hide]

DECLARE
   TYPE rc IS ref CURSOR;
   s rc;
   s2 VARCHAR2(32767);
   ctx DBMS_XMLGEN.ctxHandle;
BEGIN
   OPEN s FOR
   SELECT xmlforest(function_gen_4000_characters "a")"b"
     FROM dual;
   ctx := dbms_xmlgen.newcontext( s );
   dbms_xmlgen.setrowsettag( ctx, NULL );
   dbms_xmlgen.setrowtag( ctx, NULL );
   s2 := dbms_xmlgen.getxml( ctx );
END;

Now this next example, which should return 4043 characters, raises error "PL/SQL: numeric or value error: character string buffer too small".

Code: [Select all] [Show/ hide]

DECLARE
   TYPE rc IS ref CURSOR;
   s rc;
   s2 VARCHAR2(32767);
   ctx DBMS_XMLGEN.ctxHandle;
BEGIN
   OPEN s FOR
   SELECT xmlforest(function_gen_4001_characters "a")"b"
     FROM dual;
   ctx := dbms_xmlgen.newcontext( s );
   dbms_xmlgen.setrowsettag( ctx, NULL );
   dbms_xmlgen.setrowtag( ctx, NULL );
   s2 := dbms_xmlgen.getxml( ctx );
END;

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452237 is a reply to message #452232]

Tue, 20 April 2010 18:48

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Probably to do with the SQL limit for VARCHAR2 being 4000, 32767 is the PL/SQL limit, but without a complete example I can't really say for sure.

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452240 is a reply to message #452237]

Tue, 20 April 2010 19:40

apollo
Messages: 20
Registered: September 2007

Junior Member

same thing occurred to me, however if that was the case I wouldn't expect the limit to be greater than 4k, and in a multibyte environment the max number of chars for a varchar2 column is 1333.

what other info can I include in my examples that would be helpful?

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452284 is a reply to message #452232]

Wed, 21 April 2010 01:51

_jum
Messages: 577
Registered: February 2008

Senior Member

A simple test example shows, that s2 can (10.2.0.1.0 - 64bit) exeed 4000 Bytes:

Code: [Select all] [Show/ hide]

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
   TYPE rc IS ref CURSOR;
   s rc;
   s2 VARCHAR2(32767);
   ctx DBMS_XMLGEN.ctxHandle;
BEGIN
   OPEN s FOR
     SELECT XMLElement(XML1,XMLForest(LEVEL AS lv)) b
       FROM dual CONNECT BY LEVEL<=300;
   --SELECT xmlforest(function_gen_4001_characters "a")"b"  FROM dual; 
   ctx := dbms_xmlgen.newcontext( s );
   --dbms_xmlgen.setrowsettag( ctx, NULL );
   dbms_xmlgen.setrowtag( ctx, NULL );
   s2 := dbms_xmlgen.getxml( ctx );
   dbms_output.put_line('Length='||length(s2));
END;

Length=11333

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452318 is a reply to message #452232]

Wed, 21 April 2010 05:30

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

I suspect the error is coming from the function call function_gen_4001_characters.
You're calling it via SQL so the SQL VARCHAR2 limit is being applied. If you called it via PL/SQL I suspect you wouldn't get the error.
What is the RETURN type of the function?

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452346 is a reply to message #452318]

Wed, 21 April 2010 08:55

apollo
Messages: 20
Registered: September 2007

Junior Member

The error is definitely coming from the dbms_xmlgen.getxml function. That gen_4001_characters function is just fictitious - you can replace it with anything that will give you 4k+ characters.

I tried running _jum's example and still got this error, so perhaps it's just something with our system.

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452354 is a reply to message #452346]

Wed, 21 April 2010 09:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> so perhaps it's just something with our system.
Perhaps

ALWAYS
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

Report message to a moderator

Re: returning dbms_xmlgen.getxml into a varchar2 [message #452361 is a reply to message #452354]

Wed, 21 April 2010 11:09

apollo
Messages: 20
Registered: September 2007

Junior Member

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Report message to a moderator

Previous Topic:	distinct timestamp?
Next Topic:	Progressive sum

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 19 21:56:10 CDT 2025