10 Extending Database Support

This chapter explains how to extend the database support of Oracle Data Service Integrator. Extensions let you provide immediate, dynamic support for unsupported databases and new versions of supported databases. This chapter explains how to extend database support using a feature called the Configurable Relational Provider.

Note:

A sample Configurable Relational Provider file is provided in this chapter. You can copy the sample and use it as a starting point for creating your own customized provider. See "Sample Configurable Relational Provider File" for the complete listing.

This chapter assumes that you are familiar with XQuery and SQL, especially for more advanced use cases. For suggested background on these subjects with respect to Oracle Data Service Integrator, see Section 10.1.6, "Related Reading."

This chapter includes these topics:

10.1 Introduction

The Configurable Relational Provider lets you extend the database support and functionality of Oracle Data Service Integrator. The Configurable Relational Provider lets you add or modify database support by configuring an XML file, called a "provider." You can configure the XML provider to extend database support for all but a few advanced cases. See Section 10.3, "Using the Configurable Relational Provider" for details.

This section describes the overall framework for extending Oracle Data Service Integrator database support, defines general terms, and lists several use cases for the extension framework.

This section includes these topics:

10.1.1 General Use Cases

This section explains cases where you might consider extending database support using the Configurable Relational Provider.

  • Case 1: Adding extended RDMBS support for your database or, if extended support is provided, customizing or extending that support further.

    If you are using Oracle Data Service Integrator with base platform database support (see Section 10.1.3, "Relational Providers Included With Oracle Data Service Integrator"), it is possible that the database itself can handle more complex constructs, such as expressions and clauses, than are generated by the base platform provider. In this case, users might experience reduced performance. To solve this problem, you can configure an Configurable Relational Provider.

  • Case 2: Adding support for a new version of a core database.

    If a new version of a core database is released, Oracle Data Service Integrator by default treats it the same as the previously supported version. Obviously, with a new release, there may be features that you want to use, such as improved SQL pushdown. In this case, you can update the database support by extending the relational provider for the core database using the Configurable Relational Provider to add the new pushdown features.

  • Case 3: Adding support for a new database that has fewer capabilities than the base platform or is not supported by the core databases.

    Note:

    This use case is uncommon.

    It is possible that you require access to a database that is not supported by Oracle Data Service Integrator core database set (see Table 10-1) and that cannot consume SQL generated by the base platform provider. In this case, you can use the Configurable Relational Provider and either disable unsupported features or add new features as desired.

10.1.2 Overview of the Extension Framework Architecture

The Relational Wrapper Extension Framework lets you add or modify relational database support for Oracle Data Service Integrator. This framework supports the Configurable Relational Provider, which lets you extend database support by editing a configuration file. Figure 10-1 illustrates the architecture of the Relational Wrapper Extension Framework.

Figure 10-1 Database Extension Framework Architecture

Architecture of Relational Wrapper Extension Framework
Description of "Figure 10-1 Database Extension Framework Architecture"

This framework includes a component called a Relational Wrapper that exposes XQuery views of relational sources and executes queries against them. The Relational Wrapper includes the Relational Database Provider Registry, which manages chains of components called relational providers.

The Configurable Relational Provider, which is discussed in detail in this chapter, is an example of a relational provider that you can easily configure and deploy by editing a file. The Configurable Relational Provider is the primary means by which you can extend database support.

  • Defines the SQL and runtime capabilities of a specific database.

  • Allows Oracle Data Service Integrator to handle different databases and their SQL dialects.

  • Returns information about runtime and SQL generation capabilities of the database supported by the provider.

  • Can be extended to add support for new databases and customize support for existing ones.

Inside the provider registry, relational providers are organized into chains. These chains delegate to one another and allow method invocations to be intercepted and processed along the way. Each provider either answers a request or delegates the request to its parent provider. A provider's parent is specified by the <parent> element of the provider's deployment descriptor (see Section 10.4, "Configurable Relational Provider Format Description and Reference").

As shown in Figure 10-1, the first chain is assembled from three providers: provider_1, provider_2 and provider_3. When the relational wrapper calls this chain, provider_1 first receives the call and has a choice of either answering it or delegating to its parent provider (provider_2). If provider_1 delegates to provider_2 then it is the responsibility of provider_2 to handle the request. In turn, provider_2 can decide to delegate processing to provider_3. This chain architecture increases system flexibility by supporting modular provider definitions and facilitating easy assembly.

Typically, when you create a provider using the Configurable Relational Provider, you specify a parent provider. The parent provides some features that the child provider can either accept by default or override.

The child provider inherits the features of the parent; however, you can also add features to the child provider that are not implemented in the parent. Usually, one of the abstract providers serves as the parent of the first provider in a chain. See Section 10.11, "Abstract SQL Providers."

By default, the Relational Wrapper Extension Framework supports a core set of databases. See Section 10.1.3, "Relational Providers Included With Oracle Data Service Integrator" for a complete list. Extensibility allows for full support of databases that are not in the core set and allows for support of new versions of the core databases.

For example, a new version of a core database might provide new pushdown capabilities that are not currently recognized by Oracle Data Service Integrator. You can use the extension framework to add the required database support immediately by editing and deploying a Configurable Relational Provider.

10.1.3 Relational Providers Included With Oracle Data Service Integrator

Table 10-1 lists the set of standard relational providers that are included with Oracle Data Service Integrator. Standard providers are implemented using the Relational Wrapper Extension Framework and are registered by default. You can use these providers as a basis for configuring the Configurable Relational Provider.

Table 10-1 Relational Providers Included With Oracle Data Service Integrator

Provider ID Supported Database Type and Version(s) Base Database Version (Decimal)

Oracle-8

Oracle >= 8

8

Oracle-9

Oracle >= 9

9

Oracle-11

Oracle >= 11

11

Oracle-12

Oracle >= 12

12

MSSQL-2000

Microsoft SQL Server >= 2000

8

Version 8 is the product version returned by the JDBC drivers for SQL Server 2000.

DB2-8

IBM DB2 >= 8

8

Sybase-12.5.2

Sybase >= 12.5.2

12.52

Derby

Derby >= 10.6

10.6

Access

Microsoft Access 2003

Microsoft Access support is implemented using the Configurable Relational Provider described in Section 10.3, "Using the Configurable Relational Provider."

4

AbstractSQL, AbstractSQL89, AbstractSQL92

These abstract providers provide base functionality to the Configurable Relational Provider. See Section 10.4, "Configurable Relational Provider Format Description and Reference" for details. See also Section 10.11, "Abstract SQL Providers."

Not applicable.

The abstract providers do not match any databases, and therefore do no return a base version.

The Base Database Version is calculated by the framework. This value specifies the minimum version of a database that a provider can handle. Matching rules are used to determine the value when you pick a provider that best matches your database. For more information on this calculation, see Section 10.5, "Database Matching."

10.1.4 Supported Features

The Configurable Relational Provider supports the following features found in the core relational providers:

  • Database matching

  • Standard JDBC type mapping

  • Join pushdown specification

  • Clause pushdown specification

  • Function and operator pushdown

  • Cast pushdown

  • Auto-generation of fields (usually keys)

  • Stored procedure configuration

  • A subset of runtime properties

Some features defined by the Relational Wrapper Extension Framework are not supported by the Configurable Relational Provider. In such cases, the Configurable Relational Provider delegates the request to its parent provider, which answers it.

The unsupported features include:

  • Data type mapping

  • Data type based matching when pushing down functions and cast operations

  • SQL expression kind matching when pushing down functions and cast operations

10.1.5 Importing Relational Source Metadata

You can import metadata on the data sources needed by your application using the Oracle Data Service Integrator Metadata Import wizard. This wizard introspects available data sources and identifies data objects that can be rendered as data services and functions. The relational provider registry returns a list of providers that best match the database. You can then pick one of these providers (typically, the best match or one close to the best match) from a drop down menu.

The best match appears at the top of the drop down menu. Once created, physical data services become the building-blocks for queries and logical data services. For detailed information on using the Metadata Import wizard, see Creating and Updating Physical Data Services in the Data Services Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Creating and Updating Physical Data Services.html. For information on how matching is performed, see Section 10.5.1, "Rules for Database Matching."

10.1.6 Related Reading

Refer to the following Oracle Data Service Integrator documentation for more information on Oracle Data Service Integrator database, XQuery, and SQL support:

10.2 Sample Configurable Relational Provider File

Example 10-1 shows a sample Configurable Relational Provider file. This sample demonstrates a possible way to configure a custom Microsoft Access provider. You can also find the sample Microsoft Access provider in your Oracle Data Service Integrator installation here:

<ALDSP_HOME>/samples/RelationalAdapter/MS-Access

Copy this sample provider to use as a starting point for creating your own customized provider. Reference information in this chapter explains all of the configurable elements of this XML file. To get started, see Section 10.3, "Using the Configurable Relational Provider."

Example 10-1 Sample Configurable Relational Provider File for a Microsoft Access Database

<?xml version="1.0"?>
<aldsp-rdb-extension xmlns="http://www.bea.com/ns/aldsp/rdb/extension"> 

    <name>MS Access XML Provider</name> 
    <vendor>Oracle</vendor>
    <implementation-version>1.0</implementation-version> 
    <description> MS Access Relational Wrapper Extension </description>


    <rdb-provider>
        <id>MS-Access-2003</id>
        <description>XMLProvider MS Access 2003</description>
        <parent>AbstractSQL</parent>
        <factory 
class="com.bea.dsp.wrappers.rdb.providers.custom.XMLCustomizableProviderFactory> 
  <custom-rdb-provider
    xmlns="http://www.bea.com/ns/aldsp/rdb/extension/custom"
    xmlns:fn="http://www.w3.org/2004/07/xpath-functions"
    xmlns:fn-bea="http://www.bea.com/xquery/xquery-functions"
    xmlns:op-bea="http://www.bea.com/xquery/xquery-operators"
    xmlns:op="http://www.w3.org/2004/07/xpath-operators"
    xmlns:xdt="http://www.w3.org/2004/07/xpath-datatypes"
    xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <database-kind>
     <match-database>
       <![CDATA[
       (jdbc:getDatabaseProductName() eq "ACCESS") and 
       (jdbc:getDatabaseMajorVersion() ge 4)
       ]]>
     </match-database>
     <base-version>4</base-version>
    </database-kind>

    <database-objects>
     <catalog quote="&quot;" separator="." />
     <schema quote="&quot;" separator="." />
     <table quote="&quot;" qualified-name-parts="catalog schema table" />
    </database-objects>


    <joins inner-join="true" outer-join="true">
     <sql92 right-trees="true">
      <inner-join-syntax> 
       {0} INNER JOIN {1} ON {2}
      </inner-join-syntax> 
     </sql92>
    </joins>

    <orderby column="true" expression="true" aggregate="true" null-order="low"/>

    <groupby column="true" expression="true" constant="true"/>

    <subqueries in-from="true" in-where="true" />

    <case supported="false" />

    <functions>

     <!-- String Functions -->
     <function name="fn:concat" supported="true" infix="true" >&amp;</function>
     <function name="fn:string-length" arity="1">LEN({0})</function>
     <function name="fn:lower-case"
     arity="1">IIF(ISNULL(LCASE({0})),'',LCASE({0}))</function>
     <function name="fn:upper-case" supported="true" >
     IIF(ISNULL(UCASE({0})),'',UCASE({0}))</function>
     <function name="fn:substring" arity="2" >
     IIF(ISNULL(MID({0},{1})),'',MID({0},{1}))</function>
     <function name="fn:substring" arity="3" >
     IIF(ISNULL(MID({0},{1},{2})),'',MID({0},{1},{2}))</function>

     <function name="fn-bea:left" >LEFT({0},{1})</function>
     <function name="fn-bea:right" >RIGHT({0},{1})</function>
     <function name="fn-bea:repeat" supported="false" /> 
     <function name="fn-bea:trim" arity="1" >TRIM({0})</function>
     <function name="fn-bea:trim-left" arity="1" >LTRIM({0})</function>
     <function name="fn-bea:trim-right" >RTRIM({0})</function>
     <function name="fn-bea:sql-like" arity="2" >({0} LIKE {1})</function>
     <function name="fn-bea:sql-like" arity="3" supported="false" />
     <function name="fn:starts-with" supported="false" />
     <function name="fn:ends-with" supported="false" />
     <function name="fn:contains" supported="false" />
     <function name="op-bea:string-not-equal" arity="2" >({0} &lt;&gt;
     {1})</function>

     <!-- Numeric Functions -->
     <function name="fn:abs" supported="true" arity="1" >ABS({0})</function>
     <function name="fn:ceiling" supported="false" />
     <function name="fn:floor" supported="false" />
     <function name="fn:round" >ROUND ({0})</function>

     <!-- Aggregate Functions -->
     <function name="fn:count" supported="true" arity="1" >COUNT({0})</function>
     <function name="fn:avg" >AVG({0})</function>
     <function name="fn:min" arity="1" >MIN({0})</function>
     <function name="fn:max" supported="true" arity="1" >MAX({0})</function>
     <function name="fn:sum" arity="1" >
     IIF(ISNULL(SUM({0})),0,SUM({0}))</function>

     <!-- DateTime Functions -->
     <function name="fn:day-from-date" arity="1" >DAY({0})</function>
     <function name="fn:month-from-date" >MONTH({0})</function>
     <function name="fn:year-from-date" >YEAR({0})</function>
     <function name="fn:day-from-dateTime" arity="1" >DAY({0})</function>
     <function name="fn:month-from-dateTime" >MONTH({0})</function>
     <function name="fn:year-from-dateTime" >YEAR({0})</function>
     <function name="fn:hours-from-dateTime" >HOUR({0})</function>
     <function name="fn:minutes-from-dateTime" arity="1" >MINUTE({0})</function>
     <function name="fn:seconds-from-dateTime" >SECOND({0})</function>
     <function name="fn:current-date" supported="false"/>
     <function name="fn:current-time" supported="false"/>
     <function name="fn:current-dateTime" supported="false"/>

    </functions>

    <casts>
     <cast from="xs:string" from-subtypes="true" to="xs:int">
        CINT({0})
     </cast>
     <cast from="xs:double" from-subtypes="true" to="xs:int">
        CINT({0})
     </cast>
     <cast from="xs:float" from-subtypes="true" to="xs:int">
        CINT({0})
     </cast>
     <cast from="xs:decimal" from-subtypes="true" to="xs:int">
        CINT({0})
     </cast>

     <cast from="xs:string" from-subtypes="true" to="xs:double">
        CDBL({0})
     </cast>
     <cast from="xs:decimal" from-subtypes="true" to="xs:double">
        CDBL({0})
     </cast>
     <cast from="xs:string" from-subtypes="true" to="xs:float">
        CDBL({0})
     </cast>
     <cast from="xs:decimal" from-subtypes="true" to="xs:float">
        CDBL({0})
     </cast>

     <cast from="xs:string" from-subtypes="true" to="xs:dateTime">
        CDATE({0})
     </cast>

     <cast from="xs:float" from-subtypes="true" to="xs:string" >
       CSTR({0})
     </cast>
     <cast from="xs:double" from-subtypes="true" to="xs:string" >
       CSTR({0})
     </cast>
     <cast from="xs:decimal" from-subtypes="true" to="xs:string" >
       CSTR({0})
     </cast>
     <cast from="xs:boolean" from-subtypes="true" to="xs:string" >
       CSTR({0})
     </cast>
     <cast from="xs:dateTime" from-subtypes="false" to="xs:string" >
       CSTR({0})
     </cast>
    </casts>

    <limit>
     <select-top />
    </limit>

    <insert>
     <auto-column-generator kind="sql-post" >
      select @@identity
     </auto-column-generator>
    </insert>

    <properties
      supports-multiple-active-queries-per-connection="false"
      supports-cancel-query="false"
      supports-query-timeout="false" />

   </custom-rdb-provider>
        </factory>
    </rdb-provider>
       
</aldsp-rdb-extension>

10.3 Using the Configurable Relational Provider

This section explains how to use the Configurable Relational Provider. The Configurable Relational Provider lets you configure a new relational provider by editing an XML configuration file.

Note:

Be sure to review the section "Introduction" on page 10-2 before continuing.

This section includes the following topics:

10.3.1 Summary of Basic Configuration Steps

This section lists the basic steps required to develop and deploy an Configurable Relational Provider. The basic process of creating a new provider is also shown in Figure 10-2.

Figure 10-2 Custom Provider Development Process

Diagram of custom provider development process.
Description of "Figure 10-2 Custom Provider Development Process"

  1. Choose a base parent provider, such as one of the Abstract providers discussed in Section 10.11, "Abstract SQL Providers." The base provider represents the first provider in a provider chain. Subsequent providers in the chain can extend or override features of a parent provider. See Section 10.1.2, "Overview of the Extension Framework Architecture" for information about provider chains.

  2. Configure one or more Configurable Relational Providers. Configurable Relational Providers are configured in an XML file in which you specify all of the properties of the Configurable Relational Provider(s). See Section 10.2, "Sample Configurable Relational Provider File." The sample is a good starting point for developing your own customized provider.

  3. Deploy the provider. A command line script is provided to deploy your customized provider. See Section 10.3.2, "Deploying the Relational Provider."

  4. Test the provider.

10.3.2 Deploying the Relational Provider

A command-line deployment tool, described in this section, is provided with Oracle Data Service Integrator. Use this tool to add and remove relational providers. To use this deployment tool, your provider's deployment descriptor must be packaged in a JAR file.

Note:

When Oracle Data Service Integrator loads an extension, the deployment descriptor is read and validated. If a provider section of the description is determined to be invalid, it is ignored.

10.3.2.1 Adding a Provider

The command syntax for adding a provider is:

<ALDSP_HOME>/bin/update-providers.[cmd/sh] -add <provider.jar>

The fully-qualified path to the provider relational wrapper extension JAR file is required. When a new provider is added, it is copied into the <ALDSP_HOME>/providers directory.

Note:

Adding or removing a provider requires that you restart the IDE or the server.

10.3.2.2 Removing a Provider

The command syntax for removing a provider is:

<ALDSP_HOME>/bin/update-providers.[cmd/sh] -remove <provider.jar>

Specify the filename of the provider JAR file located in the <ALDSP_HOME>/providers directory. When an existing provider is removed, it is deleted from the <ALDSP_HOME>/providers directory.

Note:

Adding or removing a provider requires that you restart the IDE or the server.

10.4 Configurable Relational Provider Format Description and Reference

This section describes the format, elements, and configurable properties of an Configurable Relational Provider.

This section includes:

A complete provider example is listed in Section 10.2, "Sample Configurable Relational Provider File."

10.4.1 Overview of Primary XML Elements

This section describes each of the primary elements in an Configurable Relational Provider file. This file is a deployment descriptor that is used to specify the properties of the relational provider extension.

Note:

The file must be packaged and deployed in a JAR file. The JAR must only contain one deployment descriptor; however, the descriptor can define and configure one or more providers. See Section 10.3.2, "Deploying the Relational Provider."

The following list describes the primary elements of a relational provider deployment descriptor.

  • <name> – The name of the provider.

  • <vendor> – (Optional) The name of the vendor of the provider.

  • <implementation-version> – (Optional) A version number for the provider.

  • <description> – (Optional) A brief description of the extension.

  • <id> – The provider ID. This ID is used to register the provider in the provider registry.

  • <description> – (Optional) A brief description of the provider.

  • <parent> – (Optional) The <id> element of a parent provider.

    Note:

    You must name the deployment descriptor file aldsp-rdb-extension.xml.

In the sample file in Section 10.2, "Sample Configurable Relational Provider File", the class specified by the <parent> element is AbstractSQL. See Section 10.11, "Abstract SQL Providers" for detailed information on this abstract provider parent class.

  • <modifier> – (Optional) Either abstract or final. If set to abstract, the provider cannot be referred to by any data service; however, an abstract provider can be extended (be the parent of another provider). If set to final, the provider cannot be extended by any other providers.

  • <factory> – (Optional) This element specifies a factory class that instantiates the provider. The Configurable Relational Provider uses the default factory class, ,XMLCustomizableProviderFactory.

    In the sample file in Section 10.2, "Sample Configurable Relational Provider File", the <factory> element explicitly specifies the default factory class, XMLCustomizableProviderFactory.

  • <custom-rdb-provider> – A sub-element that specifies the namespace of the Configurable Relational Provider and its full configuration. The default namespace is: http://www.bea.com/ns/aldsp/rdb/extension/custom.

    For details on configuring the <custom-rdb-provider> element, see Section 10.2, "Sample Configurable Relational Provider File" and Section 10.4, "Configurable Relational Provider Format Description and Reference."

    Note:

    When Oracle Data Service Integrator loads an extension, the deployment descriptor is read and validated. If a provider section of the description is determined to be invalid, it is ignored.

10.4.2 Overview of the <custom-rdb-provider> Element

Example 10-2 shows the basic configuration of the <custom-rdb-provider> element in an Configurable Relational Provider. This configuration is based on a schema file that is provided with Oracle Data Service Integrator.

Each of the properties are described in greater detail in Section 10.4.3, "Configurable Relational Provider Reference." For a complete example, see Section 10.2, "Sample Configurable Relational Provider File."

Example 10-2 Overview of the <custom-rdb-provider> Element

<custom-rdb-provider xmlns="http://www.bea.com/ns/aldsp/rdb/extension/custom">

    <database-kind>
        <match-database>
            XQuery expression that uses a predefined external function to 
            Access JDBC metadata. Result type: boolean
        </match-database> 
        <base-version> 
            Base database version supported by this provider (decimal)
        </base-version> 
        <matched-version> 
            XQuery expression returning matched version. Result type: decimal
        </matched-version> 
    </database-kind> 

    <database-objects>
        <catalog   quote?="string" separator?="string"/>
        <schema    quote?="string" separator?="string" />
        <table     quote?="string" separator?="string" 
                                   qualified-name-parts="string*"/>
        <column    quote?="string" />
        <procedure quote?="string" qualified-name-parts="string*"/>
    </database-objects> 

    <joins inner-join="boolean" outer-join="boolean">
        <sql92 right-trees="boolean(:=true)" />  or 
        <sql89 outer-join-kind?="columnModifier|tableModifier" 
               outer-join-modifier?="string" /> 
    </joins> 

    <orderby column?="boolean" expression?="boolean" aggregate?="boolean"
             null-order?="low|high|first|last|undefined"
             style?="ordering-expression|ordering-expression-with-projection|
                position-in-project-list" />

    <groupby column?="boolean" constant?="boolean" expression?="boolean" /> 

    <subqueries in-from?="boolean" in-where?="boolean" /> 

    <case supported?="boolean(:=true)" /> 

    <functions default-syntax-for-empty-input="lax|strict|strict-coalesce">
        <function name="QName" arity?="integer" supported?="boolean(:=true)" 
           infix?="boolean(:=false)">
           SQL expression which uses {0},{1},…{n} for input expressions
           (string)
        </function> 
    </functions> 

    <casts>
        <cast from="QName" from-subtypes?="boolean(:=false)" to="QName"
              supported?="boolean(:=true)">
            SQL expression which uses {0} for input expression
        </cast> 
    </casts> 

    <limit supported?="boolean(:=true)">
        <top parameter="true|false" composable="true|false"/> or
        <rownum kind="project_first|filter_first">
            ROWNUM
        </rownum>
    </limit>

    <insert>
        <key-gen kind?="jdbc|sql-pre|sql-post">
             SQL statement 
        </key-gen>
    </insert> 

    <properties
         supports-query-timeout = "boolean"
         supports-cancel-query  = "boolean" 
         supports-multiple-active-queries-per-connection = "boolean"
    /> 
 
</custom-rdb-provider>

10.4.3 Configurable Relational Provider Reference

Table 10-2 describes each of the sub-elements and properties of the <custom-rdb-provider> element of an XML Customization Provider configuration file.

For a summary of the file format, see Section 10.4.2, "Overview of the <custom-rdb-provider> Element." For a complete example, see Section 10.2, "Sample Configurable Relational Provider File."

Most of the settings listed in Table 10-2 are optional. Any settings that are specified in the configuration file override default settings provided by the parent provider. The parent provider is specified with the <parent> element of the descriptor.

If no setting is provided for an attribute, then the request is delegated to the parent provider. See Section 10.1.2, "Overview of the Extension Framework Architecture" for a description of the way in which providers delegate to parent providers in a "chains."

Table 10-2 Configuration Elements and Attributes Description

Element(s) and Sub-element(s) Attribute(s) Description of Element or Attribute

<match-database>

<matched-version>

  

These elements contain XQuery expressions that can access JDBC database metadata through predefined external functions. See Section 10.5, "Database Matching."

There are no default values for these elements. Default values are inherited from the parent provider.

<database-objects>

  

Sub-elements of this element specify various properties of database object identifiers in the generated SQL.

<database-objects>

<catalog>

<schema>

<table>

<column>

<procedure>

quote

The quote attribute specifies the identifier quote for the corresponding database object.

Example: <catalog quote="'" />

To specify open and close quotes, specify first the open quote, then the close quote.

Example: <table quote="[]" />

The general rule is: if the number of characters in the specified quote string is even – then it is assumed that open and close quotes are different. The first half of the specified string is the open quote; the second half is the close quote. If the number of characters in the specified string is odd then it is assumed that the open and close quotes are the same and equal to the whole string.

<database-objects>

<catalog>

<schema>

<table>

<procedure>

separator

The separator attribute specifies the separator character between object identifiers in the fully qualified object name.

Example: <schema separator="."/>

If this attribute is not specified, the parent provider's value is used by default.

<table>

<procedure>

qualified-name-parts

The qualified-name-parts attribute specifies a list of object kinds that specify how a fully qualified name is constructed for this database object.

Note: Object kinds in the list must be separated by a space character.

Example: <table qualified-name-parts="catalog schema table" />

Example: <procedure qualified-name-parts="schema procedure"/>

If this attribute is not specified, the parent provider's value is used by default.

<joins>

inner-join

outer-join

These attributes are booleans that specify whether the database supports inner and outer joins respectively.

The exact join syntax is defined by the sql92 and sql89 child elements of the joins element.

Example: <joins inner-join="true" outer-join="true">

If these attributes are not specified, the parent provider's values are used by default.

<joins>

<sql92>

  

The sql92 sub-element specifies that the database uses SQL-92 syntax for joins. For example: SELECT ... FROM a INNER JOIN | LEFT OUTER JOIN b ON ...

<joins>

<sql92>

right-trees

This attribute is a boolean that determines whether parenthesis can be used to control the order of joins in the join clause.

Default: true

<joins>

<sql92>

inner-join-syntax

(Optional) Defines the syntax for an inner join. {0} is used for the left branch source, {1} for the right branch source, and {2} for a join condition expression.

Example: {0} JOIN {1} ON {2}

<joins>

<sql92>

outer-join-syntax

(Optional) Defines the syntax for a left outer join. {0} is used for the left branch source, {1} for the right branch source, and {2} for the join condition expression.

Example: {0} LEFT OUTER JOIN {1} ON {2}

<joins>

<sql89>

  

The sql89 sub-element specifies that the database uses SQL-89 syntax for joins. For example: SELECT ... FROM a,b WHERE ...

<joins>

<sql89>

inner-join-syntax

(Optional) Defines the syntax for a left inner join. {0} is used for the left branch source, {1} for the right branch source.

Default: {0}, {1}

<joins>

<sql89>

outer-join-syntax

(Optional) Defines the syntax for a left outer join. {0} is used for the left branch source, {1} for the right branch source.

Example: {0}, OUTER {1}

Default: empty (left outer join is not supported)

<joins>

<sql89>

outer-join-right-branch-column-modifier

(Optional) Specifies the transformation to be applied to the columns on the right side of a left outer join. {0} is used to specify the right-side column.

Example: {0}(+)

Default: empty (no transformation is required)

<orderby>

column

expression

This boolean attribute specifies whether the database supports orderby column and other expressions.

If these attributes are not specified, the parent provider's values are used by default.

<orderby>

aggregate

This boolean attribute specifies whether the database supports orderby aggregate.

<orderby>

null-order

This attribute specifies one of the following values:

  • low – NULL values are sorted low.

  • high – NULL values are sorted high.

  • first – NULL values are sorted at the start regardless of sort order.

  • last – NULL values are sorted at the end regardless of sort order.

  • undefined – NULL values are sorted by Oracle Data Service Integrator ("order by" is not pushed to the database in this case).

If this attribute is not specified, the parent provider's values are used by default.

<orderby>

style

Style of the orderby expressions that will be generated:

  • position-in-project-list – Generates ORDER BY n, where 'n' is a position of the ordering expression in the SELECT clause. The ordering expression is automatically added to the SELECT clause if necessary.

  • ordering-expression-with-projection – Generates ORDER BY <expr> where <expr> is automatically added to the SELECT clause if necessary.

  • ordering-expression – Generates ORDER BY <expr> where <expr> is not automatically added to the SELECT clause.

There is no default value for this attribute. The parent provider's value is used if not specified.

<groupby>

column

constant

expression

These boolean attributes specify whether the group by clause can operate on columns, constants, and expression.

If these attributes are not specified, the parent provider's values are used by default.

<subqueries>

in-from

in-where

These boolean attributes specify whether subqueries are supported in FROM and WHERE clauses. Oracle Data Service Integrator generates only a subquery in the WHERE clause only when translating a semi-join.

Example: "WHERE EXITS(...))")

If these attributes are not specified, the parent provider's values are used by default.

<case>

supported

This boolean attribute specifies whether the CASE expression is supported.

Default: true

<functions>

  

This element defines SQL syntaxes for functions.

<functions>

default-syntax-for-empty-input

An enumeration of strings that define which default syntax to use in the presence of NULL input. NULL (an empty sequence in XQuery) input is usually handled differently by SQL and XQuery functions. In SQL, NULL is usually propagate to the output of a function. For example: f(NULL)=NULL). In XQuery, however, NULL is usually replaced with a value. For string functions, such as f( () ) = "", sum( () )=0, and so on. This setting specifies how to deal with such situations when choosing default SQL syntax for a function.

This attribute must specify one of the following values:

  • strict – Follow XQuery semantics. Do not push down if the input can be empty.

  • strict–coalesce – (Default) Follow XQuery semantics. Push down with the help of the COALESCE function in SQL. Only use this value if the database supports the COALESCE function.

  • lax – Do not follow XQuery semantics. Generate SQL without the COALESCE function, such that f(NULL) -> NULL.

See Section 10.7, "Default SQL Syntax for Functions."

Default: strict-coalesce

<functions>

<function>

  

This sub-element defines the translation of an XQuery function(operator) into SQL.

The contents of this sub-element is a SQL expression that must be generated for the named function. Parameters are specified as {0}, {1}, … {n}.

A variable number of parameters is supported. See Section 10.6, "Specifying SQL Syntax for Functions" for more information on the format.

This element is not required if the supported attribute is set to false.

The contents of this element can be empty. In this case, the default syntax for this function is used for SQL generation. A list of default syntaxes is provided in Section 10.7, "Default SQL Syntax for Functions."

For examples, see Section 10.2, "Sample Configurable Relational Provider File."

<functions>

<function>

name

(Required) Specifies the QName of a function. See Section 10.10, "Function and Type Name Resolution Process."

<functions>

<function>

arity

Specifies the arity of the named function. Can be omitted if function name is non-ambiguous.

<functions>

<function>

supported

(Boolean) specifies whether the function pushdown is supported or not. Disables the pushdown of a function defined by the parent provider.

Default: true

<functions>

<function>

infix

(Boolean) Specifies whether or not to use infix formatting style for this function. A SQL expression in the sub-element contents specifies the only infix operation in this case. Parameters are processed automatically.

<casts>

  

This element defines cast operations for push down.

<casts>

<cast>

  

Defines translation for cast operations to SQL for a particular combination of types.

The content of this element is the SQL expression that must be generated for this cast operation. The parameter is specified as {0}.

This element is not required if the supported attribute is false.

For examples, see Section 10.2, "Sample Configurable Relational Provider File."

<casts>

<cast>

to

from

These attributes specify the QNames of input and target XQuery types. If only a local name is specified, Oracle Data Service Integrator searches for the type in well-known namespaces.

For examples, see Section 10.2, "Sample Configurable Relational Provider File." See also Section 10.9, "Standard and Oracle Data Service Integrator Namespaces for Functions and Types."

<casts>

<cast>

from-subtypes

(Boolean) Specifies whether the matching input type must also match its subtypes (according to XQuery type hierarchy).

Default: false.

For examples, see Section 10.2, "Sample Configurable Relational Provider File."

<casts>

<cast>

supported

(Boolean) Specifies whether this cast operation is supported. Intended usage is to disable cast pushdown of the parent provider.

Default: true

<limit>

  

This element defines the pushdown of fn:subsequence(). This element must have one child element specified. To disable pushdown of this function, set supported to false.

<limit>

supported

(Boolean) Specifies whether the database supports fn:subsequence() pushdown.

Default: true

<limit>

<select-top>

  

Specifies that fn:subsequence() must be pushed down using the TOP modifier of the SELECT clause. For example: SELECT TOP n FROM ...

The content of the select-top element defines SQL syntax for the select clause modifier. {0} is bound to the length expression.

Default content value: TOP {0}

<limit>

<select-top>

parameter

(Boolean) Specifies whether the TOP value can be a parameter. For example, whether SELECT TOP ? FROM ... is supported by the database.

Default: false

<limit>

<select-top>

composable

If set to true, specifies whether to stop SQL generation after processing fn:subsequence(). If set to false, continues by creating a subquery for a SELECT TOP ... statement.

Default: false

<limit>

<row-number-function>

  

Specifies that the fn:subsequence() is a pushdown using a ROWNUM-like function.

The content of this element defines the SQL syntax for ROWNUM-like functions supported by the database. The content portion is optional.

Default content: ROW_NUMBER() Over(...)

<limit>

<row-number-function>

explicit-order-by

(Boolean) Determines whether ORDER BY ordering expressions will be passed as arguments to the ROW_NUMBER function.

<limit>

<row-number-function>

split-range-filter

(Boolean) Determines whether the range test should be split between subqueries. (Oracle ROWNUM pattern)

Default: false

<limit>

<limit-clause>

  

Specifies that fn:subsequence() should be translated into SQL as a LIMIT-like clause added at the end of a SQL query.

Content of the <limit-clause> element defines SQL syntax for this clause, where {0} and {1} placeholder bindings depend on the @style attribute (see below).

Content value is optional.

Default content value: LIMIT {0} OFFSET {1}

<limit>

<limit-clause>

kind

Defines kind of the accepted subsequence() function:

  • Range - default - both start and length expression are used. In this case limit clause syntax has {0} parameter bound to the start expression and {1} to the length expression

  • Top - only top-like subsequence() is accepted for pushdown. start expression has to be constant 1. In this case limit clause syntax has only {0} parameter which is bound to the length expression

Default value: range

<limit>

<limit-clause>

parameter

(Boolean) Specifies whether SQL parameters can be used in limit clause (as start and/or length expressions)

Default value: true

<limit>

<limit-clause>

composable

(Boolean) Specifies whether SQL generation should stop after processing fn:subsequence() (when set to false), or can continue by creating subquery for SELECT … LIMIT statement (when set to true).

Default: false

<limit>

<limit-clause>

start-base

Integer. 0 or 1. Defines whether start expression is 0 or 1 - based. Only applicable when @style = range

Default: 0

<insert>

<auto-column-generator>

  

Defines a strategy to access auto-generated columns when inserting data into the database.

Strategy kind is defined by the kind attribute.

The content of this element is a SQL expression for certain kinds and empty for others.

Example:

<insert> <auto-column-generator kind="sql-post">

SELECT LAST_INSERT_ID()

</auto-column-generator></insert>

<insert>

<auto-column-generator>

kind

A string constant (enumeration) that defines the key generation strategy. This attribute must specify one of the following values:

  • jdbc – (Default) Defines the key generation strategy through the JDBC API. Content of the key-gen element must be empty in this case.

  • sql-pre – Run a specified SQL statement to get the auto-generated key before issuing an INSERT statement. For example, use this attribute to get a key from a sequence-like database object. In this case, the content of the key-gen element is the SQL statement that can use {0} as a placeholder for the sequence object name (specified by the dataservice's annotation).

  • sql-post – Run a specified SQL statement to get the auto-generated key after an INSERT statement. The content of the key-gen element is the SQL statement that must be executed.

properties

  

The attributes of this element contain various SQL generation and execution properties. These properties do not have default values; the parent's value is used if a property is not set.

<properties>

multiple-active-queriesper-connection-supported

(Boolean) Specifies whether the database supports multiple active statements open on the same connection.

<properties>

cancel-query-supported

(Boolean) Specifies whether the jdbc.sql.Statement.cancel() method is supported by the database and driver.

<properties>

query-timeout-supported

(Boolean) Specifies whether the jdbc.sql.Statement.setQueryTimeout() method is supported by the database and driver.

10.5 Database Matching

This section describes how Oracle Data Service Integrator determines the best database match for a given provider. Database matching logic is specified as an XQuery expression that can access JDBC database metadata through predefined XQuery external functions.

Matching expressions are specified in the Configurable Relational Provider elements (Table 10-2) and are evaluated by the Oracle Data Service Integrator XQuery engine. Expressions can use standard XQuery functions supported by the Oracle Data Service Integrator XQuery engine as well as additional functions defined by the Configurable Relational Provider. Database matching XQuery expressions return an xs:boolean value.

Another use of matching XQuery expressions is to compute the matched database version (in this case the result must be xs:decimal).

This section includes these topics:

10.5.1 Rules for Database Matching

The framework employs matching rules to determine if a given provider is compatible with a database. During the metadata import process (see Section 10.1.5, "Importing Relational Source Metadata") the relational provider registry determines which providers support the database being imported. For successful matches, the base version offset is also obtained. The base version offset is calculated as:

Base version offset (decimal) = (matched db version – base db version returned by the provider)

Base version decimals for the standard providers are listed in Table 10-2 in the section Section 10.1.3, "Relational Providers Included With Oracle Data Service Integrator."

The Datasource Import Wizard uses the base version offset to display providers when there are multiple matches. The wizard's drop down menu contains providers with the minimum base version offset (that is, the closest version to the database). The best match appears at the top of the drop down menu.

For information on the Datasource Import Wizard, see Creating and Updating Physical Data Services in the Data Services Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Creating and Updating Physical Data Services.html.

For example, consider the standard DB2 relational provider. This provider matches all DB2 versions starting from 8. Its base version is 8. Assume that a new DB2 provider is created with the Configurable Relational Provider that matches DB2 9 with base version 9.

During metadata import of a table from the DB2 9 instance, both providers will match the database. However, for the first provider, the base version offset is 1, but the second one is be 0. Therefore, the second provider will be preferred over the first one.

10.5.2 JDBC Metadata Methods to XQuery Functions Mapping

This section describes the mapping of a java.sql.DatabaseMetaData instance to a set of XQuery functions that can be used by a database matching expression.

Mapped interface: java.sql.DatabaseMetaData

Function namespace:

  • prefix = jdbc

  • uri = http://www.bea.com/ns/aldsp/extensions/rdb/providers/custom/jdbc

Requirements for mapped methods:

  • No parameters

  • Return type of: boolean, string, or int

Table 10-3 lists the java.sql.DatabaseMetaData methods that satisfy these requirements and their corresponding JDBC methods and XQuery functions.

Table 10-3 Java Method to XQuery Function Mapping

Java Method XQuery Function

int getDatabaseMajorVersion()

jdbc:getDatabaseMajorVersion() as xs:int?

int getDatabaseMinorVersion()

jdbc:getDatabaseMinorVersion() as xs:int?

String getDatabaseProductName()

jdbc:getDatabaseProductName() as xs:string?

String getDatabaseProductVersion()

jdbc:getDatabaseProductVersion() as xs:string?

int getDriverMajorVersion()

jdbc:getDriverMajorVersion() as xs:int?

int getDriverMinorVersion()

jdbc:getDriverMinorVersion() as xs:int?

String getDriverName()

jdbc:getDriverName() as xs:string?

String getDriverVersion()

jdbc:getDriverVersion() as xs:string?

String getURL()

jdbc:getURL()as xs:string?

Exception handling:

  • SQLException, RuntimeException – Rethrows the exception.

  • LinkageError – Returns an empty sequence. This exception occurs if the driver is compiled against older version of JDBC API.

10.5.3 Additional External XQuery Functions

This section describes additional functions that are available in the database matching expression, but are not directly mapped from the jdbc.sql.DatabaseMetaData interface.

Function namespace:

  • prefix = cxp

  • uri = http://www.bea.com/ns/aldsp/extensions/rdb/providers/custom/

Table 10-4 lists and describes the function signatures.

Table 10-4 Function Signatures

Function signature Description

cxp:getDatabaseVersion() as xs:decimal

Returns the database version as xs:decimal. The version is computed based on java.sql.DatabaseMetaData as follows:

  1. Try to detect the version from the string returned by the getDatabaseProductVersion() method. Search for a format: n1.n2.n3. n1, n2, n3 must be non-negative integers and n3 is optional. The resulting decimal version is n1+max(n2,99)*0.01+max(n3,999)*0.00001

  2. If Step 1 fails and if getDatabaseMajorVersion(), getDatabaseMinorVersion() are implemented by the driver, then the result is: major + max(minor,99)*0.01

cxp:getDriverVersion() as xs:decimal

Same as approach 1, but uses the following functions from jdbc.sql.DatabaseMetaData: getDriverVersion(), getDriverMajorVersion(), getDriverMinorVersion().

10.6 Specifying SQL Syntax for Functions

This section discusses the SQL syntax for functions specified in the Configurable Relational Provider deployment descriptor. See also Section 10.4.3, "Configurable Relational Provider Reference" and the example descriptor in Section 10.2, "Sample Configurable Relational Provider File."

This section includes these topics:

10.6.1 Syntax Overview

Function SQL syntax is specified as a string with placeholders for each parameter. The syntax defines a SQL fragment to be generated by the relational wrapper when translating the corresponding XQuery function into SQL. It is specified as the content of the <function> element.

Example:

<function name="fn:lower">LOWER({0})</functions>

Parameter placeholders start with 0. There can be more than one placeholder with the same index which means that the argument must be replicated in the generated SQL.

Example:

<function name="fn:substring" arity="2">SUBSTR({0}, {1}, LENGTH({0})-{1}+1) </function>

Functions with a variable number of arguments can be specified in two different ways:

  • By setting the infix attribute and specifying only a delimiter as the function syntax

  • By using a variable length placeholder: {...}

These methods are described in the next two sections.

10.6.2 Setting the infix Attribute

The infix attribute of the function element is set as follows:

<function name="fn:concat" infix="true">||</function>

The generated SQL for this example is:

arg1 || arg2 || arg3 || ... || argN

10.6.3 Using a Variable Length Placeholder

During SQL generation the variable length placeholder {...} is replaced with the remaining arguments separated by commas.

<function name="fn:concat">CONCAT({...})</function>

The generated SQL is:

CONCAT(arg1,arg2,arg3,….,argN)

If another delimiter is required, it must be specified inside the variable length placeholder as follows:

{...DELIMITER}

For example:

<function name="fn:concat">COALESCE({... || }, "")</function>

The generated SQL is:

COALESCE(arg1 || arg2 || arg3 || … || argN, "")

Note:

In this case the delimiter is "|| ".

10.7 Default SQL Syntax for Functions

The default syntax for a function is used when the function is specified in the <functions> section of the Configurable Relational Provider configuration file (Table 10-2), but its syntax is not provided by the user (the <function> element content is empty). For some functions in this case, the relational provider chooses default syntax based on the default-syntax-for-empty-input attribute. See Section 10.4.3, "Configurable Relational Provider Reference" for information on the default-syntax-for-empty-input attribute.

This section lists the default syntaxes used for the three possible values of the default-syntax-for-empty-input attribute.

Table 10-5 default-syntax-for-empty-input Attribute

Attribute

Described In

strict

Table 10-6

strict-coalesce

Table 10-7

lax

Table 10-8

Functions for which the default SQL syntax depends on the default-syntax-for-empty-input attribute are denoted with an asterisk (*) in Table 10-6, Table 10-7, and Table 10-8.

These functions are:

  • fn:concat

  • fn:substring with 2 parameters

  • fn:substring with 3 parameters

  • fn:string-length

  • fn:lower-case

  • fn:upper-case

  • fn:sum

If default syntax is not defined for a function, then you must specify the syntax of the function when you use it. Otherwise, it is an error.

Table 10-6 default-syntax-for-empty-input = strict-coalesce

XQuery function Default SQL syntax Pushdown requirements

op:numeric-add

{0} + {1}

  

op:numeric-multiply

{0} * {1}

  

op:numeric-divide

{0} / {1}

  

op:numeric-mod

MOD({0}, {1})

  

fn:abs

ABS({0})

  

fn:ceiling

CEILING({0})

  

fn:floor

FLOOR({0})

  

fn:round

FLOOR({0} + 0.5)

  

fn-bea:sql-round

ROUND({0})

  

* fn:concat

COALESCE({0} || {1} || … || {n}, '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Arguments are not of type CLOB or LONG VARCHAR.

* fn:substring ($str, $pos)

if $pos is a subtype of xs:integer

COALESCE(SUBSTRING({0} FROM {1}), '')

else

COALESCE(SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER)), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

First argument is not of type CLOB or LONG VARCHAR.

* fn:substring($str, $pos, $len)

if $pos and $len are subtypes of xs:integer

COALESCE(SUBSTRING({0} FROM {1} FOR {2}), '')

else

COALESCE(SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER) FOR CAST({2}+0.5 AS INTEGER)), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

First argument is not of type CLOB or LONG VARCHAR.

* fn:string-length

COALESCE(CHAR_LENGTH({0}), 0)

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

* fn:lower-case

COALESCE(LOWER({0}), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

* fn:upper-case

COALESCE(UPPER({0}), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

fn:contains,

fn:starts-with,

fn:ends-with

LIKE with the ESCAPE clause and '|' as the escape character.

The first argument is not of type CLOB or LONG VARCHAR.

The second argument is SQL constant or parameter.

fn:year-from-dateTime,

fn:year-from-date

EXTRACT(YEAR FROM {0})

  

fn:month-from-dateTime

fn:month-from-date

EXTRACT(MONTH FROM {0})

  

fn:day-from-dateTime

fn:day-from-date

EXTRACT(DAY FROM {0})

  

fn:hours-from-dateTime,

fn:hours-from-time

EXTRACT(HOUR FROM {0})

  

fn:minutes-from-dateTime,

fn:minutes-from-time

EXTRACT(MINUTE FROM {0})

  

fn:seconds-from-dateTime,

fn:seconds-from-time

CAST(EXTRACT(SECOND FROM {0}) AS DECIMAL)

  

op:hexBinary-equal

{0} = {1}

  

op-bea:hexBinary-not-equal

{0} != {1}

  

fn:empty

{0} IS NULL

  

fn:exists

{0} IS NOT NULL

(or as EXISTS if subqueries in the WHERE clause are supported)

  

fn:count

COUNT (with COUNT DISTINCT support)

  

* fn:sum

COALESCE(SUM({0}), 0)

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

  

fn:min

MIN({0})

  

fn:max

MAX({0})

  

fn:avg

AVG({0})

  

fn-bea:sql-like($str, $pattern)

{0} LIKE {1}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:sql-like($str, $pattern, $escape)

{0} LIKE {1} ESCAPE {2}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:left

LEFT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:right

RIGHT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim

LTRIM(RTRIM({0}))

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-left

LTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-right

RTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:repeat

REPEAT({0})

Argument is not of type CLOB or LONG VARCHAR.

Table 10-7 default-syntax-for-empty-input = strict

XQuery function Default SQL syntax Pushdown requirements

op:numeric-add

{0} + {1}

  

op:numeric-multiply

{0} * {1}

  

op:numeric-divide

{0} / {1}

  

op:numeric-mod

MOD({0}, {1})

  

fn:abs

ABS({0})

  

fn:ceiling

CEILING({0})

  

fn:floor

FLOOR({0})

  

fn:round

FLOOR({0} + 0.5)

  

fn-bea:sql-round

ROUND({0})

  

* fn:concat

{0} || {1} || … || {n}

Arguments are not of type CLOB or LONG VARCHAR.

Arguments must be non–nullable (as detected by the compiler).

* fn:substring ($str, $pos)

if $pos is a subtype of xs:integer

SUBSTRING({0} FROM {1})

else

SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER))

First argument is not of type CLOB or LONG VARCHAR.

First argument must be non–nullable (as detected by the compiler).

* fn:substring($str, $pos, $len)

if $pos and $len are subtypes of xs:integer

SUBSTRING({0} FROM {1} FOR {2})

else

SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER) FOR CAST({2}+0.5 AS INTEGER))

First argument is not of type CLOB or LONG VARCHAR.

First argument must be non–nullable (as detected by the compiler).

* fn:string-length

CHAR_LENGTH({0})

Argument is not of type CLOB or LONG VARCHAR.

Argument must be non–nullable (as detected by the compiler).

* fn:lower-case

LOWER({0})

Argument is not of type CLOB or LONG VARCHAR

Argument must be non–nullable (as detected by the compiler).

* fn:upper-case

UPPER({0})

Argument is not of type CLOB or LONG VARCHAR

Argument must be non–nullable (as detected by the compiler).

fn:contains,

fn:starts-with,

fn:ends-with

LIKE with the ESCAPE clause and '|' as escape character.

The first argument is not of type CLOB or LONG VARCHAR.

The second argument is SQL constant or parameter.

fn:year-from-dateTime,

fn:year-from-date

EXTRACT(YEAR FROM {0})

  

fn:month-from-dateTime

fn:month-from-date

EXTRACT(MONTH FROM {0})

  

fn:day-from-dateTime

fn:day-from-date

EXTRACT(DAY FROM {0})

  

fn:hours-from-dateTime,

fn:hours-from-time

EXTRACT(HOUR FROM {0})

  

fn:minutes-from-dateTime,

fn:minutes-from-time

EXTRACT(MINUTE FROM {0})

  

fn:seconds-from-dateTime,

fn:seconds-from-time

CAST(EXTRACT(SECOND FROM {0}) AS DECIMAL)

  

op:hexBinary-equal

{0} = {1}

  

op-bea:hexBinary-not-equal

{0} != {1}

  

fn:empty

{0} IS NULL

  

fn:exists

{0} IS NOT NULL

(or as EXISTS if subqueries in the WHERE clause are supported)

  

fn:count

COUNT (with COUNT DISTINCT support)

  

* fn:sum

SUM({0})

Argument must be non–nullable (as detected by the compiler).

fn:min

MIN({0})

  

fn:max

MAX({0})

  

fn:avg

AVG({0})

  

fn-bea:sql-like($str, $pattern)

{0} LIKE {1}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:sql-like($str, $pattern, $escape)

{0} LIKE {1} ESCAPE {2}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:left

LEFT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:right

RIGHT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim

LTRIM(RTRIM({0}))

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-left

LTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-right

RTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:repeat

REPEAT({0})

Argument is not of type CLOB or LONG VARCHAR.

Table 10-8 default-syntax-for-empty-input = lax

XQuery function Default SQL syntax Pushdown requirements

op:numeric-add

{0} + {1}

  

op:numeric-multiply

{0} * {1}

  

op:numeric-divide

{0} / {1}

  

op:numeric-mod

MOD({0}, {1})

  

fn:abs

ABS({0})

  

fn:ceiling

CEILING({0})

  

fn:floor

FLOOR({0})

  

fn:round

FLOOR({0} + 0.5)

  

fn-bea:sql-round

ROUND({0})

  

* fn:concat

{0} || {1} || … || {n}

Arguments are not of type CLOB or LONG VARCHAR.

* fn:substring ($str, $pos)

if $pos is a subtype of xs:integer

SUBSTRING({0} FROM {1})

else

SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER))

First argument is not of type CLOB or LONG VARCHAR.

* fn:substring($str, $pos, $len)

if $pos and $len are subtypes of xs:integer

SUBSTRING({0} FROM {1} FOR {2})

else

SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER) FOR CAST({2}+0.5 AS INTEGER))

First argument is not of type CLOB or LONG VARCHAR.

* fn:string-length

CHAR_LENGTH({0})

Argument is not of type CLOB or LONG VARCHAR.

* fn:lower-case

LOWER({0})

Argument is not of type CLOB or LONG VARCHAR.

* fn:upper-case

UPPER({0})

Argument is not of type CLOB or LONG VARCHAR.

fn:contains,

fn:starts-with,

fn:ends-with

LIKE with ESCAPE clause and '|' as escape character

The first argument is not of type CLOB or LONG VARCHAR.

The second argument is SQL constant or parameter.

fn:year-from-dateTime,

fn:year-from-date

EXTRACT(YEAR FROM {0})

  

fn:month-from-dateTime

fn:month-from-date

EXTRACT(MONTH FROM {0})

  

fn:day-from-dateTime

fn:day-from-date

EXTRACT(DAY FROM {0})

  

fn:hours-from-dateTime,

fn:hours-from-time

EXTRACT(HOUR FROM {0})

  

fn:minutes-from-dateTime,

fn:minutes-from-time

EXTRACT(MINUTE FROM {0})

  

fn:seconds-from-dateTime,

fn:seconds-from-time

CAST(EXTRACT(SECOND FROM {0}) AS DECIMAL)

  

op:hexBinary-equal

{0} = {1}

  

op-bea:hexBinary-not-equal

{0} != {1}

  

fn:empty

{0} IS NULL

  

fn:exists

{0} IS NOT NULL

(or as EXISTS if subqueries in the WHERE clause are supported)

  

fn:count

COUNT (with COUNT distinct support)

  

* fn:sum

SUM({0})

  

fn:min

MIN({0})

  

fn:max

MAX({0})

  

fn:avg

AVG({0})

  

fn-bea:sql-like($str, $pattern)

{0} LIKE {1}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:sql-like($str, $pattern, $escape)

{0} LIKE {1} ESCAPE {2}

Arguments are not of type CLOB or LONG VARCHAR.

fn-bea:left

LEFT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:right

RIGHT({0}, {1})

First argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim

LTRIM(RTRIM({0}))

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-left

LTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-right

RTRIM({0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:repeat

REPEAT({0})

Argument is not of type CLOB or LONG VARCHAR.

10.8 Translating Built-In XQuery Operators Into SQL

The XQuery Functions and Operators specification defines built-in operators into which arithmetic and comparison operations are translated. For some operations, Oracle Data Service Integrator defines additional operators that it uses for evaluation. These additional operators can also be used for specifying XQuery to SQL translation.

Note:

For references to the XQuery specifications, see Supported XQuery Specifications in the XQuery and XQSE Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/xquery/intro.html#wp1109723.

For each of the following arithmetic operations, Oracle Data Service Integrator defines more specific operations for the following types: integer, decimal, double, float. These specific operations can be used to specify a better type match when defining a SQL generation rule.

  • op:numeric-add

  • op:numeric-subtract

  • op:numeric-multiply

  • op:numeric-divide

  • op:numeric-integer-divide

  • op:numeric-mod

For example, the following four operations are defined for op:numeric-add:

  • op-bea:integer-add

  • op-bea:decimal-add

  • op-bea:float-add

  • op-bea:double-add

Comparison operations in the XQuery are defined by three operators:

  • op:<type>-equals

  • op:<type>-less-than

  • op:<type>-greater-than

Oracle Data Service Integrator adds three more operations for each type:

  • op-bea:<type>-not-equals

  • op-bea:<type>-less-than-or-equals

  • op-bea:<type>-greater-than-or-equals

    Note:

    The function operation prefixes used in the expanded operations (such as op-bea) are discussed in "Standard and Oracle Data Service Integrator Namespaces for Functions and Types" on page -35.

For numeric types, each operator op-bea:numeric-<comparison_op> is further expanded into four numeric types:

op-bea:integer-<comparison_op>, op-bea:decimal-<comparison_op>, op-bea:double-<comparison_op>, op-bea:float-<comparison_op>.

Additional numeric comparisons added by Oracle Data Service Integrator follow the same pattern. For example

op-bea:numeric-not-equals is expanded into:

  • op-bea:integer-not-equals

  • op-bea:decimal-not-equals

  • op-bea:double-not-equals

  • op-bea:float-not-equals

All six string comparison operators are defined as Oracle Data Service Integrator specific operators:

  • op-bea:string-equals

  • op-bea:string-less-than

  • op-bea:string-greater-than

  • op-bea:string-not-equals

  • op-bea:string-less-than-or-equals

  • op-bea:string-greater-than-or-equals

10.9 Standard and Oracle Data Service Integrator Namespaces for Functions and Types

Table 10-9 lists the standard and Oracle Data Service Integrator namespaces for functions and types. Table 10-10 lists and describes each of the type namespaces.

Table 10-9 Function and operators namespaces

Prefix

Namespace

Description

fn

http://www.w3.org/2004/07/xpath-functions

Standard XQuery functions

op

http://www.w3.org/2004/07/xpath-operators

Standard XQuery operators

fn-bea

http://www.bea.com/xquery/xquery-functions

Oracle Data Service Integrator extension functions

op-bea

http://www.bea.com/xquery/xquery-operators

Oracle Data Service Integrator extension operators

Table 10-10 Type namespaces

Prefix

Namespace

Description

xs

http://www.w3.org/2001/XMLSchema

XML Schema types

xdt

http://www.w3.org/2004/07/xpath-datatypes

Additional XQuery types

dt-bea

http://www.bea.com/xquery/xquery-datatypes

Additional Oracle Data Service Integrator types. Currently only one: dt-bea:numeric (common numeric type)

10.10 Function and Type Name Resolution Process

The Relational Wrapper Extension Framework looks up functions, operators, and types by name as follows:

  1. Attempt a lookup using the specified QName. If the object is found, return it.

  2. If the namespace is empty or the prefix is not specified, loop through all commonly used namespaces for this object kind (see Section 10.9, "Standard and Oracle Data Service Integrator Namespaces for Functions and Types") and try to find the object in each of these namespaces.

For example, suppose the following function definition exists:

<function name="round">ROUND({0})</function>

First, that name is resolved to a QName in the default element namespace and looked up. Suppose then that the XQuery function with this name is not found (for example, if there was no default namespace used in the XML document).

Then the system will try start searching for the following functions (in this order): fn:round, op:round, fn-bea:round, op-bea:round. The system will find fn:round and register it with the specified SQL syntax.

A similar lookup process is applied for types when reading cast operation definitions. For types, the system automatically searches in xs, xdt and dt-bea namespaces.

Note that the arity attribute is also optional and only required to disambiguate between functions with the same name, for example, a substring with 2 and 3 arguments.

10.11 Abstract SQL Providers

Oracle Data Service Integrator provides a group of three abstract base classes that provide functionality to the Configurable Relational Provider. The AbstractSQLProvider class is the default parent class of the Configurable Relational Provider.

You can specify an abstract provider class in the Configurable Relational Provider's deployment descriptor with the parent element. See Section 10.3, "Using the Configurable Relational Provider."

This section discusses the abstract relational provider classes, and contains these sections:

10.11.1 AbstractSQLProvider

AbstractSQLProvider is an abstract base class. All other abstract and concrete relational provider classes extend this class. This class is used as a parent provider when the parent is not specified in the deployment descriptor of a provider; therefore, this class is not explicitly registered in the provider registry.

Table 10-11 summarizes the level of SQL support provided by AbstractSQLProvider:

Table 10-11 AbstractSQLProvider Features

Feature Status

Standard JDBC datatypes

Supported

Trivial select-project queries (for example: select ... from ... where)

Supported

Joins, group by, and order by

Not supported

Catalogs and schemas when addressing tables

Not supported

Catalog, schema, and table quotes

Set to "empty string"

Catalog and schema separator

Set to '.' (although separators are not used for queries generated by this provider)

Runtime properties

All set to false.

Table 10-12 lists the supported functions and operators for AbstractSQLProvider.

Table 10-12 Supported Functions and Operators for AbstractSQLProvider

XQuery function SQL Syntax Pushdown Requirements / Comments

and, or, fn:not

AND, OR, NOT

None.

op:numeric-equal

op:numeric-less-than

op:numeric-greater-than

op-bea:numeric-less-than-or-equal

op-bea:numeric-greater-than-or-equal

op-bea:numeric-not-equal

=, <, >, <=, >=, !=

  

op-bea:string-equal

op-bea:string-less-than

op-bea:string-greater-than

op-bea:string-less-than-or-equal

op-bea:string-greater-then-or-equal

op-bea:string-not-equal

=, <, >, <=, >=, !=

Both arguments are not CLOB or LONG VARCHAR

op:dateTime-equal

op:dateTime-less-than

op:dateTime-greater-than

op-bea:dateTime-less-than-or-equal

op-bea:dateTime-greater-than-or-equal

op-bea:dateTime-not-equal

=, <, >, <=, >=, !=

None.

op:date-equal

op:date-less-than

op:date-greater-than

op-bea:date-less-than-or-equal

op-bea:date-greater-than-or-equal

op-bea:date-not-equal

=, <, >, <=, >=, !=

None.

op:time-equal

op:time-less-than

op:time-greater-than

op-bea:time-less-than-or-equal

op-bea:time-greater-than-or-equal

op-bea:time-not-equal

=, <, >, <=, >=, !=

None.

op:hexBinary-equal

op-bea:hexBinary-not-equal

=, !=

Only if both arguments are BINARY or VARBINARY.

10.11.2 AbstractSQL89Provider

AbstractSQL89Provider extends AbstractSQLProvider (see Section 10.11.1, "AbstractSQLProvider"). This class adds support for additional clauses, functions, and updates. The AbstractSQL89Provider class includes these features:

  • Supports SQL89-style inner joins (for example, select … from A,B where A.<x> = B.<x>).

  • Supports order by column (null order is assumed to be 'low').

  • Supports group by column (and aggregate functions).

  • Schemas are used for table addressing (using dot as a separator).

  • Supports subqueries in where clause.

Table 10-13 lists the supported functions and operators for AbstractSQL89Provider. These functions and operators are in addition to the ones provided by the parent class, AbstractSQLProvider.

Table 10-13 Supported Functions and Operators for AbstractSQL89Provider

XQuery function SQL Syntax Pushdown Requirements / Comments

op:numeric-add

op:numeric-subtract

op:numeric-multiply

op:numeric-divide (except op-bea:integer-divide)

+, -, *, /

None.

fn:exists

{0} IS NOT NULL

(EXISTS in the WHERE clause is not supported)

None.

fn:empty

{0} IS NULL

None.

fn:count

COUNT (with COUNT DISTINCT support)

None.

fn:sum

SUM({0})

Note that this function does not match XQuery semantics. For empty (NULL) input, the function returns empty (NULL) instead of 0.

XQuery specifies that SUM(())=0; where () is an empty sequence. This provider translates the function to SQL as SUM(...). However, in SQL, SUM(NULL)=NULL, which is equivalent to () in XQuery.

fn:min

MIN({0})

None.

fn:max

MAX({0})

None.

fn:avg

AVG({0})

None.

fn-bea:sql-like($str, $pattern)

{0} LIKE {1}

First argument is not CLOB or LONG VARCHAR.

Second (and third) arguments are a SQL constant or parameter.

fn-bea:sql-like($str, $pattern, $escape)

{0} LIKE {1} ESCAPE {2}

None.

10.11.3 AbstractSQL92Provider

AbstractSQL92Provider extends AbstractSQL89Provider (see Section 10.11.2, "AbstractSQL89Provider"). This class adds support for SQL92-style joins (inner and outer), subqueries, and other features. The AbstractSQL92Provider class supports:

  • Inner and outer-joins

  • Subqueries in from clause

  • Order by and group by expression

  • Case expressions

  • Updates (update/identity-fetch – JDBC kind)

Table 10-14 lists the supported functions and operators for AbstractSQL92Provider. These functions and operators are in addition to the ones provided by the parent class, AbtractSQL89Provider.

Table 10-14 Supported Functions and Operators for AbstractSQL92Provider

XQuery function SQL Syntax Pushdown Requirements / Comments

fn:concat

COALESCE({0} || {1} || … || {n}, '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Arguments are not of type CLOB or LONG VARCHAR.

fn:upper-case

COALESCE(UPPER({0},'')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

fn:lower-case

COALESCE(LOWER({0},'')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

fn:substring ($str, $pos)

if $pos is a subtype of xs:integer

COALESCE(SUBSTRING({0} FROM {1}), '')

else

COALESCE(SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER)), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

First argument is not of type CLOB or LONG VARCHAR.

fn:substring($str, $pos, $len)

if $pos and $len are subtypes of xs:integer

COALESCE(SUBSTRING({0} FROM {1} FOR {2}), '')

else

COALESCE(SUBSTRING({0} FROM CAST({1}+0.5 AS INTEGER) FOR CAST({2}+0.5 AS INTEGER)), '')

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

First argument is not of type CLOB or LONG VARCHAR.

fn:string-length

COALESCE(CHAR_LENGTH({0}), 0)

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

Argument is not of type CLOB or LONG VARCHAR.

fn:contains,

fn:starts-with,

fn:ends-with

LIKE with ESCAPE clause and '|' as escape character

The first argument is not of type CLOB or LONG VARCHAR.

The second argument is SQL constant or parameter.

fn:year-from-dateTime,

fn:year-from-date

EXTRACT(YEAR FROM {0})

  

fn:month-from-dateTime

fn:month-from-date

EXTRACT(MONTH FROM {0})

  

fn:day-from-dateTime

fn:day-from-date

EXTRACT(DAY FROM {0})

  

fn:hours-from-dateTime,

fn:hours-from-time

EXTRACT(HOUR FROM {0})

  

fn:minutes-from-dateTime,

fn:minutes-from-time

EXTRACT(MINUTE FROM {0})

  

fn:seconds-from-dateTime,

fn:seconds-from-time

CAST(EXTRACT(SECOND FROM {0}) AS DECIMAL)

  

fn:sum

COALESCE(SUM({0}), 0)

COALESCE is not used if at compile-time it is determined that input can never be empty (NULL).

SUM(DISTINCT …) is supported

  

fn:min

MIN(DISTINCT …) supported

  

fn:max

MAX(DISTINCT …) supported

  

fn:avg

AVG(DISTINCT … ) supported

  

fn-bea:left

SUBSTRING({0} FROM 1 FOR {1})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim

TRIM (BOTH ' ' FROM {0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-left

TRIM(LEADING ' ' FROM {0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:trim-right

TRIM(TRAILING' ' FROM {0})

Argument is not of type CLOB or LONG VARCHAR.

fn-bea:date-from-dateTime

CAST({0} AS DATE)

  

Table 10-15 lists the cast operations that are pushed down by AbstractSQL92Provider.

Table 10-15 Supported Cast Operations for AbstractSQL92Provider

Source Type Target Type SQL Syntax Comments

subtypes of xs:int

xs:string

CAST({0} AS VARCHAR(11))

  

xs:string

xs:double

CAST({0} AS DOUBLE PRECISION)

Argument is not of type CLOB or LONG VARCHAR.

subtypes of numeric

xs:double

  

xs:string

xs:float

CAST({0} AS REAL)

Argument is not of type CLOB or LONG VARCHAR.

subtypes of numeric

xs:float

  

xs:string

xs:int

CAST({0} AS INT)

Argument is not of type CLOB or LONG VARCHAR.

subtypes of numeric

xs:int

  

xs:string

xs:short

CAST({0} AS SMALLINT)

Argument is not of type CLOB or LONG VARCHAR.

subtypes of numeric

xs:short

  

xs:dateTime

xs:date

CAST({0} AS DATE)