Re: Display CLOB content on AL32UTF8

Rios schreef:
> 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
>
>

Application Developers Guide, chapter 4, on LOBs: For CLOB and NCLOB instances used in OCI (Oracle Call Interface), or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another.

The DBMS_LOB.LOADCLOBFROMFILE API, performs an implicit conversion from binary data to character data when loading to a CLOB or NCLOB. With the exception of DBMS_LOB.LOADCLOBFROMFILE, LOB APIs do not perform implicit conversions from binary data to character data.

For example, when you use the DBMS_LOB.LOADFROMFILE API to populate a CLOB or NCLOB, you are populating the LOB with binary data from a BFILE. In this case, you must perform character set conversions on the BFILE data before calling DBMS_LOB.LOADFROMFILE.

Looks like you're bitten by implicit conversion (and you forgot to do your homework :))

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...