I am generating XML by use of DBMS_XMLGEN.getxml pakage and the output i am storing into one CLOB. Then i am reading the value from the CLOB by use of DBMS_LOB.READ for every 32767 count of data. Below is my code. My Total LOB length is 450755. In the loop, When it reaches 393205, i am getting the below error.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
DECLARE
v_xml_clob CLOB := EMPTY_CLOB();
v_lob_length INTEGER;
v_index INTEGER := 1;
v_read_cnt INTEGER;
v_chunk VARCHAR2(32767);
BEGIN
o_ret_code := 0;
SELECT DBMS_XMLGEN.getxml ('SELECT OBJECT_NAME, OBJECT_TYPE FROM DBS_OBJECTS')
INTO v_xml_clob
FROM DUAL;
v_lob_length := NVL(DBMS_LOB.getlength(v_xml_clob),0);
DBMS_OUTPUT.PUT_LINE('LOB Length : '||v_lob_length);
v_index := 1;
-- Start to read the data from CLOB object
WHILE v_index <= v_lob_length
LOOP
DBMS_OUTPUT.PUT_LINE('IN');
v_read_cnt := 32767;
DBMS_LOB.read (
v_xml_clob,
v_read_cnt,
v_index,
v_chunk
);
-- USe fnd_file.put function to place the XML data into concurrent view output
fnd_file.put(fnd_file.output,v_chunk);
DBMS_OUTPUT.PUT_LINE('v_index Start: '||v_index);
v_index := v_index + v_read_cnt;
DBMS_OUTPUT.PUT_LINE('v_index End: '||v_index);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
NULL;
END;
Please let me know what may be the problem
Thanks