Re: Creating XML from an Oracle DB

How about trying to create a brand new database using UTF8 *instead* of
AL32UTF8 ?

I'm no expert, and I'm not doing the same thing you are, but I solved *my*
UTF8 problems
thusly:-

<characterSet>US7ASCII</characterSet>
<nationalCharacterSet>UTF8</nationalCharacterSet>

""Garrett, Philip (MAN-Corporate)"" <Philip.Garrett@xxxxxxxxxxx> wrote in
message news:D9C13100F14E4C4795A1E83B125B40350232B631@xxxxxxxxxxxxxxxxxxxx
Keith.Barnard@xxxxxxxxxxxxxx wrote:
Hi Tim,

I have spent about three hours using Google and Metalink trying to
find the

answer to what must be a FAQ when generating XML from Oracle but I
cannot find anything that answers the question.

I work for a company in the UK and we are generating an XML file from
an Oracle 9.2 database using DBMS_XMLQUERY and DBMS_LOB. The XML file
has to be
in UTF-8 format (encoding="utf-8") but this is not the format that
it is held in the database. From what I have read, it seems that it
is AL32UTF8.

Oracle's utf-8 support for the XML packages is shameful.
http://www.dbforums.com/showthread.php?t=1212787.

For any of this to happen automatically, you need to make sure that your
original data (what you're making the XML from) is stored correctly in
the database's character set. This means that if your data actually
contains utf-8, the database characterset should be AL32UTF8. It
appears your data is indeed in utf-8, because 49827 (0xC2A3) is the
utf-8 representation of the pound sign.

You can determine your database's character set with this query:
select value from v$nls_parameters
where parameter = 'NLS_CHARACTERSET'

Assuming your database characterset actually matches the data that's in
it, you can just set the client character set to your desired output,
and the encoding is done for you:

# data from Oracle will now be converted correctly
# into Perl's internal encoding.
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

# in perl...
# convert from Perl's internal encoding to utf-8 when
# printing to stdout.
binmode(STDOUT, ':utf8'); # convert from Perl's internal to utf-8

If your database characterset does not match the data (for example, your
database characterset is US7ASCII) then sorry, you will have to manually
convert each unicode column. If you're using Oracle 10G, then there are
functions to convert XML encodings explicitly using character set Ids.
Otherwise, you'll have to figure out the right hocus pocus to transfer
the data from Oracle to Perl without losing character information, and
then encode/decode in Perl with the Encode module.

If you haven't already, I recommend reading the Oracle 9i Globalization
Best Practices document. It can help get your head around how the
character sets work. http://tinyurl.com/mtsxg [oracle.com].

Hope it helps. Regards,

Philip




.

Relevant Pages

  • Re: NLS_LANG
    ... From the "Oracle Database Globalization Support Guide" I understand ... If you really plan to use WE8ISO8859P1 on the Windows client, ... But even if you plan to use a different character set on the Windows client ...
    (comp.databases.oracle.misc)
  • Conversion of Oracle UTF-8 to SQL Server Unicode
    ... I have to load text data from an Oracle 9i database into a SQL Server ... includes far-Eastern language characters ... the UTF-8 format, and copies the text into the nvarchar columns in the ...
    (microsoft.public.sqlserver.dts)
  • RE: LANG system environment variable
    ... So, if I understand you correctly, I would be better off leaving the system the way it is and change the database data to UTF-8? ... > character set the other expects. ... > to store umlaut characters and accents. ...
    (Fedora)
  • Re: character sets
    ... Set your MySQL server's character set to UTF-8. ... check if you currently have UTF-8 support. ... UTF-8 as the default character set for your database: ...
    (comp.lang.php)
  • Re: problem with charset
    ... When creating tables in my database, the fields where I think it might ... be necessary are defined as UTF-8, while the rest is usually Latin-1: ... textColumn VARCHARCHARACTER SET 'utf8', ... This ensures that transfering the data between the DB and my scripts ...
    (comp.lang.php)