This discussion is archived
7 Replies Latest reply: Nov 21, 2012 2:07 AM by odie_63 RSS

XMLELEMENT and decimal numbers

Peter Gjelstrup Guru
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Doh - never came near that "early" implicit conversion

    Thanks SY


    BR
    Peter
  • 3. Re: XMLELEMENT and decimal numbers
    Solomon Yakobson Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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}

Legend

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