Link to home
Create AccountLog in
Avatar of jtittler
jtittlerFlag for United States of America

asked on

Object view problems based on objects created during schema registration. Oracle 10.2.0.1

I am trying to create an object view based on objects created by registering a schema. Strangely enough, I am having problems with the OBJECT ID part of the creation DDL. I'm using Oracle 10.2.0.1 on Windows XP. The select statement that the create view DDL is based on runs correctly if run independently through SQLPLUS. It doesn't like the OBJECT ID phrase in the CREATE VIEW DDL though. It gives me an "Invalid identifier" error. No matter which column of the XM_FILE_TEMPLATE object type I use here, it doesn't work. Here is the XM_FILE_TEMPLATE definition and the CREATE VIEW statement. This view is so complex because it's going to be the basis for an XMLType view.

Datatypes:
---------------

xm_file_template is NOT FINAL
Name                                                                                               Null?    Type
-------------------------------------------------------------------------------------------------- -------- -------------------------
SYS_XDBPD$                                                                                               XDB.XDB$RAW_LIST_T
name                                                                                                     VARCHAR2(30 CHAR)
data_template_name                                                                                       VARCHAR2(30 CHAR)
file_column_coll                                                                                         file_column_coll296_COLL
rule_definition_coll                                                                                     rule_definition_coll297_COLL
model_coll                                                                                               model_coll298_COLL

SQL:
--------------------
CREATE OR REPLACE VIEW ov_file_templates
      OF XM_FILE_TEMPLATE
      WITH OBJECT ID (NAME, DATA_TEMPLATE_NAME)
AS
      SELECT XM_FILE_TEMPLATE(
            NULL,
            F.FILE_TEMPLATE_NAME,
            D.DATA_TEMPLATE_NAME,
            CAST(MULTISET(SELECT XM_FILE_COLUMN(NULL,
                                           XM_PHYSICAL_COL_REF(NULL, FC.COLUMN_NM, DT.TABLE_SUFFIX),
                                           FC.COLUMN_TEXT,
                                           XM_DELIMITED_COL(NULL, FC.COLUMN_TYPE, FC.COLUMN_INDEX, FC.COLUMN_SIZE, FC.COLUMN_DELIMITER, FC.COLUMN_XML_TYPE))
                                FROM IDB.FILE_COLUMN FC JOIN IDB.DATA_TEMPLATE_TABLE DT ON (FC.DATA_TEMPLATE_TABLE_ID = DT.DATA_TEMPLATE_ID))
                   AS "file_column_coll296_COLL"),
            CAST(MULTISET(SELECT  XM_FILE_RULE(NULL, FR.RULE_DEFINITION, FR.RULE_TYPE) FROM IDB.FILE_TEMPLATE_RULES FR WHERE (FR.FILE_TEMPLATE_ID = F.FILE_TEMPLATE_ID))
                  AS "rule_definition_coll297_COLL"),
            CAST(MULTISET(SELECT XM_MODEL_ASSOCIATION(NULL, M.MODEL, MFC.NAME)  
                                FROM IDB.MODEL_FILE_TEMPLATES MF JOIN IDB.MODEL M ON (MF.MODEL_ID = M.MODEL_ID)
                                JOIN IDB.MANUFACTURER MFC ON (M.MANUFACTURER_ID = MFC.MANUFACTURER_ID)
                                WHERE MF.FILE_TEMPLATE_ID = F.FILE_TEMPLATE_ID)
                   AS "model_coll298_COLL"))
            FROM IDB.FILE_TEMPLATE F NATURAL JOIN IDB.DATA_TEMPLATE D;

Any ideas why the error is happening? I'm quite stumped. Help here would be appreciated. I have a couple of other related questions as well:

1. Why is it that the default constructor for the objects created during schema registration need the NULL argument? It maps to SYS_XDBPD$. I thought that was a field used by Oracle only.
2. During schema registration, a few datatypes are auto-generated to match some collections in the schema, for example: "model_coll298_COLL". I can't seem to figure out how to specify a name for that SQLType by annotating the schema. It would be helpful if I could so I could create DB generation scripts. If I have to register schemas by hand, figure out what object types it created with made up names, and then build the views by hand...that's not very good for a set of install scripts. blech... Here's the section of XSD which deals with the XM_FILE_TEMPLATE SQLType:

        <xs:complexType name="fileTemplateType"  xdb:SQLType="XM_FILE_TEMPLATE">
            <xs:sequence>            
                  <xs:element name="column" type="idb:fileColumn" maxOccurs="unbounded" xdb:SQLName="file_column_coll"/>
                  <xs:element name="rule" type="idb:fileRule" minOccurs="0" maxOccurs="unbounded" xdb:SQLName="rule_definition_coll"/>
                  <xs:element name="model_association" type="idb:associationType" minOccurs="0" maxOccurs="unbounded" xdb:SQLName="model_coll"/>
            </xs:sequence>
            <xs:attribute name="name" type="idb:string30" use="required" xdb:SQLType="VARCHAR2" xdb:SQLName="name"/>
            <xs:attribute name="dataTemplateName" type="idb:string30" use="required" xdb:SQLType="VARCHAR2" xdb:SQLName="data_template_name"/>
      </xs:complexType>

Thank you kindly,
Julie
ASKER CERTIFIED SOLUTION
Avatar of jtittler
jtittler
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator