oracle - PL/SQL DBMS_XMLQUERY max size -


my oracle version 11g release 2

our system using dbms_xmlquery transform sql result xml, data becoming large , error: ora-06502: pl/sql: numeric or value error

the reason seems dbms_xmlquery cannot handle many records, oracle's official document doesn't show limitation. maybe have done wrong. can reproduce problem in following steps:

step1:

create table xmldata ( data_id int primary key, data_str varchar2(100) );

step2:

insert xmldata values(1, 'test0123456789'); insert xmldata values(2, 'test0123456789'); insert xmldata values(3, 'test0123456789'); .... insert xmldata values(500, 'test0123456789'); 

step3:

create or replace  function test(total in int) return clob int; vn_ctx              dbms_xmlquery.ctxhandle; begin     vn_ctx := dbms_xmlquery.newcontext('select data_id, data_str xmldata rownum <= ' || total);     dbms_xmlquery.propagateoriginalexception(vn_ctx,true);     dbms_xmlquery.usenullattributeindicator(vn_ctx,true);      dbms_xmlquery.setrowtag(vn_ctx, 'item');     dbms_xmlquery.setrowsettag(vn_ctx, 'podata');     return dbms_xmlquery.getxml(vn_ctx); end; 

step4:

execute function test number greater 400. you'll 'ora-06502: pl/sql: numeric or value error'

thanks in advance

edit

really sorry... got old code, adds log statement without noticing me. log statement can accept maximum of 32767 characters in 1 line, error raised. above function executed debug tool, gives same error, it's tool's problem, not oracle.

thanks answering , sorry naive mistakes...

i no errors if variable hold return value big enough.

declare    rv1 clob;   rv2 varchar2(32000);  begin   rv1 := test(400);    -- no error   rv2 := test(200);    -- no error - returned xml < 32000 in length.   rv2 := test(400);    -- ora-06502: pl/sql: numeric or value error'  end; 

Comments

Popular posts from this blog

Android layout hidden on keyboard show -

google app engine - 403 Forbidden POST - Flask WTForms -

c - Why would PK11_GenerateRandom() return an error -8023? -