Display CLOB content on AL32UTF8

From: Rios <rios_at_gmail.com>
Date: Wed, 27 Dec 2006 14:53:38 -0500
Message-ID: <12p5jq35ltli919@corp.supernews.com>

10gR2/Solaris

With the code below I load a (xml) file to CLOB File = 1521 bytes
But "dbms_lob.getlength(l_xmlclob)" returns half of that : 760

So when I try to read the CLOB out, the output is "?????????????????????"

Database NLS_CHARACTERSET = AL32UTF8
So DB character set is double-byte

What can one do to make the output readable (in western ascii) ?

thanks

DECLARE

  l_xmlclob CLOB := EMPTY_CLOB;
  l_bfile bfile;
  l_length number ;
  l_amt number default 0;
  l_loc NUMBER := 0;
  l_anc NUMBER := 1;
  l_offset number default 1;
  l_text VARCHAR2(300) ;

BEGIN
  dbms_lob.createtemporary(l_xmlclob, TRUE);   l_bfile := bfilename('DIR_TEST', 'somefile.xml');

 dbms_lob.fileopen(l_bfile);
 dbms_lob.loadfromfile(l_xmlclob,l_bfile,dbms_lob.getlength(l_bfile));
 dbms_lob.fileclose(l_bfile);

-- CLOB length

 dbms_output.put_line(dbms_lob.getlength(l_xmlclob));
-- display the CLOB content

LOOP

  l_loc := dbms_lob.instr(l_xmlclob, chr(10), l_anc);
  l_text := dbms_lob.substr(l_xmlclob, l_loc - l_anc, l_anc);
  l_anc := l_loc + 1;

  dbms_output.put_line(l_text);
  EXIT WHEN l_loc = 0;
END LOOP;
END; NLS_CHARACTERSET = WE8MSWIN1252 NLS_CHARACTERSET = AL32UTF8 Received on Wed Dec 27 2006 - 13:53:38 CST