7 Replies Latest reply: Mar 6, 2013 10:40 AM by user4423142 RSS

    insert data XML french language

    user4423142
      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
          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
            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
              NLS_LANG is not a database setting. You must check it in your client environment.
              • 4. Re: insert data XML french language
                user4423142
                Here are our setting in the client environment :

                Registry : NLS_LANG = FRENCH_FRANCE.WE8MSWIN1252

                Thanks
                • 5. Re: insert data XML french language
                  Marco Gralike
                  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
                    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
                      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