XMLTYPE NESTED TABLE

From: jacquesh <jhullu_at_gmail.com>
Date: Sun, 01 Jul 2007 09:29:19 -0000
Message-ID: <1183282159.928071.103050@q75g2000hsh.googlegroups.com>

Hello,

I've a problem with this scenario ...

My Schema:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb"

    version="1" xdb:storeVarrayAsTable="true">     <!-- racine du document -->
    <xs:element name="FIELDS" xdb:defaultTable="ACLFIELDS">

        <xs:complexType xdb:SQLType="ACLFIELDS_T">


            <xs:sequence>

                <xs:element ref="XMLNAME" minOccurs="1"
maxOccurs="unbounded"/>


            </xs:sequence>

        </xs:complexType>

    </xs:element>
    <xs:element name="XMLNAME" xdb:SQLCollType="XMLNAME_V" xdb:SQLName="XMLNAME_VARRAY">

        <xs:complexType xdb:SQLType="XMLNAME_T">


            <xs:sequence>

                <xs:element ref="PROFILE" maxOccurs="1" minOccurs="1"/
>


            </xs:sequence>

            <xs:attribute name="ID" use="required" type="xs:string"

xdb:SQLName="FIELD_NAME"/>


            <xs:attribute name="TYPE" use="required"

type="type_control"
                xdb:SQLName="FIELD_TYPE"/>
        </xs:complexType>

    </xs:element>
    <xs:element name="PROFILE" xdb:SQLName="PROFILE">

        <xs:complexType xdb:SQLType="PROFILE_T">


            <xs:sequence maxOccurs="1" minOccurs="1">

                <xs:element ref="AU" maxOccurs="1" minOccurs="1"/>
                <xs:element ref="CM" maxOccurs="1" minOccurs="1"/>
                <xs:element ref="CT" maxOccurs="1" minOccurs="1"/>
                <xs:element ref="EC" maxOccurs="1" minOccurs="1"/>
                <xs:element ref="MA" maxOccurs="1" minOccurs="1"/>
                <xs:element ref="OT" maxOccurs="1" minOccurs="1"/>


            </xs:sequence>

        </xs:complexType>
    </xs:element>
    <xs:element name="OT" type="type_access" xdb:SQLName="OT"/>
    <xs:element name="MA" type="type_access" xdb:SQLName="MA"/>
    <xs:element name="EC" type="type_access" xdb:SQLName="EC"/>
    <xs:element name="CT" type="type_access" xdb:SQLName="CT"/>
    <xs:element name="CM" type="type_access" xdb:SQLName="CM"/>
    <xs:element name="AU" type="type_access" xdb:SQLName="AU"/>

    <!-- def. des types -->
    <xs:simpleType name="type_access">

        <xs:restriction base="xs:string">


            <xs:enumeration value="V"/>

            <xs:enumeration value="W"/>

            <xs:enumeration value="H"/>

        </xs:restriction>

    </xs:simpleType>
    <xs:simpleType name="type_control">

        <xs:restriction base="xs:string">


            <xs:enumeration value="LBL"/>

            <xs:enumeration value="CHK"/>

            <xs:enumeration value="RAD"/>

            <xs:enumeration value="LST"/>

            <xs:enumeration value="TXT"/>

        </xs:restriction>
    </xs:simpleType>
</xs:schema>

BEGIN
    DBMS_XMLSCHEMA.REGISTERSCHEMA(

        schemaurl => 'http://xxx/projet/yyy/schema/aclfields.xsd',
        schemadoc => sys.UriFactory.getUri('http://xxx:xxx@localhost:
8082/home/yyy/xsd/fields.xsd'),
        local => TRUE,
        gentypes => TRUE,
        genbean => FALSE,
        gentables => TRUE,
        force => FALSE,
        owner => 'D47');

END;
/

commit ;

• Table DROP TABLE FIELDS ; CREATE TABLE FIELDS ( ID NUMBER, NOM VARCHAR2(25 CHAR), ACL_DEF XMLTYPE, COD_HOP VARCHAR2(5 CHAR)) XMLTYPE COLUMN ACL_DEF XMLSCHEMA "http://xxx/projet/yyy/schema/aclfields.xsd" ELEMENT "FIELDS" VARRAY ACL_DEF."XMLDATA"."XMLNAME" STORE AS TABLE "XMLNAME_TABLE" ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)) ORGANIZATION INDEX OVERFLOW) /

Table creee.

All is ok... but when i do

 SQL> desc XMLNAME_TABLE

 Nom                                       NULL ?   Type
 ----------------------------------------- --------
----------------------------
 SYS_XDBPD$                                         XDB.XDB$RAW_LIST_T
 FIELD_NAME                                         VARCHAR2(4000
CHAR)
 FIELD_TYPE                                         XDB.XDB$ENUM_T
 PROFILE                                            PROFILE_T

SQL> select * from XMLNAME_TABLE ;
select * from XMLNAME_TABLE

              *
ERREUR a la ligne 1 :
ORA-22812: impossible de referencer la table de stockage de la colonne de table
imbriquee

why ?? any idea..

thanks Received on Sun Jul 01 2007 - 04:29:19 CDT