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
jtittlerAsked:
Who is Participating?
 
jtittlerAuthor Commented:
Useful notes to others trying to do this stuff:

I figured out some things. If you are annotating a schema for registering into Oracle and have xs:enumeration in there, DO NOT override the default type mapping of VARCHAR2 if it is a string based enumeration. I was annotating the attributes which used my enumeration type with xdb:SQLType="VARCHAR2". So, it mapped it to VARCHAR2(*) which is VARCHAR2(4000). Not good, when you are declaring a restriction of maxLength of 1 in the enumeration. The default datatype mapping is a XDB.XDB$ENUM_T. This is an object type you can easily generate with XDB.XDB$ENUM_T(table.enumeratedCol)

NOTE #2:

Yes, it says this in the documentation, but I didn't truly grep it until I hit the error my 1st question above is about. The TYPE generation from DBMS_XMLSCHEMA.registerSchema IS CASE SENSITIVE. When referencing the attribute names of the objects it creates during schema registration, if you used lower case or camel case capitalization in the schema, YOU MUST USE DOUBLE QUOTES to reference them in SQL and PLSQL. I found it easier to just turn everything into uppercase.

I'm still at a loss over the type names for the collections. There must be some way to define this via annotation so you know what they are going to be ahead of time.

Julie
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.