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
Post a Comment