Convert between Charsets in a plsql stored procedure (ora10g)

[Michael Postmann]

Hello!

I have Oracle 10g using AL32UTF8 as internal charset.

I have a documents table where uploaded files (I'm using modplsql
web-gateway (release 8i)) are stored in a blob-column. I upload a
CSV-File to this table which is in ISO 8859-1 Charset.
I now need to convert this binary data (which is in fact all
character-data) into utf-8.

I'm currently using this self-written function to do the conversion:

FUNCTION converttoclob(
p_blob IN BLOB,
p_clob OUT CLOB)
RETURN PLS_INTEGER IS
t_dest_offset PLS_INTEGER := 1;
t_src_offset PLS_INTEGER := 1;
t_blob_csid NUMBER := s2o_lob.default _csid;
t_lang_context PLS_INTEGER := s2o_lob.default _lang_ctx;
t_warning PLS_INTEGER;
BEGIN
s2o_lob.createt emporary(p_clob , TRUE);
s2o_lob.convert toclob(
p_clob,
p_blob,
s2o_lob.lobmaxs ize,
t_dest_offset,
t_src_offset,
t_blob_csid,
t_lang_context,
t_warning);
RETURN t_warning;
END;

When I do
converttoclob(t _blob, t_clob);
the umlauts and non us-ascii chars are getting converted to multibyte
chars. But however this is NOT UTF8. (I wasn't able to figure out what
encoding/charset the output is in) Also after this conversion
us-ascii-chars immediately following special chars (like german umlauts,
euro-sign, etc.) are sometimes stripped of or converted to odd
characters too.

I did not find any documentation further explaining the csid and
lang_context parameters, maybe I could solve my problem with these.
The second Problem is closely related:

I have a varchar2 variable with text including umlauts, special-chars,
etc. As the Database is AL32UTF8 the varchar2data is UTF8 too. I now
want this to be converted to ISO 8859-1 Charset which I will then
convert to blob (the data could be converted to blob in the process of
converting it to latin1 too) to encode it with base64 or
quoted-printable and send it via mail.

I don't have a clue here too..

Thx a lot for your Help!

regards,

Michael Postmann