471,319 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

Convert between Charsets in a plsql stored procedure (ora10g)

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
Jun 27 '08 #1
0 3312

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by simon | last post: by
15 posts views Thread by marvado | last post: by
reply views Thread by sybrandb | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.