Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error ORA-06502 in DBMS_LOB.READ

634151Mar 23 2011 — edited Mar 23 2011
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
This post has been answered by Peter Gjelstrup on Mar 23 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 20 2011
Added on Mar 23 2011
6 comments
2,427 views