This discussion is archived
7 Replies Latest reply: Mar 6, 2013 8:40 AM by user4423142 RSS

insert data XML french language

user4423142 Newbie
Currently Being Moderated
Hello,

I use Oracle 11G. I have created a schema :
<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="data_formulation">
<xs:complexType>
<xs:sequence>
<xs:element name="data" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="famille" type="xs:string"/>
<xs:attribute name="value" type="xs:string"/>
<xs:attribute name="type" type="xs:string"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

a table using this schema

CREATE TABLE DATA_FORMULATION OF XMLTYPE
XMLTYPE STORE AS BINARY XML
XMLSCHEMA "data_formulation.xsd" element "data_formulation"
DISALLOW NONSCHEMA;

I inserted data (I hope it is the good practice for inserting data)

insert into data_formulation values
(XMLType('<data_formulation>
<data famille="filtration" value="Filtrations"/>
</data_formulation>'));

Another data like this : (the table has just one record)

update data_formulation
set object_value=XMLQuery('copy $tmp:=.modify insert
node <data famille="filtration" value="750"/>
into $tmp/data_formulation return $tmp'
passing object_value returning content);

It is OK. But with french characters the insertion is bad. For example with this request :

update data_formulation
set object_value=XMLQuery('copy $tmp:=.modify insert
node <data famille="test" value="été"/>
into $tmp/data_formulation return $tmp'
passing object_value returning content);

the data inserted are :
<data famille="polymerization" value="¿©t¿©"/>

I tried to do this before inserted :
alter session SET NLS_LANGUAGE='FRENCH';
but the result is the same.

Thanks in advance for your help
  • 1. Re: insert data XML french language
    odie_63 Guru
    Currently Being Moderated
    Hi,

    What's the database character set?

    That's a typical issue related to the NLS_LANG environment variable.
    Could you check it too?

    Which client tool/version are you using to run the queries?
    Due to NLS_LANG not set correctly, your client tool may not interpret and translate what you type on your local machine to the db charset.
  • 2. Re: insert data XML french language
    user4423142 Newbie
    Currently Being Moderated
    Here are our settings

    The used client is Oracle SQL Developper. We have the same result on PC with Windows 7 or Mac OS X Lion
    .
    select * from NLS_DATABASE_PARAMETERS ;

    "PARAMETER"     "VALUE"
    "NLS_LANGUAGE"     "AMERICAN"
    "NLS_TERRITORY"     "AMERICA"
    "NLS_CURRENCY"     "$"
    "NLS_ISO_CURRENCY"     "AMERICA"
    "NLS_NUMERIC_CHARACTERS"     ".,"
    "NLS_CHARACTERSET"     "WE8ISO8859P15"
    "NLS_CALENDAR"     "GREGORIAN"
    "NLS_DATE_FORMAT"     "DD-MON-RR"
    "NLS_DATE_LANGUAGE"     "AMERICAN"
    "NLS_SORT"     "BINARY"
    "NLS_TIME_FORMAT"     "HH.MI.SSXFF AM"
    "NLS_TIMESTAMP_FORMAT"     "DD-MON-RR HH.MI.SSXFF AM"
    "NLS_TIME_TZ_FORMAT"     "HH.MI.SSXFF AM TZR"
    "NLS_TIMESTAMP_TZ_FORMAT"     "DD-MON-RR HH.MI.SSXFF AM TZR"
    "NLS_DUAL_CURRENCY"     "$"
    "NLS_COMP"     "BINARY"
    "NLS_LENGTH_SEMANTICS"     "BYTE"
    "NLS_NCHAR_CONV_EXCP"     "FALSE"
    "NLS_NCHAR_CHARACTERSET"     "AL16UTF16"
    "NLS_RDBMS_VERSION"     "11.2.0.3.0"


    SELECT * FROM NLS_SESSION_PARAMETERS ;

    "PARAMETER"     "VALUE"
    "NLS_LANGUAGE"     "FRENCH"
    "NLS_TERRITORY"     "FRANCE"
    "NLS_CURRENCY"     "€"
    "NLS_ISO_CURRENCY"     "FRANCE"
    "NLS_NUMERIC_CHARACTERS"     ", "
    "NLS_CALENDAR"     "GREGORIAN"
    "NLS_DATE_FORMAT"     "DD/MM/RR"
    "NLS_DATE_LANGUAGE"     "FRENCH"
    "NLS_SORT"     "FRENCH"
    "NLS_TIME_FORMAT"     "HH24:MI:SSXFF"
    "NLS_TIMESTAMP_FORMAT"     "DD/MM/RR HH24:MI:SSXFF"
    "NLS_TIME_TZ_FORMAT"     "HH24:MI:SSXFF TZR"
    "NLS_TIMESTAMP_TZ_FORMAT"     "DD/MM/RR HH24:MI:SSXFF TZR"
    "NLS_DUAL_CURRENCY"     "€"
    "NLS_COMP"     "BINARY"
    "NLS_LENGTH_SEMANTICS"     "BYTE"
    "NLS_NCHAR_CONV_EXCP"     "FALSE"

    Thanks a lot
  • 3. Re: insert data XML french language
    odie_63 Guru
    Currently Being Moderated
    NLS_LANG is not a database setting. You must check it in your client environment.
  • 4. Re: insert data XML french language
    user4423142 Newbie
    Currently Being Moderated
    Here are our setting in the client environment :

    Registry : NLS_LANG = FRENCH_FRANCE.WE8MSWIN1252

    Thanks
  • 5. Re: insert data XML french language
    Marco Gralike Oracle ACE Director
    Currently Being Moderated
    I find the following FAQ on NLS always very useful, despite that the layout got scrambled during the merge of the SUN and Oracle websites. Have a good read and think about what it actually says about WHERE happens WHAT (aka "is this on my client" "is this on the database" "is my database acting as a client when I use, for example, a database link")

    http://www.oracle.com/technetwork/database/globalization/nls-lang-099431.html
  • 6. Re: insert data XML french language
    odie_63 Guru
    Currently Being Moderated
    Does it work in SQL*Plus ?

    It's strange that you end up with a multi-byte encoding whereas both client and db use a single-byte charset, as well as the same codepoint for "é" (233).

    You should try XMLSerialize() function to visualize the content of the XMLType :
    select xmlserialize(document object_value) 
    from data_formulation;
  • 7. Re: insert data XML french language
    user4423142 Newbie
    Currently Being Moderated
    With XMLSerialize I can see the right characters.

    So the inserts are in fact good and my programming tool is able to read correctly the data.
    Thanks a lot

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points