Ora-00902 Creating An Object Relational Xml Table

(Doc ID 1168764.1)

Last updated on DECEMBER 27, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.3 to 11.2.0.2 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Symptoms


After registering a schema in XDB, table creation based of that schema is failing with:

BEGIN
DBMS_XMLSchema.deleteSchema(
schemaurl=>'http://nytd.nj.org',
delete_option=>DBMS_XMLSchema.Delete_Cascade_Force);
END;
/

BEGIN
DBMS_XMLSCHEMA.registerschema(
'http://nytd.nj.org',
'<xs:schema
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:nytd="http://blah.com"
attributeFormDefault="unqualified"
elementFormDefault="unqualified"
targetNamespace="http://blah.com"
xmlns:xs="http://www.w3.org/2001/XMLSchema" >
<xs:element name="NYTD_DATA_FILE">

...

<xs:complexType name="noYesDeclinedNADKBlankWithNoteType">
<xs:simpleContent>
<xs:extension base="nytd:noYesDeclinedNADKBlankType">
<xs:attribute name="note" type="nytd:charStringType" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:schema>',
LOCAL => TRUE, -- local
GENTYPES => FALSE, -- generate object types
GENBEAN => FALSE, -- no java beans
GENTABLES => FALSE, -- generate object tables
FORCE => FALSE,
OWNER => USER);

END;
/


commit;


CREATE TABLE xwarehouses (
id_file NUMBER,
xml_spec XMLTYPE)
XMLTYPE xml_spec STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://nytd.nj.org"
ELEMENT "NYTD_DATA_FILE";


CREATE TABLE xwarehouses (
*
ERROR at line 1:
ORA-00902: invalid datatype

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