XMLType.schemaValidate() failing in 11.2.0.3 with ORA-30992, ORA-01858 when NLS_TERRITORY=GERMANY (Doc ID 1397051.1)

Last updated on JANUARY 19, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.3 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Since upgrading to 11.2.0.3, the following SQL is now failing when NLS_TERRITORY = GERMANY:

SQL> select * from nls_session_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  GERMANY
NLS_CURRENCY                   ?
NLS_ISO_CURRENCY               GERMANY
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD.MM.RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ?
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL> @test_date.sql
SQL> declare
  2    i_xml_clob CLOB;
  3    o_xml XMLtype;
  4  begin
  5    i_xml_clob :=
  6  '<?xml version="1.0" encoding="UTF-8"?>
  7  <myData xsi:schemaLocation="http://www.oracle.com/TEST-SCHEMA-001 TEST-SCHEMA-001.xsd"
  8          xmlns="http://www.oracle.com/TEST-SCHEMA-001"
  9          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 10          <myDate1>2011-10-05T00:00:00.000+02:00</myDate1>
 11          <myDate2>2011-09-30T00:00:00.000+02:00</myDate2>
 12  </myData>
 13  ';
 14
 15    o_xml := XMLType( i_xml_clob );
 16
 17    o_xml.schemaValidate();
 18  end;
 19  /
declare
*
ERROR at line 1:
ORA-30992: error occurred at Xpath /myData/myDate1
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at line 15

The myDate1 element is defined as

<xs:element name="myDate1" type="xs:dateTime" minOccurs="0" xdb:SQLType="TIMESTAMP WITH TIME ZONE">

The same error occurs when NLS_TERRITORY = SLOVENIA.

Changes

Database was upgraded from 11.2.0.1 to 11.2.0.3.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms