This discussion is archived
1 Reply Latest reply: Nov 1, 2010 11:51 AM by 3025 RSS

XMLTable and timestamp with time zone

3025 Explorer
Currently Being Moderated
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

Legend

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