"ORA-22812: cannot reference nested table column's storage table" when trying to access system table

Question

I have a table in my Oracle 12c Database

XML Schema creation:

BEGIN
-- Register the schema
DBMS_XMLSCHEMA.registerSchema('http://www.example.com/fvInteger_12.xsd',
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="FeatureVector">
<xs:complexType>
<xs:sequence>
<xs:element name="feature" type="xs:integer" minOccurs="12" maxOccurs="12"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>',
   TRUE, TRUE, FALSE);
END;
/

Created the table:

CREATE TABLE fv_xml_12_1000 (
   id    NUMBER,
   fv  XMLTYPE)
   XMLTYPE fv STORE AS OBJECT RELATIONAL
      XMLSCHEMA "http://www.example.com/fvInteger_12.xsd"
      ELEMENT "FeatureVector";

The table DDL:

SELECT 
DBMS_METADATA.GET_DDL( 'TABLE','FV_XML_12_1000') 
FROM DUAL;

The result of the query above:

  CREATE TABLE "HIGIIA"."FV_XML_12_1000"
   (    "ID" NUMBER,
    "FV" "SYS"."XMLTYPE"
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 VARRAY "FV"."XMLDATA"."feature" STORE AS TABLE "SYS_NTZqNkxcSIThTgU5pCWr3HmA=="

 (( PRIMARY KEY ("NESTED_TABLE_ID", "SYS_NC_ARRAY_INDEX$")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) RETURN AS LOCATOR
  XMLTYPE COLUMN "FV" XMLSCHEMA "http://www.example.com/fvInteger_12.xsd" ELEMEN
T "FeatureVector" ID 4129

I want to access this table, that is within the HIGIIA schema (it is in the higiia's user_tables, indeed). :

SYS_NTZqNkxcSIThTgU5pCWr3HmA==

However, I am not able to execute the command:

desc SYS_NTZqNkxcSIThTgU5pCWr3HmA==

Because I get the error:

SP2-0565: Identificador invalido.

The query:

select * from "SYS_NTZqNkxcSIThTgU5pCWr3HmA=="

Return the error:

ORA-22812: cannot reference nested table column's storage table

What should I do to access this table (table SYS_NTZqNkxcSIThTgU5pCWr3HmA==)?

Thanks in advance!

Answer 1

Though the column FV is a nested table, it cannot be accessed like a table. All access must be through the table FV_XML_12_1000.

SELECT *
  FROM TABLE( SELECT FV
                FROM FV_XML_12_1000);

Answer 2

You are storing XML documents as 'object relational', which is causing Oracle to create an internal table for the storage, which you do not generally need to access directly.

You can though, by unnesting the table; note that you have to include a table alias, and use that to make the dot notation resolve properly; and the "feature" has to be quoted because it's case-sensitive:

select f.id, t.column_value
from fv_xml_12_1000 f
cross join table(f.fv.xmldata."feature") t;

You can't see the whole fv or its implicit XMLDATA, just the feature values held in the varray.

If I create three dummy rows:

insert into fv_xml_12_1000 values (1, xmltype.createxml ('<?xml version="1.0"?>
<FeatureVector xmlns:ns="http://www.example.com/fvInteger_12.xsd">
 <feature>123</feature>
</FeatureVector>'));

insert into fv_xml_12_1000 values (2, xmltype.createxml ('<?xml version="1.0"?>
<FeatureVector xmlns:ns="http://www.example.com/fvInteger_12.xsd">
 <feature>234</feature>
</FeatureVector>'));

insert into fv_xml_12_1000 values (3, xmltype.createxml ('<?xml version="1.0"?>
<FeatureVector xmlns:ns="http://www.example.com/fvInteger_12.xsd">
 <feature>456</feature>
 <feature>567</feature>
</FeatureVector>'));

then that query gives me:

        ID Result Sequence
---------- ---------------
         1             123
         2             234
         3             456
         3             567

---

You can also just access the XML documents using normal XML DB mechanisms; to see the stored data as XML documents just do:

select fv from fv_xml_12_1000;

or add a filter to pick a single ID's XML document.

if you want to extract elements from the XML documents you can use an XQuery or XMLTable; this is equivalent to the previous query:

select x.*
from fv_xml_12_1000 f
cross join xmltable('/' passing f.fv columns x xmltype path '.') x;

... but you can add a more useful XPath expression and/or columns clauses to get the data you want relationally, e.g.:

select f.id, x.feature
from fv_xml_12_1000 f
cross join xmltable(
  '/FeatureVector/feature'
  passing f.fv
  columns feature number path '.')
x;

which gives you the master table ID value and all of the related feature numbers, with one row per ID/feature. With the same three dummy rows as before, that query gives me:

        ID    FEATURE
---------- ----------
         1        123
         2        234
         3        456
         3        567