"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!

Rutherfordium

Answer 1

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

Dolly

Answer 2

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);

Aigrette

It does not work... I have got this error: ORA-22905:cannot access rows from a non-nested table item – Rutherfordium

I edited the question - I added the table creation. It might help. – Rutherfordium

Executed the create table from your post. It gives this error. "ORA-31000: Resource 'example.com/fvInteger_12.xsd' is not an XDB schema document " – Aigrette

It seems you have changed your original table. It does not have any nested table anymore but an XMLType. You cannot get the error mentioned in the title with XMLType. That is a nested table error. This question is for nested table. For XMLType if you are getting any new error, post it as a new question along with that error. Please note that I am still unable to create the table due to error in Register Schema step. ORA-01741: illegal zero-length identifier ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 3 ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 14 ORA-06512: at line 3 – Aigrette

The question is totally right... Oracle stores the XMLType column as a nested table... The question is that I want to access the nested table created by the system directly. – Rutherfordium

I have just followed all the steps I have indicated throughout the question. It is right. I am using Oracle 12c.... pehaps it does not work in previous versions of the system. – Rutherfordium

More specifically... I need to access the varray created by the system. – Rutherfordium

Sorry. My bad. Got lost in between. Can you please try this? SELECT * FROM TABLE( SELECT "FV"."XMLDATA"."feature" FROM FV_XML_12_1000); – Aigrette

I had tried it already... generates the error: line 1: ORA-00904: "FV"."XMLDATA"."feature": invalid identifier – Rutherfordium

I'm intrigued that this is still getting up-votes when it clearly doesn't work. Both the answer and the last version in comments - if that was modified to include a table alias to fix the invalid identifier problem - would get an error from the subquery returning more than one row (unless there is only one row in the table). – Dolly