Ошибка при выборе метки времени из столбца XMLType в Oracle 11g

Question

У меня есть 2 базы данных Oracle 11g с таблицей, содержащей столбец XMLType, и некоторые тестовые данные, отличающиеся только разделителем (.,) Для миллисекунд временной отметки:

create table TEST_TIMESTAMP (
  ID  number(19,0) constraint "NN_TEST_TIMESTAMP_ID" not null,
  DOC xmltype      constraint "NN_TEST_TIMESTAMP_DOC" not null
);

insert into TEST_TIMESTAMP values ( 1, xmltype('<?xml version="1.0" encoding="utf-8"?><test><ts>2015-04-08T04:55:33.11</ts></test>'));
insert into TEST_TIMESTAMP values ( 2, xmltype('<?xml version="1.0" encoding="utf-8"?><test><ts>2015-04-08T04:55:33,11</ts></test>'));

Когда я пытаюсь извлечь метку времени с помощью следующих операторов, происходит сбой либо с первым документом в одной базе данных, либо со вторым документом в другой базе данных.

select x.*
from TEST_TIMESTAMP t,
     xmltable( 
     '/test'
     passing t.DOC
     columns
       ORIGINAL varchar2(50) path 'ts',
       RESULT timestamp with time zone path 'ts'
 ) x
 where t.ID = 1;

 select x.*
 from TEST_TIMESTAMP t,
 xmltable( 
      '/test'
      passing t.DOC
      columns
        ORIGINAL varchar2(50) path 'ts',
        RESULT timestamp with time zone path 'ts'
 ) x
 where t.ID = 2;

Ошибка, которую я получаю:

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
        incorrect.  The input data did not contain a number where a number was
        required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

Единственные различия между этими базами данных, которые я нашел:

• DB1: версия =11.2.0.1.0, NLS_CHARACTERSET=AL32UTF8 -> ошибка в документе 2
• DB2: версия =11.2.0.2.0, NLS_CHARACTERSET=WE8MSWIN1252 -> ошибка в документе 1

DB1 имеет поведение, которое я ожидаю. Кто-нибудь знает, почему эти базы данных ведут себя по-разному и как решить проблему в DB2?

Заранее спасибо Оливер

Answer 1

Я предполагаю, что nls_timestamp_format отличается между двумя базами данных.

Однако вместо принудительного неявного преобразования на уровне XMLTABLE я бы сделал явное преобразование в списке выбора:

with test_timestamp as (select 1 id, xmltype('<?xml version="1.0" encoding="utf-8"?><test><ts>2015-04-08T04:55:33.11</ts></test>') doc from dual union all
                        select 2 id, xmltype('<?xml version="1.0" encoding="utf-8"?><test><ts>2015-04-08T04:55:33,11</ts></test>') doc from dual)
select x.original,
       to_timestamp(x.original, 'yyyy-mm-dd"T"hh24:mi:ss,ff2') result
from   test_timestamp t,
       xmltable('/test' passing t.doc
                columns original varchar2(50) path 'ts') x;

ORIGINAL                                           RESULT                                            
-------------------------------------------------- --------------------------------------------------
2015-04-08T04:55:33.11                             08/04/2015 04:55:33.110000000
2015-04-08T04:55:33,11                             08/04/2015 04:55:33.110000000

NB Я обнаружил, что при использовании "ss.ff2" произошла ошибка, но "ss,ff2" отлично справился с обоими случаями. Я не уверен, зависит ли это от некоторых других настроек NLS или нет.