1 Reply Latest reply: Nov 1, 2010 1:51 PM by 3025 RSS

    XMLTable and timestamp with time zone

    3025
      Hi,

      I'm having trouble using XMLTable() including columns with datatype of timestamp with local time zone. I would appreciate help from anyone would has done this before.

      I've condensed it down to the following test case, on 11.2.0.1. I will submit this to Metalink as well, because it looks to me like a bug. (Although I could be doing something stupid...)
      SQL> create table xml_test (col xmltype);
      
      Table created.
      
      SQL> insert into xml_test values ('<SomeXml><TStamp>2010-10-21T11:57:46+0000</TStamp></SomeXml>');
      
      1 row created.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> SELECT x.*
        2     FROM xml_test,
        3     XMLTABLE('/SomeXml'
        4        PASSING col
        5        COLUMNS tstamp timestamp with local time zone PATH 'TStamp') x
        6  /
      SELECT x.*
      *
      ERROR at line 1:
      ORA-01843: not a valid month
      Changing NLS date format to match XML Schema dateTime format doesn't help:
      SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT= 'YYYY-MM-DD"T"HH24:MI:SSTZHTZM';
      
      SQL> select systimestamp from dual;
      
      SYSTIMESTAMP
      ---------------------------------------------------------------------------
      2010-10-25T09:58:52-0700
      
      SQL> SELECT x.*
        2     FROM xml_test,
        3     XMLTABLE('/SomeXml'
        4        PASSING col
        5        COLUMNS tstamp timestamp with local time zone PATH 'TStamp') x
        6  /
      SELECT x.*
      *
      ERROR at line 1:
      ORA-01843: not a valid month
      Thanks in advance,
      Malcolm