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.
The code of the Euro character in WE8ISO8859P15 is 0xA4=164, not 0x80=128. (0x80 is the code from many Microsoft code pages, such as WE8MSWIN1252.) To make your test code portable among character sets, you may also use:
UNISTR('\20AC')
By the way, do not use the deprecated Oracle character set 'UTF8' for the destination character set, because, despite its name, it is CESU-8, not UTF-8. Use AL32UTF8 instead.