Re: XML File to CLOB and Display it

klabu wrote:
> <10gR2>
> I want to read a XML file into CLOB and display the (CLOB) content.
> Keeps erroring at line#14: string buffer too small
> Can you pls help ?
> thanks
>
>
>
> DECLARE
> l_xmlclob CLOB := EMPTY_CLOB;
> l_bfile bfile;
> l_buffer VARCHAR2(100);
> l_amount NUMBER := 100 ;
> l_pos NUMBER := 1;
> BEGIN
> dbms_lob.createtemporary(l_xmlclob, TRUE);
> l_bfile := bfilename( 'DIR_FILES', 'test.xml');
> dbms_lob.fileopen(l_bfile);
> dbms_lob.loadfromfile(l_xmlclob,l_bfile,dbms_lob.getlength(l_bfile));
> dbms_lob.fileclose(l_bfile);
> LOOP
> DBMS_LOB.READ(l_xmlclob, l_amount, l_pos, l_buffer); -- error here
> dbms_output.put_line(l_buffer);
> l_pos := l_pos + l_amount;
> END LOOP;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> DBMS_OUTPUT.PUT_LINE('End of Chapter Reached.');
> END;
Try declaring l_buffer as VARCHAR2(100 CHAR). By default, Oracle uses byte length semantics for VARCHAR2s, but DBMS_LOB.READ() uses character length semantics for amount when reading CLOBs. If your database character set is not single-byte, this discrepancy will cause "buffer too small" errors.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed Dec 20 2006 - 23:41:57 CST