I was browsing through the oracle documentation on CREATE TABLE and came across the followiing:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2095331
specificially this image: http://docs.oracle.com/cd/E11882_01/server.112/e26088/img/xmltype_table.gif
which suggests that the OID_clause can be used for XMLTYPE tables. (http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#i2159502)
However my attempts to put this in practice seem to fail:
CREATE TABLE oidtest OF XMLTYPE
(
PRIMARY KEY (id)
)
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
id AS (XMLCAST(XMLQUERY('/root/identifier' PASSING object_value RETURNING CONTENT) AS NUMBER))
)
OBJECT IDENTIFIER IS PRIMARY KEY;
Error starting at line 1 in command:
CREATE TABLE oidtest OF XMLTYPE
(
PRIMARY KEY (id)
)
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
id AS (XMLCAST(XMLQUERY('/root/identifier' PASSING object_value RETURNING CONTENT) AS NUMBER))
)
OBJECT IDENTIFIER IS PRIMARY KEY
Error at Command Line:10 Column:2
Error report:
SQL Error: ORA-00922: Ontbrekende of ongeldige optie.
00922. 00000 - "missing or invalid option"
*Cause:
*Action:
If I leave out the OID_clause, it works fine:
CREATE TABLE oidtest OF XMLTYPE
(
PRIMARY KEY (id)
)
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
id AS (XMLCAST(XMLQUERY('/root/identifier' PASSING object_value RETURNING CONTENT) AS NUMBER))
);
table OIDTEST created.
What am I missing here?
DB version is Enterprise 11.2.0.2.0 - 64bit