7 Replies Latest reply: Nov 21, 2012 4:07 AM by odie_63 RSS

    XMLELEMENT and decimal numbers

    Peter Gjelstrup
      Hello members,

      I just encountered a problem, which indicate that XMLELEMENT relies on NLS settings.
      SQL> alter session set nls_numeric_characters = ',.'
        2  /
      
      Session altered.
      
      SQL> select xmlelement("x",1.23) from dual
        2  /
      
      XMLELEMENT("X",1.23)
      -----------------------------------------------------------------
      <x>1,23</x>
      
      SQL> alter session set nls_numeric_characters= '.,'
        2  /
      
      Session altered.
      
      SQL> select xmlelement("x",1.23) from dual
        2  /
      
      XMLELEMENT("X",1.23)
      -----------------------------------------------------------------
      <x>1.23</x>
      
      SQL> select *from v$version
        2  /
      
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      PL/SQL Release 10.2.0.3.0 - Production
      CORE    10.2.0.3.0      Production
      TNS for Linux: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      
      SQL>
      This kind of surprises me, especially since according to XML DB Developer's Guide 10g Release 2 (10.2), W3C standards are honored since 10gR2:
      http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1028612
      The XML Schema standard specifies that dates and timestamps in XML data be in standard formats.
      XML generation functions in Oracle XML DB produce XML dates and timestamps according to this standard.

      In releases prior to Oracle Database 10g Release 2, the database settings for date and timestamp formats were used for XML, instead of the XML Schema standard formats.
      You can reproduce this previous behavior by setting the database event 19119, level 0x8, as follows:
      Can anybody confirm, maybe even explain above behaviour?
      - Maybe I am just fooling myself, somehow?


      Sorry for asking, but I did try to search (a little) for an answer ;)

      Best regards
      Peter
        • 1. Re: XMLELEMENT and decimal numbers
          Solomon Yakobson
          Peter Gjelstrup wrote:

          I just encountered a problem, which indicate that XMLELEMENT relies on NLS settings.
          And what else did you expect? Did you read docs on XMLELEMENT? Parameter value_expr is a string. You pass a number. Therefore Oracle implicitly converts it to string. And obviously it uses nls_numeric_characters for that.

          SY.
          • 2. Re: XMLELEMENT and decimal numbers
            Peter Gjelstrup
            Doh - never came near that "early" implicit conversion

            Thanks SY


            BR
            Peter
            • 3. Re: XMLELEMENT and decimal numbers
              Solomon Yakobson
              Peter,

              The only exepction I am aware of is dates & timestamps. No matter what NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT is, XML will override it.

              SY.
              • 4. Re: XMLELEMENT and decimal numbers
                Peter Gjelstrup
                If you don't mind SY, need to follow up.

                Perhaps you could explain how this is different:
                SQL> alter session set nls_date_format = 'dd.mm.yy
                  2  /
                
                Session altered.
                
                SQL> select xmlelement("Now",sysdate) from dual
                  2  /
                
                XMLELEMENT("NOW",SYSDATE)
                --------------------------------------------------
                <Now>2012-11-20</Now>
                
                SQL> alter session set nls_date_format = 'hh24:mi'
                  2  /
                
                Session altered.
                
                SQL> select xmlelement("Now",sysdate) from dual
                  2  /
                
                XMLELEMENT("NOW",SYSDATE)
                --------------------------------------------------
                <Now>2012-11-20</Now>
                
                SQL>
                Reading docs on XMLELEMENT, I don't think it is absolutely clear that value_expr is a string ?

                BR
                Peter


                Edit: Think you already answered above :)

                But when I read the first docs I pointed to, my feeling is it should have been the same too, for numbers.

                Edited by: Peter on Nov 20, 2012 1:08 PM

                Edit, Edit: more dohs, reading that doc for fourth or fith time, I now see that it DOES NOT MENTION NUMBERS :(

                Edited by: Peter on Nov 20, 2012 1:18 PM
                • 5. Re: XMLELEMENT and decimal numbers
                  Solomon Yakobson
                  Peter Gjelstrup wrote:
                  Edit, Edit: more dohs, reading that doc for fourth or fith time, I now see that it DOES NOT MENTION NUMBERS :(
                  So what's the issue. You know implitit conversions should be avoided (replaced with explicit). So issue either:
                  SQL> select xmlelement("x",to_char(1.23,'FM999D999','nls_numeric_characters= ''.,''')) from dual
                    2  /
                  
                  XMLELEMENT("X",TO_CHAR(1.23,'FM999D999','NLS_NUMERIC_CHARACTERS=''.,'''))
                  --------------------------------------------------------------------------------
                  <x>1.23</x>
                  Or
                  SQL> select xmlelement("x",to_char(1.23,'FM999D999','nls_numeric_characters= '',.''')) from dual
                    2  /
                  
                  XMLELEMENT("X",TO_CHAR(1.23,'FM999D999','NLS_NUMERIC_CHARACTERS='',.'''))
                  --------------------------------------------------------------------------------
                  <x>1,23</x>
                  
                  SQL> 
                  and xml element will be independent of session nls settings.

                  SY.
                  • 6. Re: XMLELEMENT and decimal numbers
                    Peter Gjelstrup
                    Solomon Yakobson wrote:
                    So what's the issue.
                    None, other than I apparently have read "dates and timestamps" as "dates and numbers". Over and over again.


                    Thanks for your patience.
                    Peter

                    Still finding it strange that Oracle choose to be w3c compliant with dates, but not with decimals
                    • 7. Re: XMLELEMENT and decimal numbers
                      odie_63
                      Solomon Yakobson wrote:
                      No matter what NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT is, XML will override it.
                      Correct, but unfortunately the dateTime fractional part still follows NLS settings :
                      SQL> select xmlserialize(content xmlelement("ts", systimestamp)) from dual;
                       
                      XMLSERIALIZE(CONTENTXMLELEMENT
                      --------------------------------------------------------------------------------
                      <ts>2012-11-21T11:02:46,675000+01:00</ts>
                       
                      SQL> alter session set nls_numeric_characters = ".,";
                       
                      Session altered
                       
                      SQL> select xmlserialize(content xmlelement("ts", systimestamp)) from dual;
                       
                      XMLSERIALIZE(CONTENTXMLELEMENT
                      --------------------------------------------------------------------------------
                      <ts>2012-11-21T11:03:17.581000+01:00</ts>
                       
                      And that seems to cause various related problems, such as : {thread:id=2464867}