jtittler
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_CO LL
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_C OLL"),
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_TEMPL ATE">
<xs:sequence>
<xs:element name="column" type="idb:fileColumn" maxOccurs="unbounded" xdb:SQLName="file_column_c oll"/>
<xs:element name="rule" type="idb:fileRule" minOccurs="0" maxOccurs="unbounded" xdb:SQLName="rule_definiti on_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
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_CO
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
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_C
CAST(MULTISET(SELECT XM_MODEL_ASSOCIATION(NULL,
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_TEMPL
<xs:sequence>
<xs:element name="column" type="idb:fileColumn" maxOccurs="unbounded" xdb:SQLName="file_column_c
<xs:element name="rule" type="idb:fileRule" minOccurs="0" maxOccurs="unbounded" xdb:SQLName="rule_definiti
<xs:element name="model_association" type="idb:associationType"
</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
</xs:complexType>
Thank you kindly,
Julie
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Vee_Mod
Community Support Moderator