Oracle Data Service Integrator Documentation > Data Services Developer's Guide
Constraints on Publishing Data Service Objects to SQL
There are some semantic and structural constraints to publishing data service objects to SQL.
Semantic constraints include some general types of objects as private functions.
 | See Mapping Rules for a matrix showing publishable Oracle Data Service Integrator object types and their corresponding SQL object types. |
The following table outlines the structural constraints on publishing data service artifacts to SQL.
| Limitation | Discussion |
|---|---|
| Limitation affecting all SQL objects | Limitations in this section affect publication to any type of SQL object. |
| Functions referring to types that are neither simple nor elements | Examples of such types include item, node, and attribute. |
| Functions with simple types that have no corresponding SQL type | The simple type on the XQuery side must correspond with a JDBC-supported SQL type, such as QName for example. |
| Functions with anonymous element types | Functions containing elements where the name is not defined are not mapable. For example: declare function f() as element() |
| Functions declarations using recursive XML types | For example, a function declaration with a complex type (PersonType) containing an element that is also of type PersonType is not mapable, as shown by the following: <element name="PERSON" type="tns:PersonType"/> <complexType name="PersonType"> <sequence> <element name="first_name" type="string"/> <element name="last_name" type="string"/> <element name="contact" type="tns:PersonType"/> </sequence> </complexType> |
| XML types with content models containing wildcards | XML wildcards include: • xs:any • xs:anyAttribute |
| XML types with mixed content | An example of a document containing mixed content is: <a>
<child/>
this is simply text
<child/>
</a>
|
| Limitations affecting publishing as a SQL Table | Limitations in this category affect publishing as SQL tables |
| Functions with parameters | Functions with parameters can be mapped as stored procedures. |
| Functions containing simple return types | Functions containing simple return types can be mapped as SQL functions. |
| Functions containing any non-tabular element type | See How Non-Tabular Element Types Affect the Ability to Publish Functions as SQL Objects. Also applies to stored procedures. |
| Functions with any AtomicType types | Also applies to stored procedures. |
| Limitations affecting publishing as a stored procedure |
Limitations in this category affect publishing as a stored procedure |
| Functions accepting element parameter types | These functions cannot be published as stored procedures. |
| Functions containing a sequence of simple return types, such as xs:string* |
The function declaration is not eligible. For example: declare function f($p as xs:string*) as xs:int
|
| Functions with anyAtomicType types | Also applies to tables. |
| Functions with any non-tabular element types | See How Non-Tabular Element Types Affect the Ability to Publish Functions as SQL Objects. Also applies to tables. |
| Limitations affecting publishing as a SQL Function |
Limitations in this category affect publishing as a SQL functions |
| Function with a sequence parameter type and an arity greater than 1. | An example shows xs:int* as the sequence parameter type: declare function f($p as xs:int*, $q as xs:string) as xs:int |
| Functions with element types | declare function f ($p as element(e)) as xs:int
|
How Non-Tabular Element Types Affect the Ability to Publish Functions as SQL Objects
The structure of a data service function determines whether it can be mapped to an SQL object or not. For example, a parameterized function cannot be published as an SQL table since by definition SQL tables do not take parameters. Some structural constraints are practically self-evident; others are less obvious.
|
A quick way to determine if a particular function can be published to a particular type of SQL object is to drag the function to a SQL object table, stored procedure, or functions folder. Even if the function is grayed out — meaning that it cannot be published to any type of SQL object — an alert dialog will appear explaining why the selected object cannot be published. |
For example, functions with non-tabular element types cannot be published as tables or stored procedures because XML output structure cannot be mapped to a normalized SQL table.
Underlying each data service is an XML type, or schema. Some XML types are readily mapped for JDBC use because they are — like SQL tables — two dimensional.
<CUSTOMER> <FIRST_NAME> <LAST_NAME> <CUSTOMER_ID> </CUSTOMER>
When published as SQL, the table structure corresponds to the following:
| FIRST_NAME | LAST_NAME | CUSTOMER_ID |
|---|---|---|
| Jack | Black | CUSTOMER1 |
As long as the object mapper can reduce the structure of the XML document to rank-one, the mapping
can occur. For example:
<CUSTOMER>
<FIRST_NAME>
<LAST_NAME>
<CUSTOMER_ID>
<CUSTOMER_ORDER>
<ORDER_ID>
<C_ID>
<ORDER_DT>
</CUSTOMER_ORDER>
</CUSTOMER>
is publishable as a table in the following form as long as there is one or fewer customer orders
associated with the customer:
| FIRST_NAME | LAST_NAME | CUSTOMER_ID | ORDER_ID | C_ID | ORDER_DT |
|---|---|---|---|---|---|
| Jack | Black | CUSTOMER1 | ORDER_1_0 | CUSTOMER1 | 2001-10-01 |
If, however, the CUSTOMER_ORDER type is unbounded, meaning that it can represent more than one order associated with a single customer, the structure no longer corresponds to a well-formed relational table and the mapping is not allowed.
