Convert between Charsets in a plsql stored procedure (ora10g)

From: Michael Postmann <postmann_at_s2o.at>
Date: Mon, 25 Apr 2005 16:28:16 +0200
Message-ID: <1114439277.922700@newsmaster-03.atnet.at>

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.createtemporary(p_clob, TRUE);
       s2o_lob.converttoclob(
          p_clob,
          p_blob,
          s2o_lob.lobmaxsize,
          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 Received on Mon Apr 25 2005 - 09:28:16 CDT