Hi Tom,

I have a charset problem after converting CLOB to BLOB. We are creating XML-Files from SQL cursors and using a convert-function which is shown in code section (test script).
After converting a CLOB to a BLOB using this functionality I am getting a charset problem with the euro sign (€). Every special character is correctly in the XML-File. The euro sign not. When I am looking for mailadresses then I see „@“ signs everywhere correctly in xml.

In the code section you can see how we are accomplish the task converting CLOB to BLOB.
Is the problem already known or has similar behavior already been reported? How can we solve our problem?


Thanks for your help.
Best regards



Code section/test script:

declare
   lboolean boolean;
   lblob    blob;
   lclob    clob;
   lclob2   clob;
   lMessage Varchar2(1000);
   linteger Integer;



   function fConvertClobToBlob(iocSource            in out nocopy Clob,
                               iobDestination       in out nocopy Blob,
                               ivDestinationCharset in            Varchar2 Default Null,
                               ovMessage               out        Varchar2)
      return Boolean is
   
      lbTemp        Blob;
      liLangContext Integer := dbms_lob.default_lang_ctx;
      liOffsetDest  Integer := 1;
      liOffsetSrc   Integer := 1;
      liWarning     Integer;
      lnBlobCsid    Number := dbms_lob.default_csid;
      lvCharset     Varchar2(30);
   
   begin
   
      if DBMS_Lob.getlength(iocSource) < 1 then
         return false;
      end if;
   
      if ivDestinationCharset is not null then
         lnBlobCsid := nls_charset_id(ivDestinationCharset);
      end if;
   
      dbms_lob.createtemporary(lbTemp, true);
      dbms_lob.convertToBlob(dest_lob     => lbTemp,
                             src_clob     => iocSource,
                             amount       => dbms_lob.lobmaxsize,
                             dest_offset  => liOffsetDest,
                             src_offset   => liOffsetSrc,
                             blob_csid    => lnBlobCsid,
                             lang_context => liLangContext,
                             warning      => liWarning);
   
      iobDestination := lbTemp;
   
      dbms_lob.freetemporary(lbTemp);
   
      return true;
   
   exception
      when others then
         raise;
   end;



   function fBlob2Filesystem (ibBlob      in out nocopy Blob,
                              ivDirectory in            varchar2,
                              ivFileName  in            varchar2)
     return Boolean is

     lrBuffer     Raw(32767);
     liAmount     Binary_integer := 32767;
     liBlobSize   Integer;
     ltFileHandle UTL_FILE.FILE_TYPE;
     liPos        Integer :=1;

   BEGIN

     liBlobSize := DBMS_LOB.getlength(ibBlob);

     if liBlobSize < 1 then
        return false;
     end if;

     ltFileHandle := UTL_FILE.fopen(ivDirectory,ivFileName,'wb',32767);

     while liPos < liBlobSize loop

       DBMS_LOB.READ(
         lob_loc => ibBlob,
          amount => liAmount,
          offset => liPos,
          buffer => lrBuffer);

       UTL_FILE.PUT_RAW (
            file => ltFileHandle,
          buffer => lrBuffer,
       autoflush => true);

       liPos:= liPos + liAmount;

     end loop;

     UTL_FILE.FCLOSE(ltFileHandle);

     return true;

     exception
       when others then
          raise;
   end;
   
   

begin

   --execute immediate 'alter session set nls_length_semantics=char';
   --> doesn´t make any difference

   lclob :=               'Char  80:' || chr(80) 
            || chr(10) || 'Char 128:' || chr(128) -- this should be the right one
            || chr(10) || 'Symbol :'  || '$€%!*#³€$€&/=ß@' 
            || chr(10) || 'Raw-Value:' || utl_raw.cast_to_raw(chr(128)) 
            || chr(10) || 'Hex-Value:' || rawtohex(utl_raw.cast_to_raw(chr(128))) 
            || chr(10) || 'C280 cast to Varchar:' || UTL_RAW.CAST_TO_VARCHAR2('C280');

   lBoolean := fConvertClobToBlob(iocSource            => lclob,
                                  iobDestination       => lblob,
                                  ivDestinationCharset => 'UTF8',--AL32UTF8
                                  ovMessage            => lMessage);

   lBoolean := fBlob2Filesystem(lblob,
                                '/samba/nh/',
                                'Euro_Blob_fConvert_DB_CHARSET.txt');
end;

Update:
Our character set on database is
WE8ISO8859P15

We also tested the charset problem on database version 12.1.0.2.0 with the same character set.

UNISTR('\20AC')