Database Software

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