Oracle® Fusion Middleware

Using Data Service Integrator XQuery Engine

12c (12.1.3)

E47946-01

May 2014

Oracle Fusion Middleware Using Data Service Integrator XQuery Engine, 12c (12.1.3)

E47946-01

Copyright © 2008, 2014, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate failsafe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

Contents

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

1 Introducing the Oracle Data Service Integrator XQuery Engine

- 1.1 XML and XQuery

- 1.2 XQuery Use in Oracle Data Service Integrator

- 1.3 Supported XQuery Specifications

- 1.4 Learning More About the XQuery Language

2 Oracle's XQuery Implementation

- 2.1 Oracle XQuery Function Implementation

- 2.1.1 Function Overview

- 2.1.2 Access Control Functions

- 2.1.2.1 fn-bea:is-access-allowed

- 2.1.2.2 fn-bea:is-user-in-group

- 2.1.2.3 fn-bea:is-user-in-role

- 2.1.2.4 fn-bea:userid

- 2.1.3 Duration, Date, and Time Functions

- 2.1.3.1 fn-bea:date-from-dateTime

- 2.1.3.2 fn-bea:date-from-string-with-format

- 2.1.3.3 fn-bea:date-to-string-with-format

- 2.1.3.4 fn-bea:dateTime-from-string-with-format

- 2.1.3.5 fn-bea:dateTime-to-string-with-format

- 2.1.3.6 fn-bea:time-from-dateTime

- 2.1.3.7 fn-bea:time-from-string-with-format

- 2.1.3.8 fn-bea:time-to-string-with-format

- 2.1.3.9 Date and Time Patterns

- 2.1.4 Execution Control Functions

- 2.1.4.1 fn-bea:async

- 2.1.4.2 fn-bea:fence

- 2.1.4.3 fn-bea:timeout and fn-bea:timeout-with-label

- 2.1.4.4 fn-bea:fail-over, fn-bea:fail-over-with-label, fn-bea:fail-over-retry, and fn-bea:fail-over-retry-with-label

- 2.1.5 Numeric Functions

- 2.1.5.1 fn-bea:format-number

- 2.1.5.2 fn-bea:decimal-round

- 2.1.5.3 fn-bea:decimal-truncate

- 2.1.6 Other Functions

- 2.1.6.1 fn-bea:get-property

- 2.1.6.2 fn-bea:inlinedXML

- 2.1.6.3 fn-bea:rename

- 2.1.7 QName Functions

- 2.1.7.1 fn-bea:QName-from-string

- 2.1.8 Sequence Functions

- 2.1.8.1 fn-bea:interleave

- 2.1.9 String Functions

- 2.1.9.1 fn-bea:match

- 2.1.9.2 fn-bea:sql-like

- 2.1.9.3 fn-bea:trim

- 2.1.9.4 fn-bea:trim-left

- 2.1.9.5 fn-bea:trim-right

- 2.1.9.6 fn-bea:pad-left

- 2.1.9.7 fn-bea:pad-right

- 2.1.10 Extended XQuery Data Model (XXDM) Functions

- 2.1.10.1 fn-bea:current-value

- 2.1.10.2 fn-bea:old-value

- 2.1.11 Using Mutators for Updates

- 2.1.11.1 fn-bea:changed-element

- 2.1.11.2 fn-bea:insert-into

- 2.1.11.3 fn-bea:delete

- 2.1.11.4 fn-bea:replace-value

- 2.1.12 Mutator Function Examples

- 2.1.12.1 Update Based on Simple Parameters

- 2.1.12.2 Update Based on Complex Input Parameters

- 2.1.12.3 Update When the Parameter Structure Matches the Return Type

- 2.1.12.4 Update Based on Differences Between Old and New Values

- 2.1.12.5 Update Using Additional Data

- 2.1.12.6 Update Operation Replication

- 2.1.13 Unsupported XQuery Functions

- 2.1.14 Restrictions Related to Client Ad-Hoc Queries

- 2.1.15 Implementation-Specific Functions and Operators

- 2.2 Oracle XQuery Language Implementation

- 2.2.1 XQuery Language Support (and Unsupported Features)

- 2.2.2 Extensions to the XQuery Language in the Oracle Data Service Integrator XQuery Engine

- 2.2.2.1 Generalized FLWGOR (group by)

- 2.2.2.2 Optional Indicator in Direct Element and Attribute Constructors

- 2.2.3 Implementation-Defined Values for XQuery Language Processing

3 XQuery Engine and SQL

- 3.1 Introduction

- 3.1.1 Base and Core RDBMS Support

- 3.1.2 How the XQuery Engine Supports SQL Data Sources

- 3.1.2.1 Metadata and Data Type Mappings Get Stored in Annotated Files

- 3.1.2.2 Runtime Connection Management—Connection Sharing

- 3.2 XQuery-SQL Data Type Mappings

- 3.2.1 Date and Time Data Type Differences: Timezones and Time Precision

- 3.2.2 How Oracle Data Service Integrator Handles Timezone Information

- 3.2.3 How Oracle Data Service Integrator Handles Fractional Seconds

- 3.2.4 Scope Differences for Expressions and Data Types

- 3.3 SQL Pushdown: Performance Optimization

- 3.3.1 Function and Operator Pushdown

- 3.3.2 Parameters in Generated SQL Statements

- 3.3.3 Cast Operation Pushdown

- 3.3.4 Path Expressions Pushdown

- 3.3.5 Constant Pushdown

- 3.3.6 Variable Pushdown

- 3.3.7 Common Query Patterns

- 3.3.7.1 Simple Projection Queries

- 3.3.7.2 Where Clause Pushdown

- 3.3.7.3 Order By Clause Pushdown

- 3.3.7.4 Inner Join Pushdown

- 3.3.7.5 Outer Join Pushdown

- 3.3.7.6 Semi-Joins and Anti-Semi-Joins

- 3.3.8 Grouping and Aggregation

- 3.3.8.1 Group By Pushdown

- 3.3.8.2 Distinct-by Pushdown

- 3.3.8.3 Simple Aggregate Pattern

- 3.3.8.4 Group-By with a Nested Where Clause Translates to SQL HAVING Clause

- 3.3.8.5 Outer Join with Aggregate Pattern

- 3.3.8.6 If-Then-Else Pattern

- 3.3.8.7 Subsequence Pushdown

- 3.3.9 Direct SQL Data Services and Pushdown

- 3.3.10 Distributed Query Pushdown

- 3.4 Preventing SQL Pushdown

4 Understanding XML Namespaces

- 4.1 Introducing XML Namespaces

- 4.1.1 Exploring XML Schema Namespaces

- 4.2 Using XML Namespaces in Oracle Data Service Integrator Queries and Schemas

5 Best Practices Using XQuery

- 5.1 Introducing Data Service Design

- 5.2 Understanding Data Service Design Principles

- 5.3 Applying Data Service Implementation Guidelines

6 Oracle XQuery Scripting Extension (XQSE)

- 6.1 Introducing the XQuery Scripting Extension

- 6.2 Prolog and Query Body

- 6.3 Procedure Declaration

- 6.4 XQSE Function Declaration

- 6.5 Value Statement and Procedure Call

- 6.6 Block

- 6.7 Set Statement

- 6.8 While Statement

- 6.9 Return Statement

- 6.10 Iterate Statement

- 6.11 Try Statement

- 6.12 If Statement

- 6.13 Changed Element

- 6.14 XQSE Grammar Summary

A XQuery-SQL Mapping Reference

- A.1 IBM DB2 9.7, 10.1

- A.1.1 Data Type Mapping

- A.1.2 Function and Operator Pushdown

- A.1.3 Cast Operation Pushdown

- A.1.4 Other SQL Generation Capabilities

- A.2 Microsoft SQL Server 2008 R2, 2012

- A.2.1 Data Type Mapping

- A.2.2 Function and Operator Pushdown

- A.2.3 Cast Operation Pushdown

- A.2.4 Other SQL Generation Capabilities

- A.3 Oracle 11.x, 12.x

- A.3.1 Data Type Mapping

- A.3.2 Function and Operator Pushdown

- A.3.3 Cast Operation Pushdown

- A.3.4 Other SQL Generation Capabilities

- A.4 Sybase Adaptive Server Enterprise 15.7

- A.4.1 Data Type Mapping

- A.4.2 Function and Operator Pushdown

- A.4.3 Cast Operation Pushdown

- A.4.4 Other SQL Generation Capabilities

- A.5 Teradata 13.x, 14.x

- A.5.1 Data Type Mapping

- A.5.2 Function and Operator Pushdown

- A.5.3 Cast Operation Pushdown

- A.5.4 Other SQL Generation Capabilities

- A.6 Derby 10.6 (and higher)

- A.6.1 Data Type Mappings

- A.6.2 Functions and Operators

- A.6.3 Cast Operations

- A.7 Base (Generic) RDBMS Support

- A.7.1 Database Capabilities Information

- A.7.2 Data Type Mapping

- A.7.3 Cast Operation Pushdown

- A.7.4 Other SQL Generation Capabilities

Preface

This document describes how to develop applications for the Oracle Data Service Integrator.

Audience

This document is intended for application developers.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.

Related Documents

For more information, see the following documents in the Oracle Data Service Integrator documentation set:

- Oracle Fusion Middleware Using Data Service Integrator XQuery Engine

- Oracle Fusion Middleware Administering Data Service Integrator

- Oracle Fusion Middleware Installing Data Service Integrator

- Oracle Fusion Middleware Developing Applications with Data Service Integrator

- Oracle Fusion Middleware Data Services Java API for Oracle Data Integrator

Conventions

The following text conventions are used in this document:

Convention

Meaning

boldface

Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary.

italic

Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values.

monospace

Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

1 Introducing the Oracle Data Service Integrator XQuery Engine

This chapter briefly introduces the Oracle Data Service Integrator XQuery language and describes the version of the XQuery specification implemented in Oracle Data Service Integrator. Links to more information about XQuery are also provided.

The following topics are covered:

- Section 1.1, "XML and XQuery"

- Section 1.2, "XQuery Use in Oracle Data Service Integrator"

- Section 1.3, "Supported XQuery Specifications"

- Section 1.4, "Learning More About the XQuery Language"

1.1 XML and XQuery

XML is an increasingly popular markup language that can be used to label content in a variety of data sources including structured and semi-structured documents, relational databases, and object repositories.

XQuery is a query language that uses the structure of XML to express queries against data, including data physically stored in XML or transformed into XML using additional software. XQuery is therefore a language for querying XML-based information.

The relationship between XQuery and XML-based information is similar to the relationship between SQL and relational databases. Developers who are familiar with SQL will find XQuery to be conceptually a natural next step.

The W3C Query Working Group used a formal approach by defining a data model as the basis for XQuery. XQuery uses a type system and supports query optimization. It is statically typed, which supports compile-time type checking.

However, unlike SQL, which always returns two-dimensional result sets (rows and columns), XQuery results can conform to a complex XML schema. An XML schema can represent a hierarchy of nested elements that represent very detailed and complicated business data and information.

1.2 XQuery Use in Oracle Data Service Integrator

Oracle Data Service Integrator models the contents of various types of data sources as XML schemas. After you have configured Oracle Data Service Integrator access to the data sources you want to use, such as relational databases, Web Services, application views, data views, and so on, you can issue queries written in XQuery to Oracle Data Service Integrator. Oracle Data Service Integrator evaluates the query, fetches the data from the underlying data sources, and returns the query results.

For more information on developing data service XQueries see the Data Services Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/index.html.

1.3 Supported XQuery Specifications

Table 1-1 lists the XQuery and XML specifications with which the Oracle implementation complies.

Table 1-1 Supported XQuery and XML Standards

Topic

Specification

XQuery 1.0 and XPath 2.0 Data Model

The XQuery and XPath data model implementation is based on the following specification:

http://www.w3.org/TR/2004/WD-xpath-datamodel-20040723/

XQuery 1.0 Specification

The Oracle XQuery engine implements XQuery 1.0 based on the following specification:

http://www.w3.org/TR/2004/WD-xquery-20040723/

XQuery 1.0 and XPath 2.0 Functions and Operators

The Oracle XQuery engine implements functions and operators based on the following specification:

http://www.w3.org/TR/2004/WD-xpath-functions-20040723/

For information about Oracle extensions implemented in Oracle Data Service Integrator, see Section 2.2, "Oracle XQuery Language Implementation."

1.4 Learning More About the XQuery Language

You can learn more about XQuery and related technologies at the following locations:

- XQuery: http://www.w3.org/XML/Query

- XML Schema: http://www.w3.org/XML/Schema

2 Oracle's XQuery Implementation

The World Wide Web Consortium (W3C) defines a set of language features and functions for XQuery. The Oracle XQuery engine fully supports these language features with one exception (modules) and also supports a robust subset of functions and adds a number of implementation-specific functions and language keywords.

This chapter describes the function and language implementation and extensions in the XQuery engine.

The chapter includes the following topics:

- Section 2.1, "Oracle XQuery Function Implementation"

- Section 2.2, "Oracle XQuery Language Implementation"

2.1 Oracle XQuery Function Implementation

Oracle Data Service Integrator supports the W3C Working Draft "XQuery 1.0 and XPath 2.0 Functions and Operators" dated 23 July 2004 (http://www.w3.org/TR/2004/WD-xpath-functions-20040723/). In addition,

Oracle Data Service Integrator supports a number of functions that are enhancements to the XQuery specification, which you can recognize by their extended function prefix fn-bea:. For example, the full XQuery notation for an extended function is: fn-bea:function_name.

This section describes the Oracle XQuery function extensions, and contains the following topics:

- Section 2.1.1, "Function Overview"

- Section 2.1.2, "Access Control Functions"

- Section 2.1.3, "Duration, Date, and Time Functions"

- Section 2.1.4, "Execution Control Functions"

- Section 2.1.5, "Numeric Functions"

- Section 2.1.6, "Other Functions"

- Section 2.1.7, "QName Functions"

- Section 2.1.8, "Sequence Functions"

- Section 2.1.9, "String Functions"

- Section 2.1.13, "Unsupported XQuery Functions"

- Section 2.1.14, "Restrictions Related to Client Ad-Hoc Queries"

- Section 2.1.15, "Implementation-Specific Functions and Operators"

2.1.1 Function Overview

Table 2-1 provides an overview of the Oracle XQuery function extensions.

Table 2-1 Oracle XQuery Function Extensions

Category

Function

Description

Access Control Functions

fn-bea:is-access-allowed

Checks whether a user associated with the current request context can access the specified resource.

fn-bea:is-user-in-group

Checks whether the current user is in the specified group.

fn-bea:is-user-in-role

Checks whether the current user is in the specified role.

fn-bea:userid

Returns the identifier of the user making the request for the protected resource.

Duration, Date, and Time Functions

fn-bea:date-from-dateTime

Returns the date part of a dateTime value.

fn-bea:date-from-string-with-format

Returns a new date value from a string source value according to the specified pattern.

fn-bea:date-to-string-with-format

Returns a date string with the specified pattern.

fn-bea:dateTime-from-string-with-format

Returns a new dateTime value from a string source value according to the specified pattern.

fn-bea:dateTime-to-string-with-format

Returns a date and time string with the specified pattern.

fn-bea:time-from-dateTime

Returns the time part of a dateTime value.

fn-bea:time-from-string-with-format

Returns a new time value from a string source value according to the specified pattern.

fn-bea:time-to-string-with-format

Returns a time string with the specified pattern.

Execution Control Functions

fn-bea:async

Evaluates an XQuery expression asynchronously, depositing the result of the evaluation into a buffer.

fn-bea:fence

Enables you to define optimization boundaries, dividing queries into islands within which optimizations should occur.

fn-bea:timeout

Returns either the full result of the primary expression, or the full result of the alternate expression in cases when the primary XQuery expression times out.

fn-bea:timeout-with-label

Same as fn-bea:timeout but with label to support auditing.

fn-bea:fail-over

fn-bea:fail-over-with-label

Returns either the full result of the primary expression, or the full result of the alternate expression in cases when the primary XQuery expression fails.

For fn-bea:fail-over-with-label the audit record also contains the label, specified as an argument

fn-bea:fail-over-retry

fn-bea:fail-over-retry-with-label

Returns either the full result of the primary expression, or the full result of the alternate expression in cases when the primary XQuery expression fails.

The functions re-evaluate the primary expression for each subsequent evaluation even if the evaluation of the expression raises an error.

For fn-bea:fail-over-retry-with-label the audit record also contains the label, specified as an argument.

Numeric Functions

fn-bea:format-number

Converts a double to a string using the specified format pattern.

fn-bea:decimal-round

Returns a decimal value rounded to the specified precision or whole number.

fn-bea:decimal-truncate

Returns a decimal value truncated to the specified precision or whole number.

Other Functions

fn-bea:get-property

Enables you to write data services that can change behavior based on external influence.

fn-bea:inlinedXML

Parses textual XML and returns an instance of the XQuery 1.0 Data Model.

fn-bea:rename

Renames a sequence of elements.

QName Functions

fn-bea:QName-from-string

Creates an xs:QName and uses the value of specified argument as its local name without a namespace.

Sequence Functions

fn-bea:interleave

Interleaves items specified in the arguments.

String Functions

fn-bea:match

Returns a list of integers (either an empty list with 0 integers or a list with 2 integers) specifying which characters in the string input matches the input regular expression.

fn-bea:sql-like

Searches a string using a pattern, specified using the syntax of the SQL LIKE clause. The function optionally enables you to escape wildcards in the pattern.

fn-bea:trim

Removes the leading and trailing white space.

fn-bea:trim-left

Removes the leading white space.

fn-bea:trim-right

Removes the trailing white space.

fn-bea:pad-left

Adds a specified number of characters to the left of a specified string. Optionally, the character string used in padding can also be specified.

fn-bea:pad-right

Adds a specified number of characters to the right of a specified string. Optionally the character string used in padding can also be specified.

Extended XQuery Data Model (XXDM) Functions

fn-bea:current-value

Returns an XQuery Data Model (XDM) instance representing the current value of the specified argument.

fn-bea:old-value

Returns an XDM instance representing the value of the specified argument prior to modification.

2.1.2 Access Control Functions

Oracle Data Service Integrator uses the role-base security policies of the underlying WebLogic platform to control access to data resources. A security policy is a condition that must be met for a secured resource to be accessed. If the outcome of condition evaluation is false — given the policy, requested resource, and user context — access to the resource is blocked and associated data is not returned.

Once the security policies have been configured using the Oracle Data Service Integrator Administration Console, you can use the security function extensions described in this section to determine:

- Whether a user associated with the current request context can access a specified resource

- Whether the current user is in a specified role

- Whether the current user is in a specified group

This section describes the following Oracle Data Service Integrator access control function extensions to the Oracle implementation of XQuery:

- Section 2.1.2.1, "fn-bea:is-access-allowed"

- Section 2.1.2.2, "fn-bea:is-user-in-group"

- Section 2.1.2.3, "fn-bea:is-user-in-role"

- Section 2.1.2.4, "fn-bea:userid"

2.1.2.1 fn-bea:is-access-allowed

The fn-bea:is-access-allowed() function checks whether a user associated with the current request context can access the specified resource, which is denoted by a resource name and a data service identifier. The function has the following signature:

fn-bea:is-access-allowed($resource as xs:string, $data\service as xs:string) as xs:boolean

where $resource is the name of the resource, and $dataservice is the resource identifier.

This function makes a call to the WebLogic security framework to check access for the specified resource. An example is shown below.

if (fn-bea:is-access-allowed("CustomerProfile/ssn",
"ld:DataServices/CustomerProfile.ds"))
then fn:true()

2.1.2.2 fn-bea:is-user-in-group

The fn-bea:is-user-in-group() function checks whether the current user is in the specified group. This function analyzes the WebLogic authenticated subject for appropriate group membership.

This function has the following signature:

fn-bea:is-user-in-group($group as xs:string) as xs:boolean

where $group is the group to test against the current user.

Note:

This operation is not automatically authenticated.

2.1.2.3 fn-bea:is-user-in-role

The fn-bea:is-user-in-role() function checks whether the current user is in the specified global role. This function obtains a list of roles from the WebLogic security framework.

The function has the following signature:

fn-bea:is-user-in-role($role as xs:string) as xs:boolean

where $role is the role to test against the current user.

Note:

This operation is not automatically authenticated.

2.1.2.4 fn-bea:userid

The fn-bea:userid() function returns the identifier of the user making the request for the protected resource.

The function has the following signature:

fn-bea:userid() as xs:string

2.1.3 Duration, Date, and Time Functions

This section describes the following duration, date, and time function extensions to the Oracle implementation of XQuery:

- Section 2.1.3.1, "fn-bea:date-from-dateTime"

- Section 2.1.3.2, "fn-bea:date-from-string-with-format"

- Section 2.1.3.3, "fn-bea:date-to-string-with-format"

- Section 2.1.3.4, "fn-bea:dateTime-from-string-with-format"

- Section 2.1.3.5, "fn-bea:dateTime-to-string-with-format"

- Section 2.1.3.6, "fn-bea:time-from-dateTime"

- Section 2.1.3.7, "fn-bea:time-from-string-with-format"

- Section 2.1.3.8, "fn-bea:time-to-string-with-format"

2.1.3.1 fn-bea:date-from-dateTime

The fn-bea:date-from-dateTime() function converts a dateTime to a date, and returns the date part of the dateTime value.

The function has the following signature:

fn-bea:date-from-dateTime($dateTime as xs:dateTime?) as xs:date?

where $dateTime is the date and time.

Examples:

- fn-bea:date-from-dateTime(xs:dateTime("2005-07-15T21:09:44")) returns a date value corresponding to July 15th, 2005 in the current time zone.

- fn-bea:date-from-dateTime(()) returns an empty sequence.

2.1.3.2 fn-bea:date-from-string-with-format

The fn-bea:date-from-string-with-format() function returns a new date value from a string source value according to the specified pattern.

The function has the following signature:

fn-bea:date-from-string-with-format($format as xs:string?, $dateString as xs:string?) as xs:date?

where $format is the pattern and $dateString is the date. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:date-from-string-with-format("yyyy-MM-dd G", "2005-06-22 AD") returns the specified date in the current time zone.

- fn-bea:date-from-string-with-format("yyyy-MM-dd", "2002-July-22") generates an error because the date string does not match the specified format.

- fn-bea:date-from-string-with-format("yyyy-MMM-dd", "2005-JUL-22") returns the specified date in the current time zone.

2.1.3.3 fn-bea:date-to-string-with-format

The fn-bea:date-to-string-with-format() function returns a date string with the specified pattern.

The function has the following signature:

fn-bea:date-to-string-with-format($format as xs:string?, $date as xs:date?) as xs:string?

where $format is the pattern and $date is the date. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:date-to-string-with-format("yy-dd-mm", xs:date("2005-07-15")) returns the string "05-15-07".

- fn-bea:date-to-string-with-format("yyyy-mm-dd", xs:date("2005-07-15")) returns the string "2005-07-15".

2.1.3.4 fn-bea:dateTime-from-string-with-format

The fn-bea:dateTime-from-string-with-format() function returns a new dateTime value from a string source value according to the specified pattern.

The function has the following signature:

fn-bea:dateTime-from-string-with-format($format as xs:string?, $dateTimeString as xs:string?) as xs:dateTime?

where $format is the pattern and $dateTimeString is the date and time. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:dateTime-from-string-with-format("yyyy-MM-dd G", "2005-06-22 AD") returns the specified date, 12:00:00AM in the current time zone.

- fn-bea:dateTime-from-string-with-format("yyyy-MM-dd 'at' hh:mm", "2005-06-22 at 11:04") returns the specified date, 11:04:00AM in the current time zone.

- fn-bea:dateTime-from-string-with-format("yyyy-MM-dd", "2005-July-22") generates an error because the date string does not match the specified format.

- fn-bea:dateTime-from-string-with-format("yyyy-MMM-dd", "2005-JUL-22") returns 12:00:00AM in the current time zone.

2.1.3.5 fn-bea:dateTime-to-string-with-format

The fn-bea:dateTime-to-string-with-format() function returns a date and time string with the specified pattern.

The function has the following signature:

fn-bea:dateTime-to-string-with-format($format as xs:string?, $dateTime as xs:dateTime?) as xs:string?

where $format is the pattern and $dateTime is the date and time. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:dateTime-to-string-with-format("dd MMM yyyy hh:mm a G", xs:dateTime("2005-01-07T22:09:44")) returns the string "07 JAN 2005 10:09 PM AD".

- fn-bea:dateTime-to-string-with-format("MM-dd-yyyy", xs:dateTime("2005-01-07T22:09:44")) returns the string "01-07-2005".

2.1.3.6 fn-bea:time-from-dateTime

The fn-bea:time-from-dateTime() function returns the time from a dateTime value.

The function has the following signature:

fn-bea:time-from-dateTime($dateTime as xs:dateTime?) as xs:time?

where $dateTime is the date and time.

Examples:

- fn-bea:time-from-dateTime(xs:dateTime("2005-07-15T21:09:44")) returns a time value corresponding to 9:09:44PM in the current time zone.

- fn-bea:time-from-dateTime(()) returns an empty sequence.

2.1.3.7 fn-bea:time-from-string-with-format

The fn-bea:time-from-string-with-format() function returns a new time value from a string source value according to the specified pattern.

The function has the following signature:

fn-bea:time-from-string-with-format($format as xs:string?, $timeString as xs:string?) as xs:time?

where $format is the pattern and $timeString is the time. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:time-from-string-with-format("HH.mm.ss", "21.45.22") returns the time 9:45:22PM in the current time zone.

- fn-bea:time-from-string-with-format("hh:mm:ss a", "8:07:22 PM") returns the time 8:07:22PM in the current time zone.

2.1.3.8 fn-bea:time-to-string-with-format

The fn-bea:time-to-string-with-format() function returns a time string with the specified pattern.

The function has the following signature:

fn-bea:time-to-string-with-format($format as xs:string?, $time as xs:time?) as xs:string?

where $format is the pattern and $time is the time. For more information about specifying patterns, see Section 2.1.3.9, "Date and Time Patterns."

Examples:

- fn-bea:time-to-string-with-format("hh:mm a", xs:time("22:09:44")) returns the string "10:09 PM".

- fn-bea:time-to-string-with-format("HH:mm a", xs:time("22:09:44")) returns the string "22:09 PM".

2.1.3.9 Date and Time Patterns

You can construct date and time patterns using standard Java class symbols. Table 2-2 outlines the pattern symbols you can use.

Table 2-2 Date and Time Patterns

This Symbol

Represents This Data

Produces This Result

G

Era

AD

y

Year

1996

M

Month of year

July, 07

d

Day of the month

19

h

Hour of the day (1–12)

10

H

Hour of the day (0–23)

22

m

Minute of the hour

30

s

Second of the minute

55

S

Millisecond

978

E

Day of the week

Tuesday

D

Day of the year

27

w

Week in the year

27

W

Week in the month

2

a

am/pm marker

AM, PM

k

Hour of the day (1–24)

24

K

Hour of the day (0–11)

0

z

Time zone

PST (or PDT)

Repeat each symbol to match the maximum number of characters required to represent the actual value. For example, to represent 4 July 2002, the pattern is d MMMM yyyy. To represent 12:43 PM, the pattern is hh:mm a.

2.1.4 Execution Control Functions

This section describes the following Oracle Data Service Integrator execution control function extensions to the Oracle implementation of XQuery:

- Section 2.1.4.1, "fn-bea:async"

- Section 2.1.4.2, "fn-bea:fence"

- Section 2.1.4.3, "fn-bea:timeout and fn-bea:timeout-with-label"

- Section 2.1.4.4, "fn-bea:fail-over, fn-bea:fail-over-with-label, fn-bea:fail-over-retry, and fn-bea:fail-over-retry-with-label"

2.1.4.1 fn-bea:async

The fn-bea:async() function evaluates an XQuery expression asynchronously, using a buffer to control data flow between threads of execution.

The function has the following signature:

fn-bea:async($expression as item()*) as item()*

where $expression is the XQuery expression to evaluate asynchronously.

The fn-bea:async function enables asynchronous execution of Web services to reduce problems caused by the latency of these services.

Note:

Asynchronous web services do not propagate the transaction context to other threads, regardless of the transaction settings. Asynchronous operations are likewise unable to start new transactions.

Example:

In the following example, CUSTOMER is a database table while the getCreditScore functions are Web services offered by two credit rating agencies.

for $cust in db:CUSTOMER()
where $cust/ID eq $param
return
let $score1:= fn-bea:async(exper:getCreditScore($cust/SSN), 2),
$score2:= fn-bea:async(equi:getCreditScore($cust/SSN), 2)
return
if (fn:abs($score1 - $score2) < $threshold)
then fn:avg(($score1, $score2))
else fn:max(($score1, $score2))

2.1.4.2 fn-bea:fence

The fn-bea:fence() function enables you to define optimization boundaries, dividing queries into islands within which optimizations should occur while preventing optimizations across boundaries. You might consider using the fn-bea:fence function when building a query incrementally.

The function has the following signature:

fn-bea:fence($expression as item()*) as item()*

where $expression is the input expression.

The fn-bea:fence function is a pass-through function that does not change the input stream, but indicates to the optimizer that global rewritings should not occur across itself. Specifically, the fn-bea:fence function stops the following rewritings: view unfolding, loop unrolling, constant folding, and Boolean optimizations.

2.1.4.3 fn-bea:timeout and fn-bea:timeout-with-label

The timeout functions return either of the following:

- The full result of the primary expression.

- The full result of an alternate expression, in cases where the primary XQuery expression times out or fails. One or two alternate expressions can be returned, as described below.

Timeout functions are designed to be highly configurable. In the case of an error condition, the function can return either a single $alt expression or it can return more detailed information as $timeout and $failure.

The difference between the two functions fn-bea-timeout( ) and fn-bea-timeout-with-label( ) is that the latter returns $label along with other auditing information when an error condition is encountered.

fn-bea-timeout Signature

The fn-bea:timeout() function has the following signature:

fn-bea:timeout($seq as item()*,
$millisec as xs:integer,
$timeout as item()*,
$failure as item()*) as item()*

where $seq is the primary XQuery expression to evaluate, $millisec is the timeout value in milliseconds, $timeout is returned if the evaluation of $seq takes more than $millis milliseconds to execute. $failure is returned if the evaluation of $seq raises an error.

Alternatively, you can replace the $timeout and $failure parameters with a single $alt parameter. The result of $alt will then be returned if a timeout or other error occurs.

fn-bea-timeout-with-label Signature

The fn-bea:timeout-with-label() function has the following signature:

fn-bea:timeout-with-label($seq as item()*,
$millisec as xs:integer,
$timeout as item()*
$failure as item(),
$label as xs:string) as item()*

where $label represents information provided to the audit record.

Operational Details

Both functions return the result of evaluating $seq if the evaluation of $seq:

1. Does not raise an error and

2. Does not take more than the value of $millis (in milliseconds) to execute.

If an error does occur or the millisecond limit is exceeded, the alternate expression is returned along with the audit record.

The audit record contains:

- Name of the function call

- Source location of the function call (if available)

- Timeout value that was exceeded, if the execution of $seq timed out, or

- The error that was raised by execution of $seq

- Label, if the version of timeout that returns $label is invoked.

If the evaluation of $millis or $alt raises an error, the error is reported in the usual way. That is, neither of the functions attempts to handle the returned error.

If for a specific instance of one of these functions in a query the evaluation of $seq raises an error or "times out", all subsequent evaluations of this instance during the same query evaluation will return $timeout and $failure (or $alt). No attempt to re-evaluate $seq is made in such a case.

You can use the timeout functions in the following ways:

- Around a region of an XQuery result which is optional, such as when you want the rest of the answer in any case.

- To select an available data source from among a set of possibly (very) heterogeneous sources that can provide the information of interest.

- To handle slow or unavailable resources uniformly.

Note that the timeout functions immediately return the alternative expression in cases when accessing the data source causes an error.

Here is an example where $param is a external parameter:

for $cust in db:CUSTOMER()
where $cust/ID eq $param
return
fn-bea:timeout(exper:getCreditScore($cust/SSN), 200,
fn-bea:timeout(equi:getCreditScore($cust/SSN), 200,
fn:error()
)
)

2.1.4.4 fn-bea:fail-over, fn-bea:fail-over-with-label, fn-bea:fail-over-retry, and fn-bea:fail-over-retry-with-label

The fn:bea:fail-over() and fn:bea:fail-over-with-label() functions return the result of evaluating $seq if the evaluation of $seq does not raise an exception. If it does raise an exception, $alt is returned. Both functions are polymorphic and their static return type is the union of the static types of $seq and $alt.

The functions have the following signatures:

fn-bea:fail-over($seq as item()*,
$alt as item()*) as item()*

fn-bea:fail-over-with-label($seq as item()*,
$alt as item()*,
$label as xs:string) as item()*

If $alt is returned the audit record contains:

- The name of the function call

- The source locations of the function call (if available)

- The exception that was raised by the execution of $seq. For fn-bea:fail-over-with-label the audit record also contains $label.

If the evaluation of $seq raises an exception, all subsequent evaluations of this instance during the same query evaluation will return $alt. No attempt to re-evaluate $seq is made. If the evaluation of $alt raises an exception, it is simply reported. No attempt is made to handle the error.

The fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions return the result of evaluating $seq if the evaluation of $seq does not raise an exception. If it does raise an exception, $alt is returned.

In contrast to the fn:bea:fail-over and fn:bea:fail-over-with-label functions, however, the fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions re-evaluate $seq for each subsequent evaluation even if the evaluation of $seq raises an error.

The fn:bea:fail-over-retry and fn:bea:fail-over-retry-with-label functions have the following signatures:

fn-bea:fail-over-retry($seq as item()*,
$alt as item()*) as item()*

fn-bea:fail-over-retry-with-label($seq as item()*,
$alt as item()*,
$label as xs:string) as item()*

The fn-bea:fail-over( ) functions can be used in two ways:

- A fail-over function can be placed around an "optional" XQuery result. Then, if expected result is not returned, at least the remainder of the query results will be returned. In such a case, the XMLtype (schema) needs to be constructed in such a way that the results remain valid when some portion of the information is not returned.

- Nested invocations can be used to select an available data source from among a set of (possibly) heterogeneous and (possibly) unavailable data sources. Each invocation can access the appropriate available source and restructure its answer set appropriately for the surrounding context. Best practices in query construction would likely involve the use of functions to restructure the content.

2.1.5 Numeric Functions

This section describes the following numeric function extensions to the Oracle implementation of XQuery:

- Section 2.1.5.1, "fn-bea:format-number"

- Section 2.1.5.2, "fn-bea:decimal-round"

- Section 2.1.5.3, "fn-bea:decimal-truncate"

2.1.5.1 fn-bea:format-number

The fn-bea:format-number() function converts a double to a string using the specified format pattern.

The function has the following signature:

fn-bea:format-number($number as xs:double, $pattern as xs:string) as xs:string

where $number represents the double number to be converted to a string, and $pattern represents the pattern string. The format of this pattern is specified by the JDK 1.5.0 DecimalFormat class. For information on DecimalFormat and other JDK 1.5.0 Java classes see: http://java.sun.com/j2se/1.5.0/.

2.1.5.2 fn-bea:decimal-round

The fn-bea:decimal-round() function returns a decimal value rounded to the specified precision (scale) or to the nearest whole number.

The function has the following signatures:

fn-bea:decimal-round($value as xs:decimal?, $scale as xs:integer?) as xs:decimal?
fn-bea:decimal-round($value as xs:decimal?) as xs:decimal?

where $value is the decimal value to round and $scale is the precision with which to round the decimal input. A scale value of 1 rounds the input to tenths, a scale value of 2 rounds it to hundreths, and so on.

Examples:

- fn-bea:decimal-round(127.444, 2) returns 127.44.

- fn-bea:decimal-round(0.1234567, 6) returns 0.123457.

2.1.5.3 fn-bea:decimal-truncate

The fn-bea:decimal-truncate() function returns a decimal value truncated to the specified precision (scale) or to the nearest whole number.

The function has the following signatures:

fn-bea:decimal-truncate($value as xs:decimal?, $scale as xs:integer?) as xs:decimal?
fn-bea:decimal-truncate($value as xs:decimal?) as xs:decimal?

where $value is the decimal value to truncate and $scale is the precision with which to truncate the decimal input. A scale value of 1 truncates the input to tenths, a scale value of 2 truncates it to hundreths, and so on.

Examples:

- fn-bea:decimal-truncate(192.454, 2) returns 192.45.

- fn-bea:decimal-truncate(192.454) returns 192.

- fn-bea:decimal-truncate(0.1234567, 6) returns 0.123456.

2.1.6 Other Functions

This section describes the following function extensions to the Oracle implementation of XQuery:

- Section 2.1.6.1, "fn-bea:get-property"

- Section 2.1.6.2, "fn-bea:inlinedXML"

- Section 2.1.6.3, "fn-bea:rename"

2.1.6.1 fn-bea:get-property

The fn-bea:get-property() function enables you to write data services that can change behavior based on external influence. This is an implicit way to parameterize functions.

The function first checks whether the property has been defined using the Oracle Data Service Integrator Administration Console. If so, it returns this value as a string. In cases when the property is not defined, the function returns the default value.

The function has the following signature:

fn-bea:get-property($propertyName as xs:string, $defaultValue as xs:string) as xs:string

where $propertyName is the name of the property, and $defaultValue is the default value returned by the function.

2.1.6.2 fn-bea:inlinedXML

The fn-bea:inlinedXML() function parses textual XML and returns an instance of the XQuery 1.0 Data Model.

The function has the following signature:

fn-bea:inlinedXML($text as xs:string) as node()*

where $text is the textual XML to parse.

Examples:

- fn-bea:inlinedXML("<e>text</e>") returns element "e".

- fn-bea:inlinedXML("<?xml version="1.0"><e>text</e>") returns a document with root element "e".

2.1.6.3 fn-bea:rename

The fn-bea:rename() function renames an element or a sequence of elements.

The function has the following signature:

fn-bea:rename($oldelements as element()*, $newname as element()) as element()*)

where $oldelements is the sequence of elements to rename, and $newname is an element from which the new name and type are extracted.

For each element in the original sequence, the fn-bea:rename function returns a new element with the following:

- The same name and type as $newname

- The same content as the old element

Example:

for $c in CUSTOMER()
return
<CUSTOMER>
{fn-bea:rename($c/FIRST_NAME, <FNAME/>)}
{fn-bea:rename($c/LAST_NAME, <LNAME/>)}
</CUSTOMER>

In the above, if CUSTOMER() returns:

<CUST><FIRST_NAME>John</FIRST_NAME><LAST_NAME>Jones</LAST_NAME></CUST>

The output value would be:

<CUSTOMER><FNAME>John</FNAME><LNAME>Jones</LNAME></CUSTOMER>

2.1.7 QName Functions

This section describes the following QName function extensions to the Oracle implementation of XQuery:

2.1.7.1 fn-bea:QName-from-string

The fn-bea:QName-from-string() function creates an xs:QName and uses the value of $param as its local name without a namespace.

The function has the following signature:

fn-bea:QName-from-string($name as xs:string) as xs:QName

where $name is the local name.

2.1.8 Sequence Functions

This section describes the following sequence function extensions to the Oracle implementation of XQuery:

- Section 2.1.8.1, "fn-bea:interleave"

2.1.8.1 fn-bea:interleave

The fn-bea:interleave() function interleaves the specified arguments. The function has the following signature:

fn-bea:interleave($item1 as item()*, $item2 as xdt:anyAtomicType) as item()*

where $item1 and $item2 are the items to interleave.

For example, fn-bea:interleave((<a/>, <b/>, </c>), " ") returns the following sequence:

(<a/>, " ", <b/>, " ", </c>)

2.1.9 String Functions

This section describes the following string function extensions to the Oracle implementation of XQuery:

- Section 2.1.9.1, "fn-bea:match"

- Section 2.1.9.2, "fn-bea:sql-like"

- Section 2.1.9.3, "fn-bea:trim"

- Section 2.1.9.4, "fn-bea:trim-left"

- Section 2.1.9.5, "fn-bea:trim-right"

- Section 2.1.9.6, "fn-bea:pad-left"

- Section 2.1.9.7, "fn-bea:pad-right"

2.1.9.1 fn-bea:match

The fn-bea:match() function returns a list of two integers specifying the characters in the string input that match the input regular expression (or an empty list, if none found). When the function returns a match, the first integer represents the index of (the position of) the first character of the matching substring and the second integer represents the number of matching characters. The function has the following signature:

fn-bea:match($source as xs:string?, $regularExp as xs:string?) as xs:int*

where $source is the input string and $regularExp uses is the regular expression.

Regular expression use standard java.util.regex.Pattern class patterns. Currently the following link to regular expression constructs is valid:

http://java.sun.com/j2se/1.5.0/docs/api/java/util/regex/Pattern.html

2.1.9.2 fn-bea:sql-like

The fn-bea:sql-like() function tests whether a string contains the specified pattern. Typically, you can use this function as a condition for a query, similar to the SQL LIKE operator used in a predicate of SQL queries. The function returns TRUE if the pattern is matched in the source expression; otherwise the function returns FALSE.

The function has the following signatures:

fn-bea:sql-like($source as xs:string?, $pattern as xs:string?, $escape as xs:string?) as xs:boolean?
fn-bea:sql-like($source as xs:string?, $pattern as xs:string?) as xs:boolean?

where $source is the string to search, $pattern is the pattern specified using the syntax of the SQL LIKE clause, and $escape is the character to use to escape a wildcard character in the pattern.

You can use the following wildcard characters to specify the pattern:

- Percent character ("%"). Represents a string of zero or more characters.

- Underscore character ("_"). Represents any single character.

You can include the "%" or "_" character in the pattern by specifying an escape character and preceding the "%" or "_" character in the pattern with this character. The function then reads the character literally, instead of interpreting it as a special pattern-matching character.

The $escape character has to be exactly one character in length and cannot be either the percent ("%") or underscore ("_") character.

Examples:

- fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"H%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with the character H.

- fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"_a%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with any character and have a second character of the letter a.

- fn-bea:sql-like($RTL_CUSTOMER.ADDRESS_1/FIRST_NAME,"H\%%","\") returns TRUE for all FIRST_NAME elements in $RTL_CUSTOMER.ADDRESS that start with the characters H%.

2.1.9.3 fn-bea:trim

The fn-bea:trim() function removes the leading and trailing white space.

The function has the following signature:

fn-bea:trim($source as xs:string?) as xs:string?

where $source is the string to trim. In cases when $source is an empty sequence, the function returns an empty sequence. Oracle Data Service Integrator generates an error when the parameter is not a string.

Examples:

- fn-bea:trim("abc") returns the string value "abc".

- fn-bea:trim("  abc  ") returns the string value "abc".

- fn-bea:trim(()) returns the empty sequence.

- fn-bea:trim(5) generates a compile-time error because the parameter is not a string.

2.1.9.4 fn-bea:trim-left

The fn-bea:trim-left() function removes the leading white space.

The function has the following signature:

fn-bea:trim-left($input as xs:string?) as xs:string?

where $input is the string to trim.

Examples:

- fn-bea:trim-left(" abc ") removes leading spaces and returns the string "abc ".

- fn-bea:trim-left(()) returns the empty sequence.

2.1.9.5 fn-bea:trim-right

The fn-bea:trim-right() function removes the trailing white space.

This function has the following signature:

fn-bea:trim-right($input as xs:string?) as xs:string?

where $input is the string to trim.

Examples:

- fn-bea:trim-right(" abc ") removes trailing spaces and returns the string " abc".

- fn-bea:trim-right(()) returns the empty sequence.

2.1.9.6 fn-bea:pad-left

The fn-bea:pad-left() functions add padding characters to the left of a string to create a fixed-length string. There are two variations of the function:

- The other uses the default character, which is a space (ASCII 32).

- One allows pad characters to be specified.

If the input string exceeds the requested length, only a substring as long as the length is returned.

Pad Left Function Using Default Character (ASCII 32)

The function has the following signature:

fn-bea:pad-left($str as xs:string?, $size as xs:integer?) as xs:string?

where string ($str) is returned with a specified number ($size) of characters (ASCII 32) prepended to the left of the string. The result is a string of length $size. It consists of $str prepended with $size - fn:length($str) space characters.

Examples:

- fn-bea:pad-left("abcd", 6) prepends spaces to the string up to the maximum 6 specified. The returned string is: "  abcd".

- fn-bea:pad-left("abcd", 2) returns only "ab" because characters are only prepended to the complete string. In addition, only the first two characters are returned since that is the setting of $size.

Additional notes:

- If either argument is an empty sequence, an empty sequence is returned.

- If $size is negative, a runtime exception occurs.

Pad Left Function with Specified Pad String

This function has the following signature:

fn-bea:pad-left($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string?

where string ($str) is returned with an arbitrary number ($size) of prepended characters with the pad string ($pad) replicated as many times as necessary.

Examples:

- fn-bea:pad-left("abcd", 6, "01") prepends a pad string to the string up to the maximum 6 specified. The returned string is: "01abcd".

- fn-bea:pad-left("abcd", 2, "01") returns only "ab" because characters are only prepended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.

- fn-bea:pad-left("abc", 6, "01") returns "010abc". Note that the prepended string is returned completely once and then partially up to the length ($size) specified.

Additional notes:

- If either argument is an empty sequence, an empty sequence is returned.

- If $size is negative, a runtime exception occurs.

2.1.9.7 fn-bea:pad-right

The fn-bea:pad-right() functions add padding characters to the right of a string to create a fixed-length string. There are two variations of the function:

- The other uses the default character, which is a space (ASCII 32).

- One allows pad characters to be specified.

If the input string exceeds the requested length, only a substring as long as the length is returned.

Pad Right Function Using Default Character (ASCII 32)

The function has the following signature:

fn-bea:pad-right($str as xs:string?, $size as xs:integer?) as xs:string?

where string ($str) is returned with a specified number ($size) of characters (ASCII 32) appended to the string. The result is a string of length $size. It consists of $str appended with $size - fn:length($str) space characters.

Examples:

- fn-bea:pad-right("abcd", 6) appends spaces to the string up to the maximum 6 specified. The returned string is: "abcd ".

- fn-bea:pad-right("abcd", 2) returns only "ab" because characters are only appended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.

Additional notes:

- If either argument is an empty sequence, an empty sequence is returned.

- If $size is negative, a runtime exception occurs.

Pad Right Function with Specified Pad String

This function has the following signature:

fn-bea:pad-right($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string?

where string ($str) is returned with an arbitrary number ($size) of appended characters with the pad string ($pad) replicated as many times as necessary.

Examples:

- fn-bea:pad-right("abcd", 6, "01") prepends a pad string to the string up to the maximum 6 specified. The returned string is: "abcd01".

- fn-bea:pad-right("abcd", 2, "01') returns only "ab" because characters are only appended to a complete string. In addition, only the first two characters are returned since that is the setting of $size.

- fn-bea:pad-right("abc", 6, "01") returns only "abc010". Note that the appended string is returned completely once and then partially up to the length ($size) specified.

Additional notes:

- If either argument is an empty sequence, an empty sequence is returned.

- If $size is negative, a runtime exception occurs.

2.1.10 Extended XQuery Data Model (XXDM) Functions

Oracle Data Service Integrator includes functions to support the Extended XQuery Data Model (XXDM). The XXDM represents instances of the XQuery Data Model (XDM) along with information about changes to the instances.

This section describes functions that you can use to convert XXDM instances to XDM instances.

2.1.10.1 fn-bea:current-value

The fn-bea:current-value() function returns an XDM instance representing the current value of the specified argument (discarding information about applied changes).

The function has the following signature:

fn-bea:current-value($changed as changed-element()) as element()?

where $changed is the XXDM instance.

2.1.10.2 fn-bea:old-value

The fn-bea:old-value() function returns an XDM instance representing the value of the specified argument prior to modification.

The function has the following signature:

fn-bea:old-value($changed as changed-element()) as element()?

where $changed is the XXDM instance.

Both the fn-bea:current-value and fn-bea:old-value functions are polymorphic.

Example:

The following function returns the salary difference for a customer before and after modification.

declare function salaryDifference($cus as changed-element
(cus:customer)) as xs:decimal {
fn:data(fn-bea:get-current-value($cus)/salary - fn:data(fn-
bea:get-old-value($cus)/salary)
}

The function does this by accessing the current and old versions of the customer element, extracting the salaries, and subtracting to determine the difference.

2.1.11 Using Mutators for Updates

Oracle Data Service Integrator offers several built-in XQuery functions that are useful for manipulating and applying changes to XML element instances. Using these new mutator functions, it is possible to create update data service operations that take as input scalar values that represent changes to be applied to data.

For example, you could create an update data service operation called updatePhoneNumber() which takes a string value containing the new phone number to be updated for a customer data service. In previous versions of Oracle Data Service Integrator, this type of update was not possible and instead required exposing an entire service data object (SDO) instance.

From a technical perspective, Oracle Data Service Integrator includes a set of built-in mutator functions that you can use to create and modify XXDM instances from within XQuery and XQuery Scripting Extensions (XQSE) programs. Mutator functions accept an XXDM instance, along with parameters specifying the modification, and return the modified XXDM instance.

This enables you to modify XXDM instances from within XQuery and XQSE programs without having to drop down to Java programming. Oracle Data Service Integrator uses the Extended XQuery Data Model (XXDM), an extension of the XQuery Data Model (XDM), to represent instances of the XDM along with information about changes to the instances.

You can use the mutator functions to create and modify XXDM instances from within XQuery and XQuery Scripting Extensions (XQSE) programs.

To use the mutator functions, do the following:

1. Identify the element you need to change and promote the element to an unmodified changed-element using the changed-element() function.
For example, the following call declares $co to be an unmodified changed-element of type orders for the element represented by $o.

declare $co as changed-element(orders) := changed-element($o);

2. Modify the changed-element using the mutator functions.
To insert a node into an XXDM instance (changed-element) at a specified XPath location, use the fn-bea:insert-into() function.

To delete a node at a specified XPath location in an XXDM instance, use the fn-bea:delete() function.

To replace the value of a node in an XXDM instance at a specified XPath location, use the fn-bea:replace-value() function.\

This section describes the following built-in mutator functions available in Oracle Data Service Integrator:

- Section 2.1.11.1, "fn-bea:changed-element"

- Section 2.1.11.2, "fn-bea:insert-into"

- Section 2.1.11.3, "fn-bea:delete"

- Section 2.1.11.4, "fn-bea:replace-value"

2.1.11.1 fn-bea:changed-element

The fn-bea:changed-element() function allows you to promote an element to an unmodified changed-element. You can then modify the resulting changed-element using the built-in mutator functions.

The fn-bea:changed-element() function has the following signature:

fn-bea:changed-element($e as element()) as changed-element()

The following call declares $cc to be an unmodified changed-element of type customer for the element represented by $c.

declare $cc as changed-element(customer) := fn-bea:changed-element($c);

2.1.11.2 fn-bea:insert-into

The fn-bea:insert-into() function enables you to insert a node into an XXDM instance at a specified XPath location.

The fn-bea:insert-into() function has the following signature:

fn-bea:insert-into($ce as changed-element(), $path as xs:string, $value as node()) as changed-element()

where

- $ce is the XXDM instance to be modified

- $path specifies a path into $ce using an SDO XPath fragment

- $value contains the node that is to be inserted

Consider the changed-element customer that has the following value bound to the variable $cc:

<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<last>Smith</last>
</name>
<address>...</address>
<address>...</address>
<orders>
<order>...</order>
<order>...</order>
<orders>
<customer>

Example—Inserting a New Element

Consider the following call:

set $cc := fn-bea:insert-into($cc, "name", <middle>Paul</middle>);

Assuming that the schema for customer permits an element for the middle name (middle) as a child of name and between the elements first and last, the call inserts the middle element as follows:

<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<middle>Paul</middle>
<last>Smith</last>
</name>
...
<customer>

Example—Inserting an Additional Element

Consider the following call:

set $cc := fn-bea:insert-into($cc, ".", <address>...<city>York</city></address>);

The call inserts an additional address element into the customer element, as follows:

<customer ssn="XXX-XX-XXX">
<name>...</name>
<address>...</address>
<address>...<city>York</city></address>
<address>...</address>
<orders>...</orders>
<customer>

Example—Inserting an Attribute

Consider the following call:

set $cc := fn-bea:insert-into($cc, "orders/order[2]", attribute paid { fn:true() });

The call inserts the paid attribute into the second order, as shown by the following.

<customer ssn="XXX-XX-XXX">
...
<orders>
<order>...</order>
<order paid="true">...</order>
<orders>
<customer>

2.1.11.3 fn-bea:delete

The fn-bea:delete() function enables you to delete a node at a specified XPath location in an XXDM instance. The function returns a copy of the instance that does not contain the specified descendant.

The fn-bea:delete() function has the following signature:

fn-bea:delete($ce as changed-element(), $path as xs:string) as changed-element()

where:

- $ce is the XXDM instance to be modified

- $path uniquely specifies a path to the descendant node of $ce that is to be deleted using the SDO XPath fragment

Assume that the variable $cust is bound to the value presented in the earlier example.

Example—Deleting an Element

Consider the following call:

set $cc := fn-bea:delete($cc, "address[2]");

The call deletes the second address child element of $cust, resulting in the value shown in the following:

<customer ssn="XXX-XX-XXX">
<name>...</name>
<address>...</address>
<orders>...<orders>
<customer>

Example—Deleting an Attribute

Consider the following call:

set $cc := fn-bea:delete($cc, "@ssn");

The call deletes the ssn attribute of $cust, resulting in the value shown in the following:

<customer>
<name>...</name>
<address>...</address>
<address>...</address>
<orders>...<orders>
<customer>

2.1.11.4 fn-bea:replace-value

The fn-bea:replace-value() function enables you to replace the value of a node in an XXDM instance at a specified XPath location. The function returns a copy of the instance containing the replaced value.

The fn-bea:replace-value() function has the following signature:

fn-bea:replace-value($ce as changed-element(), $path as xs:string, $value as xdt:anyAtomicType*) as changed-element()

where:

- $ce is the XXDM instance to be modified

- $path uniquely specifies a path to the descendant node of $ce that is to be modified using the SDO XPath fragment

- $value contains the value that is to replace the value of the targeted node

Note:

The function raises an error if the target node does not have simple content.

Assume that the variable $cc is bound to the value presented in the earlier example.

Example—Replacing the Contents of an Element

Consider the following call:

set $cc := fn-bea:replace-value($cc, "name/last", "Jones");

The call changes the content of the last child of name from Smith to Jones, as shown in the following:

<customer ssn="XXX-XX-XXX">
<name>
<first>Thomas</first>
<last>Jones</last>
</name>
...
<customer>

Example—Replacing an Attribute Value

Consider the following call:

set $cc := fn-bea:replace-value($cc, "@ssn", "YYY-YY-YYYY");

The call changes the content of the ssn attribute from XXX-XX-XXX to YYY-YY-YYYY, as shown in the following:

<customer ssn="YYY-YY-YYYY">
<name>...</name>
<address>...</address>
<address>...</address>
<orders>...<orders>
<customer>

Example—Removing the Contents of an Element

Consider the following call:

set $cc := fn-bea:replace-value($cc, "name/first", ());

The call removes the content of the first child element of name, as shown in the following:

<customer ssn="XXX-XX-XXX">
<name>
<first/>
<last>Smith</last>
</name>
...
<customer>

If the element first is nillable, the result would be nilled, as shown in the following:

<customer ssn="XXX-XX-XXX">
<name>
<first xsi:nil="true"/>
<last>Smith</last>
</name>
...
<customer>

2.1.12 Mutator Function Examples

This section provides several examples showing how to use the built-in mutator functions to perform common update operations.

- Section 2.1.12.1, "Update Based on Simple Parameters"

- Section 2.1.12.2, "Update Based on Complex Input Parameters"

- Section 2.1.12.3, "Update When the Parameter Structure Matches the Return Type"

- Section 2.1.12.4, "Update Based on Differences Between Old and New Values"

- Section 2.1.12.5, "Update Using Additional Data"

- Section 2.1.12.5, "Update Using Additional Data"

- Section 2.1.12.6, "Update Operation Replication"

2.1.12.1 Update Based on Simple Parameters

This example shows a data service update operation that accepts two simple input parameters, the customer ID (cid) and the Social Security Number (ssn).

declare procedure tns:update($cid as xs:string, $ssn as xs:string) {
declare $c as element(customer) := getCustomerByCustID($cid);
declare $cc as changed-element(customer) :=    fn-bea:changed-element($c);
set $cc := fn-bea:replace-value($cc, "ssn", $ssn);
tns:updateCUSTOMER($cc);
}

The update() operation retrieves the customer data by invoking the getCustomerByCustID() operation and updates the Social Security Number based on the ssn parameter. The example then updates the data source by calling the updateCUSTOMER() data service operation.

2.1.12.2 Update Based on Complex Input Parameters

This example shows a data service update operation that accepts complex input parameters. The example uses the following input parameters:

p_customer Parameter

<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
</p_customer >

p_address Parameter

<p_address>
<address_id>1</address_id>
<street1>1108 Delmas Ave</street1>
<street2></street2>
<address_type>HOME</address_type>
</p_address>

The getCustomerByCustID() operation returns the following data:

getCustomerByCustID() Return Data

<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Deer</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>San Jose</city>
<street1>123 Main Street</street1>
<street2>Apt 808</street2>
<address_type>HOME</address_type>
</address>
</customer>

Example—Replacing Values Based on Complex Input Parameters

The following example retrieves the customer data by invoking the getCustomerByCustID() operation, and returns the data in a hierarchical shape with a top-level node and multiple child nodes. The example then updates the Social Security Number (ssn) element using data supplied through the first parameter and updates the street element based on data in the second parameter.

Finally, the example updates the data source by calling the updateCUSTOMER() data service operation.

declare procedure tns:update($p1 as element(p_customer),
$p2 as element(p_address)) {
declare $c as element(customer) :=       getCustomerByCustID($p1/customer_id);
declare $cc as changed-element(customer) :=       fn-bea:changed-element($c);

set $cc := fn-bea:replace-value($cc, "ssn", fn:data($p1/ssn));

iterate $a at $i over $c/address {
if ($a/address_type eq $p2/address_type) {
declare $path1 as xs:string := concat("address[", $i, "]/street1");
declare $path2 as xs:string := concat("address[", $i, "]/street2");
set $cc := fn-bea:replace-value($cc, $path1, data($p2/street1));
set $cc := fn-bea:replace-value($cc, $path2, data($p2/street2));
}
}

tns:updateCustomer($cc);
}

Example—Inserting a New Element Based on Complex Input Parameters

The following example retrieves the customer data by invoking the getCustomerByCustID() operation, and returns the data in a hierarchical shape with a top-level node and multiple child nodes. The example then declares a new address element, populates the fields, and uses the insert-into() built-in function to insert the address into the customer element.

Finally, the example updates the data source by calling the updateCUSTOMER() data service operation.

declare procedure tns:update($p1 as element(p_customer),
$p2 as element(p_address)) {
declare $c as element(customer) :=       getCustomerByCustID($p1/customer_id);
declare $cc as changed-element(customer) :=       fn-bea:changed-element($c);

declare $addr as element(AddressType):=
<address>
<address_id>{data($p2/address_id)}</address_id>
<customer_id>{data($p1/customer_id}</customer_id>
<city>{data($p2/city)}</city>
<street1>{data($p2/street1)}</street1>
<street2>{data($p2/street1)}</street2>
<address_type>{data($p2/address_type)}</address_type>
</address>
set $cc := fn-bea:insert-into($cc, ".", $addr);

(: this call updates Customer and inserts new Address :)
tns:updateCustomer($cc);
}

2.1.12.3 Update When the Parameter Structure Matches the Return Type

This example shows a data service update operation that accepts a complex parameter as input, where the parameter structure matches the return type defined for the given data service.

The example uses the following input parameter:

p_customer Parameter

<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 Delmas Ave</street1>
<street2></street2>
<address_type>HOME</address_type>
</address>
<address>
<address_id>2</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<address_type>WORK</address_type>
</address>
</p_customer>

The getCustomerByCustID() operation returns the following data:

getCustomerByCustID() Return Data

<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Smith</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>Santa Clara</city>
<street1>350 El Camino Real</street1>
<street2>Test Street</street2>
<address_type>HOME</address_type>
</address>
</customer>

Example—Replacing all Values in a Complex Element

The following example invokes the updateCustomer() operation in the data service to replace all values in the underlying data source (the update is performed based on the primary key). This is possible because the structure of the input parameter matches the return type defined for the data service.

Note:

Note that the example includes a helper procedure that replaces the value of each child element of $ce with the value of the corresponding child element (using the same name) in $va.

declare procedure tns:replace-values($ce as changed-element(),
$path as xs:string,
$va as element()) as changed-element() {
declare $parent as changed-element() := $ce;
declare $child as element() := fn-bea:current-value($ce);

(: manual navigation according to $path :)
iterate $step over tokenize($path, "/|[") {
if (starts-with($step, "[") {
(: this is a filter (assuming positional), so just shave :)
(: the square brackets off and apply the same filter :)
set $index as xs:string := substring($step, 2, string-length($step) - 2);
set $child := $child[xs:integer($index)]
} else {
set $child := $child/[local-name() eq $step]
}
}

iterate $leaf over $child/* {
declare $vaChild as element() := $va/*[local-name() eq local-name($leaf)];
if (exists($vaChild)) {
declare $cpath = concat($path, "/", local-name($leaf));
set $parent := fn-bea:replace-value($parent, $cpath, data($vaChild));
}
}

return value $parent;
}

declare procedure tns:update($p1 as element(p_customer)) {
declare $cust as element(customer) := getCustomerByCustID($p1/customer_id);
declare $ucust as changed-element(customer) := fn-bea:changed-element($cust);

iterate $addr at $i over $p1/address {
declare $uaddr as element(AddressType) :=
<address>
<address_id>{ data($addr/address_id) }</address_id>
<customer_id>{ data($addr/customer_id) }</customer_id>
<city>{ data($addr/city) }</city>
<street1>{ data($addr/street1) }</street1>
<street2>{ data($addr/street1) }</street2>
<address_type>{ data($addr/address_type) }</address_type>
</address>
if (exists($cust/address[$i])) {
declare $path as xs:string := concat("address[", $i , "]");
set $ucust := tns:replace-values($ucust, $path, $uaddr);
} else {
set $ucust := fn-bea:insert-into($ucust, ".", $uaddr);
}
}

tns:updateCustomer($uCustomer);
}

2.1.12.4 Update Based on Differences Between Old and New Values

These examples show how to perform an update operation based on two complex parameters, one containing old values and the other containing potentially new values.

The example uses the following input parameters:

customer Parameter (New Values)

<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Smith</last_name>
<ssn>345-43-4988</ssn>
</customer>

customer Parameter (Old Values)

<customer>
<customer_id>1</customer_id>
<first_name>Johnny</first_name>
<last_name>Smithline</last_name>
<ssn>345-43-4988</ssn>
</customer>

Example—Updating Based on Parameter Differences Determined Through a Series of if Statements

The following example compares the two input parameters (the first containing old values and the second containing potentially new values) for differences using a series of if statements. The example then calls the updateCustomer() operation in the data service to update the data in the underlying data source if changes have been identified.

declare procedure tns:update($p_old as element(customer),
$p_new as element(customer)) {
declare $cust as changed-element(customer) := fn-bea:changed-element($p_old);
declare $modified as xs:boolean := false();

if (data($p_old/customer_id) eq data($p_new/customer_id) ) then {
if (data($p_old/first_name) ne data($p_new/first_name)) then {
set $cust := fn-bea:replace-value($cust, "first_name",
fn:data($p_new/first_name));
set $modified := true();
} else {}
if (data($p_old/last_name) ne data($p_new/last_name)) then {
set $cust := fn-bea:replace-value($cust, "last_name",
fn:data($p_new/last_name));
set $modified := true();
} else {}
if (data($p_old/ssn) ne data($p_new/ssn)) then {
set $cust := fn-bea:replace-value($cust, "ssn", fn:data($p_new/ssn));
set $modified := true();
} else {}
if ($modified) then {
tns:updatecustomer($c);
} else {}
} else {}
}

Example—Updating Based on Parameter Differences Determined Through Iteration

The following example compares the two input parameters for differences by iterating through the elements, and then calls the updateCustomer() operation in the data service to update the data in the underlying data source if changes have been identified.

declare procedure tns:update($p_old as element(customer),
$p_new as element(customer)) {
declare $cust as changed-element(customer) :=
fn-bea:changed-element($p_old);
declare $modified as xs:boolean := false();

if (data($p_old/customer_id) eq data($p_new/customer_id) ) then {
iterate $child over $p_old/* {
declare $name as xs:string := local-name($child);
declare $new := data($p_new/*[local-name() eq $name]);
if (data($child) ne $new) {
set $cust := fn-bea:replace-value($cust, $name, $new);
set $modified := true();
}
}
if ($modified) then {
tns:updatecustomer($c);
}
}
}

2.1.12.5 Update Using Additional Data

This example shows how to perform additional function calls to enrich the data before performing an update operation. The following input parameter is used:

p_customer Parameter

<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
</address>
</p_customer>

The getCustomerByCustID() operation returns the following data:

getCustomerByCustID() Return Data

<customer>
<customer_id>1</customer_id>
<first_name>John</first_name>
<last_name>Deer</last_name>
<ssn>123-12-1234</ssn>
<address>
<address_id>1</address_id>
<customer_id>1</customer_id>
<city>San Jose</city>
<street1>1108 First</street1>
<street2></street2>
<zip_code>95125</zip_code>
<address_valid>N</address_valid>
<country>US</country>
</address>
</customer>

The getValidAddress() operation accepts address information as a parameter and returns a standardized address element together with a validity code indicating whether the address is valid or not.

The getValidAddress() operation returns the following data:

getValidAddress() Return Data

<address>
<city>San Jose</city>
<street1>1108 1st Street</street1>
<street2></street2>
<zip_code>95131</zip_code>
<address_valid>Y</address_valid>
<country>US</country>
</address>

Example—Retrieving Additional Data Before Updating

The following example calls a read operation in the data service to retrieve additional information to include in the updated information. The example then replaces the appropriate values and calls the updateCustomer() operation to update the data in the underlying data source.

declare procedure tns:update($p1 as element(p_customer)) {
declare $c as element(customer) :=
getCustomerByCustID($p1/customer_id, p1/address_id);
declare $cc as changed-element(customer) :=
fn-bea:changed-element($c);

set $cc := fn-bea:replace-value($cc, "ssn", $p1/ssn);

declare $vAddress as element(ValidAddress) := tns:getValidAddress(
<ns1:address>
<city>{ data($p1/city) }</city>
<street1>{ data($p1/street1) }</street1>
<street2>{ data($p1/stree2) }</street2>
<country>{ data($p1/country) }</country>
</ns1:address>);

set $cc := fn-bea:replace-value($cc, "city", $vAddress/city)
set $cc := fn-bea:replace-value($cc, "street1", $vAddress/street1)
set $cc := fn-bea:replace-value($cc, "street2", $vAddress/street2)
set $cc := fn-bea:replace-value($cc, "zip_code", $vAddress/zip_code)
set $cc := fn-bea:replace-value($cc, "address_valid",
$vAddress/address_valid)
set $cc := fn-bea:replace-value($cc, "country", $vAddress/country)

tns:updateCustomer($cc);
}

2.1.12.6 Update Operation Replication

This example shows how to send changed data to two different data sources. The following input parameter is used:

p_customer Parameter

<p_customer>
<customer_id>1</customer_id>
<ssn>545-54-5445</ssn>
<first_name>Johan</first_name>
<last_name>Tyson</last_name>
<cud_operation>U</cud_operation>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
<cud_operation>U</cud _operation>
</address>
<address>
<address_id>1</address_id>
<city>San Jose</city>
<street1>1108 First St.</street1>
<street2></street2>
<country>US</country>
<cud_operation>D</cud _operation>
</address>
</p_customer>

Example—Updating Multiple Data Sources

The following example reads and updates customer information, replacing the data in the first data source and inserting the customer data in the second data source. Similarly, the example reads and updates the related address information, replacing the data in the first data source and inserting the address data in the second data source.

declare procedure tns:update($p1 as element(p_customer)) {
declare $c as element(customer) :=
getCustomerInfoByCustID(data($p1/customer_id));
declare $cc as changed-element(customer) := fn-bea:changed-element($c);
declare $curc as element(customer);

if (data($p1/cud_operation) eq "U") then {
set $cc := fn-bea:replace-value($cc, "first_name", data($p1/first_name));
set $cc := fn-bea:replace-value($cc, "last_name", data($p1/last_name));
set $cc := fn-bea:replace-value($cc, "ssn", data($p1/ssn));

(: this update goes into the first data source :)
updateCustomer($cc);

set $curc := fn-bea:current-value($cc);
(: this insert goes into the second data source :)
insertCustomer($curc);
}

{
declare $addr as element(address) :=
getAddressInfoByCustID(data($p1/customer_id));
declare $caddr as changed-element(address) := fn-bea:changed-element($addr);
declare $curaddr as element(address);
declare $paddr := $p1/address[1];

if (data($p1/cud_operation) eq "U") then {
set $caddr := fn-bea:replace-value($caddr, "city", data($paddr/city));
set $caddr := fn-bea:replace-value($caddr, "street1",
data($paddr/street1));
set $caddr := fn-bea:replace-value($caddr, "street2",
data($paddr/street2));
set $caddr := fn-bea:replace-value($caddr, "country",
data($paddr/country));

(: this update goes into the first data source :)
updateAddress($caddr);

set $curaddr :=
<address>
<address_id>{ data($paddr/address_id) }</address_id>
<city>{ data($paddr/city) }</city>
<street1>{ data($paddr/street1) }</street1>
<street2>{ data($paddr/street2) }</street2>
<country>{ data($paddr/country) }</country>
</address>

(:this insert goes into the second data source :)
insertAddress($curaddr);
}
}
}

2.1.13 Unsupported XQuery Functions

The following functions from the XQuery 1.0 specification are not supported in current Oracle XQuery engine implementation:

- fn:base-uri

- fn:id

- fn:idref

- fn:normalize-unicode

2.1.14 Restrictions Related to Client Ad-Hoc Queries

The following functions are not available to client applications for use in ad-hoc queries:

- fn:collection

- fn:doc

Note:

These functions are, however, available for use within data service XQuery functions and procedures.

2.1.15 Implementation-Specific Functions and Operators

This section describes Oracle-specific implementation details related to functions and operators.

Table 2-3 Implementation-Defined Values

Section

Description

Oracle Data Service Integrator XQuery Engine

6.2—Operators on Numeric Values [Overflow and Underflow during Arithmetic Operations]

Choice between raising an error and other options for overflow or underflow of numeric operations.

Arithmetic overflow and underflow follows behavior of the underlying Application Server's JVM (Java Virtual Machine).

6.2—Operators on Numeric Values [xs:decimal value digit precision]

Number of digits of precision for xs:decimal results

18 digits.

7.4.6—fn:normalize-unicode

In addition to supporting required normalization form "NFC", conforming implementations may also support implementation-defined semantics.

Not supported.

7.5—Functions Based on Substring Matching

Ability to decompose strings into collation units.

No collations supporting this feature are available.

10.1.1—Limits and Precision

Limits and precision for Durations, Dates and Times larger then those specified in XML Schema Part 2: Data Types

Fractional seconds are supported for more than 3 digits of accuracy: seven digits for serialized data (binXML package), 18 digits during computations.

15.5.4—Functions and Operators on Sequences [fn:doc]

Processing or document URI, usage of DTD or Schema for validation, handling of non-XML media types and construction of data model instances from non-XML resources and error handling for document processing.

fn:doc() function does not validate. Oracle Data Service Integrator uses predefined external functions for access to external XML and non-XML data sources.

2.2 Oracle XQuery Language Implementation

This section describes the Oracle XQuery language implementation, and contains the following topics:

- Section 2.2.1, "XQuery Language Support (and Unsupported Features)"

- Section 2.2.2, "Extensions to the XQuery Language in the Oracle Data Service Integrator XQuery Engine"

- Section 2.2.3, "Implementation-Defined Values for XQuery Language Processing"

2.2.1 XQuery Language Support (and Unsupported Features)

The Oracle Data Service Integrator conforms to the W3C Working Draft "XQuery 1.0: An XML Query Language" dated 23 July 2004 (http://www.w3.org/TR/2004/WD-xquery-20040723/), with these exceptions:

- Modules are not supported

- xs:integer is represented by 64-bit values

2.2.2 Extensions to the XQuery Language in the Oracle Data Service Integrator XQuery Engine

Beyond compliance with the specification, Oracle's XQuery language implementation (the Oracle Data Service Integrator XQuery engine) extends the XQuery language via the following:

- Section 2.2.2.1, "Generalized FLWGOR (group by)"

- Section 2.2.2.2, "Optional Indicator in Direct Element and Attribute Constructors"

2.2.2.1 Generalized FLWGOR (group by)

Oracle offers a group by clause extension to standard FLWOR expressions. The following EBNF shows the syntax of the general FLWGDOR:

flwgdorExpression := (forClause | letClause) (forClause
| letClause
| whereClause
| groupbyClause
| orderbyClause)* returnClause
groupbyClause := "group" [variable "as" variable] "by" (expression
["as" variable]) ("," (expression ["as" variable]))*

The remaining clauses referenced in the EBNF fragment follow the standard definition, as presented in the XQuery specification.

As an example, consider the problem of grouping books by year, without losing books that do not have a year attribute. Using standard XQuery, you would need to perform a self-join with the result of the fn:distinct-values() function, concatenating the result of the self-join with the result for books without a year attribute.

The following illustrates an XQuery expression that can be used to accomplish this:

let $books := document("bib.xml")/bib/book return (
for $year in fn:distinct-values($books/@year)
return
<g>
<year>{ $year }</year>
<titles>{ $books[@year eq $year]/title }</titles>
</g>,
<g>
<year/>
<titles>{ $books[fn:empty(@year)]/title }
</g>
)

Using the Oracle group by extension function, you could write the same query as follows:

for $book in document("bib.xml")/bib/book
group $book as $partition by $book/@year as $year
return
<g>
<year>{ $year }</year>
<titles>{ $partition/title }</titles>
</g>

The following shows book bindings before and after the group by clause is applied:

Bindings Before Group By Clause is Applied

$book

<book year="1994" ISBN="147...">...</book>
<book year="1994" ISBN="198...">...</book>
<book year="2000" ISBN="123...">...</book>

Table 2-4 Bindings After Group By Clause is Applied

$year

$partition

1994

(<book year="1994" ISBN="147...">...</book>,

<book year="1994" ISBN="198..."> ...</book>)

2000

<book year="2000" ISBN="123..."> ...</book>

The FLWGOR expression conceptually builds a sequence of binding tuples, where the size of the tuple is the number of variables in scope at that point in the FLWGOR. In the example, the tuple at the group by clause consists of a single variable binding $book which binds to each book in the bib.xml document, one book at a time.

The group by creates a new sequence of binding tuples with each output tuple containing variables defined in the group by clause. After the group by, all variables there were previously in-scope go out of scope.

In the example, the output tuple from the group by clause is of size two with the variable bindings being for $year and $partition (see Table 2-1).

The number of output tuples is equal to the number of unique group by value bindings. In the above example, this is the number of unique book/@year values: 2. The variable introduced in the group clause ($partition in the example above) binds to the sequence of all matching input values.

2.2.2.2 Optional Indicator in Direct Element and Attribute Constructors

This extension enables external consumers of XML generated by XQuery to have certain empty elements and attributes omitted. You can specify this using optional indicators, instead of employing computed constructors, conditional statements, and custom functions.

For example, consider the following query:

<a><b>{()}</b><c foo="{()}"/></a>,

The extension enables the following to be returned:

<a><c/></a>

instead of:

<a><b/><c foo=""/></a>

The extension uses the optional indicator '?' with direct element and attribute constructors. This means that in the following you could change the production DirElemConstructor to the following:

DirElemConstructor    ::=    "<" QName "?"? DirAttributeList
("/>" | (">" DirElemContent* "</" QName S? ">")) /* ws: explicit */

Likewise, you could change the DirAttributeList to the following:

DirAttributeList    ::=    (S (QName "?"? S? "=" S?
DirAttributeValue)?)*

When ? is present, elements with no children and attributes with the value "" are omitted. The query in the example could then be written as:

<a><b?>{()}</b><c foo?="{()}"/></a>

which produces the following result:

<a><c/></a>

In another example, consider the case of constructing a new customer element with different tags. One requirement is that you do not want a phone element in the resulting customer when the phone number does not exist in the original customer. Using standard XQuery, you would have to write:

for $cust in CUSTOMER()
return
<customer>
<id>{ fn:data($cust/C_ID) }</id>
{
if (fn:exists($cust/PHONE))
then <phone>{ fn:data($cust/PHONE) }</phone>
else ()
}
...
</customer>

Using the optional element constructor, you could instead write the following:

for $cust in CUSTOMER()
return
<customer>
<id>{ fn:data($cust/C_ID) }</id>
<phone?>{ fn:data($cust/PHONE) }</phone>
...
</customer>

Similarly, when you want the resulting customer element to use attributes instead of elements, you would need to employ computed attribute constructors using standard XQuery, as illustrated by the following:

for $cust in CUSTOMER()
return
<customer id="{ fn:data($cust/C_ID) }"
{
if (fn:exists($cust/PHONE))
then attribute { "phone" } { fn:data($cust/PHONE) }
else ()
}
...
/>

Using the optional attribute constructor, the query becomes:

for $cust in CUSTOMER()
return
<customer id="{ fn:data($cust/C_ID) }"
phone?="{ fn:data($cust/PHONE) }"
...
/>

2.2.3 Implementation-Defined Values for XQuery Language Processing

This section describes the Oracle-specific implementation details related to XQuery language processing.

Table 2-5 Implementation-Defined Values

Section

Description

Oracle Data Service Integrator XQuery Engine

2.1.2 Dynamic Context

Implicit timezone (value of type xdt:dayTimeDuration) that will be used when a date, time, or dateTime value that does not have a timezone is used in a comparison (or any other operation).

Timezone of the JVM of the underlying application server.

2.5.1 Kinds of Errors—Static Error

Mechanism for reporting static errors (errors that must be detected during the analysis phase, such as syntax errors).

Parser and compiler APIs throw Java exceptions

2.5.1 Kinds of Errors—Warnings

In addition to static, dynamic, and type errors, an XQuery implementation can (optionally) raise warnings during the analysis or evaluation phases, in response to specific conditions.

Provides a WarningListener API, but has no special warnings defined for the core XQuery language implementation

2.6.3 Full Axis Feature

Set of optional axes when Full Axis Feature is not supported

None.

2.6.6.1 Must-Understand Extensions; the XQuery flagger

Mechanism by which the XQuery flagger (which flags queries containing 'must understand' extensions) is enabled, if at all. By default the flagger is disabled.

XQuery flagger is not supported.

2.6.7.1 Static Typing Extensions; the XQuery static flagger

Mechanism by which the XQuery static flagger is provided, if at all.

XQuery static flagger is not supported.

3.1.1 Literals

Choice of XML 1.0 or XML 1.1 for character references (the XML-style references for Unicode characters, such as &#0151; for an em-dash).

XML 1.0

3.7.1.2 Namespace Declaration Attributes

Support for XML Names 1.1

No

3.8.3 Order By and Return Clauses

Ordering specification (orderspec) can be implemented as empty least or empty greatest (for evaluating greater-than relationship between two orderspec values in an order by clause of an XQuery).

Empty least.

4.10 Module Import

String literals following the at keyword are optional location hints in module import statements that can be interpreted (or disregarded) by the implementer.

Not applicable—Since the Oracle Data Service Integrator XQuery engine does not support modules, there is no implementation.

4.13 Function Declaration

Protocol by which parameters are passed to an external function and the result of the function is returned to the invoking query.

Set of Java APIs provided.

A.2 Lexical structure

Lexical rules can follow XML 1.0 and XML Names, or XML 1.1 and XML Names 1.1.

XML 1.0 and XML Names

3 XQuery Engine and SQL

This chapter provides an overview of how Oracle Data Service Integrator works with relational data, and describes what happens when a relational data source is imported into Oracle Data Service Integrator.

The chapter also explains how SQL data types are mapped to XQuery data types and describes what happens during runtime after deploying a data-service-enabled application. The chapter further explains how queries are handled and describes the kind of performance you can expect.

This chapter covers the following topics:

- Section 3.1, "Introduction"

- Section 3.2, "XQuery-SQL Data Type Mappings"

- Section 3.3, "SQL Pushdown: Performance Optimization"

- Section 3.4, "Preventing SQL Pushdown"

Note that while the graphical-user interface tools handle many of the details, SQL developers and application-performance tuning experts should understand how Oracle Data Service Integrator works with relational data so that they can:

- Create well-designed canonical data services that are potentially re-usable throughout an organization

- Test and tune alternative query approaches

- Validate execution paths for queries and identify opportunities to improve overall performance

Note:

For simplicity's sake, this chapter refers to the XQuery engine throughout when in fact some of the specific functionality is handled by other, ancillary sub-systems (for example, the Data Source API or other system components depicted in the "Oracle Data Service Integrator Components Architecture" figure in the Concepts Guide).

3.1 Introduction

At the core of Oracle Data Service Integrator is the data processing engine, often referred to as simply the XQuery engine—the robust, enterprise-class implementation of the XQuery language based on the standards listed in Section 1.3, "Supported XQuery Specifications," with additional enhancements as detailed in Chapter 2, "Oracle's XQuery Implementation."

In addition to compliance with XQuery and XML recommendations, Oracle Data Service Integrator XQuery engine also complies with the ANSI/ISO standard that bridges the SQL and XML worlds (the "SQL/XML (ISO-ANSI Working Draft) XML-Related Specifications" WD 9075-14 (SQL/XML), August, 2002).

As a Java application (J2EE server application), Oracle Data Service Integrator uses JDBC to generate SQL queries and submit them to the appropriate RDBMSs that comprise a data service, which means Oracle Data Service Integrator must accommodate differences in both SQL and JDBC, as follows:

- SQL Language. The SQL standard has evolved over time, and vendor implementations (in their respective RDBMS products) may be at any number of stages of compliance with the standard (SQL-89, SQL-92, SQL:1999, and SQL:2003, for example). Furthermore, vendors implement various extensions to SQL in their respective RDBMS products. In short, Oracle Data Service Integrator's support for SQL is not a "one-size-fits-all" exercise: achieving optimal integration with relational data sources requires Oracle Data Service Integrator to generate vendor-specific SQL code at times.

- JDBC API. Drivers are provided by RDBMS vendors as well as third-parties; various drivers for each RDBMS can have different levels of JDBC compatibility.

Given these factors, Oracle Data Service Integrator provides two different levels of SQL support for relational database management systems (RDBMS): base support and core support, as defined in the next section.

3.1.1 Base and Core RDBMS Support

Oracle Data Service Integrator provides two different levels of support for relational data sources:

- Base support. Oracle Data Service Integrator generates standard SQL code that is minimally required to be supported by any SQL RDBMS. Some examples of base platforms would include Oracle 7, Informix, IDMS, and MySQL.

- Core support. Oracle Data Service Integrator supports the native SQL dialect of specific versions of several leading commercial RDBMSs using the RDBMS-specific-JDBC of the vendor's JDBC driver or Oracle's JDBC driver (see Table 3-1).

Table 3-1 Core Oracle Data Service Integrator RDBMS Support

RDBMS and Versions

Vendor Driver

Oracle WebLogic Driver

IBM DB2 9.7, 10.1

IBM DB2 JDBC driver

WebLogic JDBC Driver for DB2 Type 4

Microsoft SQL Server 2008 R2, 2012

Microsoft JDBC driver 4.0 for SQL Server

WebLogic JDBC Driver for Microsoft SQL Server Type 4

Oracle 11.x, 12.x

Oracle JDBC Thin driver, version 12.1

WebLogic JDBC Driver Type 4

Sybase Adaptive Server Enterprise 15.7

Sybase jConnect driver, version 6.05

WebLogic JDBC Driver Type 4

Teradata 13.x, 14.x

Teradata JDBC driver

N/A

3.1.2 How the XQuery Engine Supports SQL Data Sources

Oracle Data Service Integrator supports SQL (relational) data sources throughout the life-cycle of a data services project, from metadata import, through query plan optimization, through runtime execution of queries and delivery of data to an end-user (or other) application. Specifically, the XQuery engine provides:

- Metadata Mapping. Importing metadata from relational data sources is the first step in creating a data service.

- Data Type Mapping. Upon import of metadata, Oracle Data Service Integrator maps data types from the RDBMS data source into XQuery atomic data types, disregarding length and other constraints. If the data source tables or views include unsupported data types — an array, for example — the column is ignored (the GUI tool alerts the person performing the import if this issue arises, and enables the person to map the data type of the source table or view to a specific XQuery data type).

- Query Optimization. The XQuery processing engine is fast and efficient, and uses several optimizing strategies, including:

- SQL pushdown. As much as possible, processing is shifted from the XQuery engine to the native RDBMS so that smallest practical result set is actually processed by the XQuery engine.

- Lazy evaluation. Queries are executed against the physical data sources only as far as necessary to obtain results.

- Connection-sharing. Multiple active queries can run over a single connection (assuming the data source RDBMS allows; see Table 3-2).

3.1.2.1 Metadata and Data Type Mappings Get Stored in Annotated Files

For each of the tables and views whose metadata is imported into Oracle Data Service Integrator (using Import Source Metadata feature of the GUI), two files are generated:

- Entity data service (.ds) file that defines the main access function (an external XQuery function with annotations that specify the RDBMS catalog or schema name and other properties) to access to the table or view data and return a sequence of elements corresponding to the rows of the underlying table. The .ds file includes numerous annotations to handle metadata about the data service, including:

- Relational provider identifier.

- Table structure information, including column names (field names), SQL data types and corresponding XQuery data types, primary key, and foreign key information.

- Relationship functions that provide access to related tables or views.

- Relationship annotations.

- JNDI lookup information. The <relationalDB> annotation in the data service file provides the JNDI name that will be used at runtime to obtain a connection to the data source and execute queries.

- XML Schema definition (.xsd) file that includes information about all the columns of the table (or view) and the data types for those columns, as mapped into the XQuery data types.

3.1.2.2 Runtime Connection Management—Connection Sharing

At runtime, the XQuery engine:

- Obtains a connection to the RDBMS.

- Prepares SQL statements, setting up parameters if necessary.

- Executes the SQL statements and releases the connection.

- Handles errors and exceptions.

- Translates the result of the query to the XML model used by XQuery engine.

Database connections (connection pools) are registered in the JNDI (Java naming and directory interface) tree of the WebLogic Server (an administrator with privileges on the server can configure connection pool, data source, and JNDI name by which connection pools are accessible).

When sub-plan execution completes, connections are typically not released back to the WebLogic Server. The XQuery engine holds the connection for the duration of the entire XQuery — not just the duration of the SQL — enabling subsequent queries to the same relational data source to be executed using an already obtained connection (which also improves performance). Whether the XQuery engine can share connections or not depends on the underlying data source and JDBC driver (see Table 3-2).

If the data source RDBMS or JDBC driver does not support connection sharing, and if the Oracle Data Service Integrator has opened multiple connections to the same data source, the XQuery engine keeps the initial connection to a data source open during XQuery execution but releases any subsequent connections to the same data source once the SQL result is received in its entirety by the XQuery engine. The initial connection will be re-used subsequent SQL queries when the connection becomes available.

Table 3-2 Runtime Connection Management

RDBMS

Support

Base RDBMS

No connection sharing.

IBM DB2 10.1, 9.7

Microsoft SQL Server 2008 R2, 2012

Oracle 11.x, 12.x

Sybase Adaptive Server Enterprise 15.7

Single shared connection for each JNDI data source; each connection supports multiple active SQL queries.

Teradata 14.x, 13.x

No connection sharing. Each access requires dedicated connection.

3.2 XQuery-SQL Data Type Mappings

XQuery-SQL data type mappings are specific to the RDBMS version and the JDBC driver, as discussed in Section 3.1.1, "Base and Core RDBMS Support." The specific data type mappings for each core RDBMS and the general mappings for any base RDBMS are detailed in Appendix A. However, XQuery and SQL differ in some respects that may affect XQuery-to-SQL translation; these differences apply to all RDBMSs:

- Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision"

- Section 3.2.4, "Scope Differences for Expressions and Data Types"

3.2.1 Date and Time Data Type Differences: Timezones and Time Precision

The XQuery language defines richer data types than SQL for handling date and time information (temporal data). These data types provide more information (timezone data, for instance) or greater degree of precision (unlimited number of fractional seconds as part of a time or date, for example). The three built-in XQuery data types for data and time information are:

- xs:dateTime

- xs:date

- xs:time

Minimally, every RDBMS has a single datatype that conveys both date and time data. This datatype maps to XQuery's xs:dateTime data type. Some RDBMSs offer additional SQL data types for storing date and time data separately (see Table 3-3)

(Of all the RDBMSs supported by Oracle Data Service Integrator, only Oracle 11.x (and higher) offers data types with timezone data (TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE).

Table 3-3 Temporal Data Type Mappings

Database

xs:date

xs:dateTime

xs:time

Base RDBMS

Reported by JDBC driver for the specific RDBMS

Reported by JDBC driver for the specific RDBMS

Reported by JDBC driver for the specific RDBMS

IBM DB2/NT 8

DATE

TIMESTAMP

TIME

Microsoft SQL Server 2000

N/A

DATETIMEFoot 1 , SMALLDATETIMEFoot 2

N/A

Oracle 8.1.x

N/A

DATEFoot 3

N/A

Oracle 9.x, 10.x

N/A

DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIMEZONE, TIMESTAMPWITH TIMEZONE

N/A

Sybase Adaptive Server Enterprise 12.5.2 (and higher)

DATE

DATETIME, SMALLDATETIME

TIME

Footnote 1 Supports fractional seconds up to 3 digits (miliseconds).

Footnote 2 Accuracy of 1 minute.

Footnote 3 Provides both date and time data, but supports neither fractional seconds nor timezone data (fractional-second data is truncated).

Oracle Data Service Integrator XQuery engine maps all SQL date and time data types to XQuery data types (for example, during metadata import of a new data source) without loss of data or precision.

However, the converse is not true: depending on the specific RDBMS (and JDBC driver) for a specific data source, the XQuery engine may need to perform additional processing to minimize data loss and to handle the timezone information when mapping XQuery temporal data types to SQL.

3.2.2 How Oracle Data Service Integrator Handles Timezone Information

When a query is being pushed down to an RDBMS that does not support timezone data, the Oracle Data Service Integrator XQuery engine converts date and time data into the local time of the underlying application server and removes the timezone information. The conversion occurs each time a date or time value that includes timezone data is sent to the data source, as follows:

- During compile time, when SQL is generated for constant date or time expressions.

- During query run time, when executing parameterized SQL with parameters bound to date/time values.

- During update, when a date or time value must be stored in the RDBMS.

3.2.3 How Oracle Data Service Integrator Handles Fractional Seconds

The XQuery language supports unlimited precision for fractional seconds, while the Oracle Data Service Integrator XQuery engine supports up to 7 digits only (for fractional seconds). However, depending on the specific RDBMS, fractional second support may be far less than 7 digits. In translating from XQuery to SQL, Oracle Data Service Integrator truncates fractional seconds to the precision supported by that RDBMS.

For example, since Microsoft's DATETIME data type supports up to 3 digits (milliseconds) for fractional time precision, when Oracle Data Service Integrator sends a datetime value to Microsoft SQL Server 2008, the value is first converted into the local time zone and then any fractional seconds are converted to the 3-digit-milliseconds allowed.

If fractional-second-precision is required (but the data source does not support it appropriately), use the fn-bea:fence() function to disable pushdown of date and time data types and operations, so that the XQuery engine processes the time- and date-related queries. (See Section 3.4, "Preventing SQL Pushdown" for more information.)

See Appendix A for more information about time and date data types for core and base RDBMS.

3.2.4 Scope Differences for Expressions and Data Types

The XQuery language is less restrictive than the SQL language in terms of the scope of expressions and data types. For example, for most all RDBMSs, an SQL query that returns a boolean can only be used inside a WHERE clause. XQuery does not have such restrictions, and as a result, in some cases, valid XQuery expressions cannot be pushed down. Expressions and data types that cannot be pushed include:

- Expressions returning boolean type can only be used in the WHERE clause (all RDBMSs)

- Some data types, such as CLOB, can be returned in the project list but cannot be grouped on or sorted on (depending on the RDBMS' SQL dialect; see Appendix A for details).

- Aggregate functions inside an ordering expression, such as in ORDER BY clauses, are not pushed down for any base RDBMS (but is supported by all other RDBMSs). See Appendix A for more information.

3.3 SQL Pushdown: Performance Optimization

Oracle Data Service Integrator achieves optimal performance for queries by performing SQL pushdown. Pushdown is an optimization technique that offloads processing from the XQuery engine by sending native SQL queries to the data source so that minimal result sets necessary to answer the query get processed by the XQuery engine.

SQL pushdown reduces the amount of data transported and processed by Oracle Data Service Integrator XQuery processing engine. This technique dramatically improves overall performance, especially when joining tables.

For example, a JOIN operation on two tables can be done by the underlying RDBMS, returning only the final result, rather than delivering all the data to the XQuery engine for processing the JOIN condition. Sorting criteria are also handled by the data source, eliminating the need to re-sort the data inside the XQuery engine.

For all core RDBMSs, the XQuery engine identifies the XQuery constructs and operations that can be translated into equivalent SQL operations. These include:

- Basic language constructs, including constants, variables, path expressions, functions and operators, and cast operations.

- Common query patterns, such as selections and projections (where clauses), joins (inner, outer, semi-join, anti-semi-join), ordering clauses, groupings and aggregations.

Not all queries can (or should) get pushed down. The XQuery engine does not pushdown:

- Cross-joins. Any join without a condition (any join that results in a Cartesian product)

- Expressions tagged with the fn-bea:fence() function.

The remainder of this section covers SQL pushdown in more detail, providing syntax samples based on the table structures shown in Figure 3-1. (For ease of reading, namespace references are not shown in the example queries.) In some cases, the query may not get pushed down as SQL, but the fragments of the query — names of columns, for example — may get pushed to the project list.

Figure 3-1 Table Structures for SQL Pushdown Examples

3.3.1 Function and Operator Pushdown

XQuery functions and operators are translated into SQL only when:

- all arguments can be pushed down directly (or as parameters)

- at least one of the argument expressions uses a value from the relational data source

- the XQuery function or operator has an equivalent SQL expression with equivalent semantics

- data type of the result is supported

Table 3-4 shows an XQuery statement and its corresponding "pushdown" or SQL translation. (Oracle syntax is used.)

Table 3-4 Function Pushdown Example

XQuery Statement

SQL Translation (Oracle Syntax)

for $c in CUSTOMER()

return lower-case($c/LAST_NAME)

SELECT LOWER(t1."LAST_NAME") AS c1

FROM "CUSTOMER" t1

If some arguments to a function or operator are not directly pushable, but can be replaced with parameters, the XQuery engine will replace the arguments with parameters and pushdown the SQL. For example, since the XQuery's string-join() function has no explicit SQL equivalent, it is replaced with a parameter (see Table 3-5).

Table 3-5 External Variable Pushdown

XQuery Statement

SQL Statement

declare variable $p as xs:string external;

...

for $c in CUSTOMER()

where starts-with($c/LAST_NAME, string-join( ("a", "b"), $p ))

return $c/FIRST_NAME

SELECT t1."FIRST_NAME" AS c1

FROM "CUSTOMER" t1

WHERE t1."LAST_NAME" LIKE ?

Aggregate Functions

Oracle Data Service Integrator translates XQuery 1.0 and XPath 2.0 aggregate functions into corresponding SQL aggregate functions (Table 3-6).

Table 3-6 Aggregate Functions

XQuery Aggregate Function

SQL Aggregate Function

fn:avg()

AVG()

fn:count()

COUNT()

fn:max()

MAX()

fn:min()

MIN()

fn:sum()

SUM()

fn:count(fn:distinct-values()

COUNT(DISTINCT …)

Note that the distinct-values() XQuery aggregate function in conjunction with the fn:count() function is further translated into an SQL COUNT(DISTINCT...) operation, as shown in Table 3-7. See Section 3.3.8, "Grouping and Aggregation" for some examples of how aggregate functions in conjunction with other expressions affect the outcome of SQL pushdown.

3.3.2 Parameters in Generated SQL Statements

The Oracle Data Service Integrator XQuery engine generates parameters from variables, functions, operators, and cast operations as needed for use by the SQL engine. If all arguments to a function are parameters, the entire function gets pushed as a parameter.

The functions that can be pushed down depend on the database. See the Appendix A for details.

3.3.3 Cast Operation Pushdown

As with functions and operators, support for cast operation pushdown is RDBMS-specific, although cast pushdown is available only for core (not base) RDBMSs. The XQuery engine can pushdown cast operations if the data source RDBMS:

- has equivalent SQL data types for both source and target of the cast XQuery data types (see Chapter 3, "XQuery Engine and SQL" for details).

- has a semantically equivalent SQL operation to convert from source data type to target data type.

Table 3-7 shows an example of how a cast in XQuery would get pushed down to a Microsoft SQL Server 2000 data source.

Table 3-7 Cast Operation Pushdown

XQuery Statement

SQL Statement (Microsoft SQL Server 2000 Syntax)

for $c in CUSTOMER()

where xs:string($c/ZIP_CODE) eq "95131"

return $c/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE CAST(t1."ZIP_CODE" AS VARCHAR) = '95131'

3.3.4 Path Expressions Pushdown

The XQuery engine maps table columns to XML elements that are children of the corresponding row elements. Simple XQuery path expressions are recognized by the XQuery engine as column accessors. For example, $c/ZIP_CODE and $c/LAST_NAME (see Table 3-9) provide access to ZIP_CODE and LAST_NAME columns.

3.3.5 Constant Pushdown

The Oracle Data Service Integrator XQuery engine translates XQuery constants into SQL constants only if the data source has an equivalent SQL data type. Table 3-8 shows an example of a constant used in a FLWOR expression and how that constant gets translated in the SQL statement.

Table 3-8 SQL Pushdown for Constants

XQuery Statement

SQL Statement

for $c in CUSTOMER()

where $c/ZIP_CODE eq 95131

return $c/LAST_NAME

SELECT t1."LAST_NAME" AS c1

FROM "CUSTOMER" t1

WHERE t1."ZIP_CODE" = 95131

3.3.6 Variable Pushdown

Both external and internal variables in XQuery expressions can be translated into SQL parameters (in generated SQL statements) when the variable's datatype is supported by the XQuery engine and:

- is atomic (static data type).

- can be translated into equivalent SQL type.

Table 3-9 shows an example of variable pushdown.

Table 3-9 Variable Pushdown

XQuery Statement

SQL Statement

declare variable $extVar

as xs:string external;

for $c in CUSTOMER()

where $c/CUSTOMER_ID eq $extVar

return $c/LAST_NAME

SELECT t1."LAST_NAME" as c1

FROM "CUSTOMER" t1

WHERE t1."CUSTOMER_ID" = ?

3.3.7 Common Query Patterns

For each relational data source, the precise set of expressions pushed down depends on the capabilities of the underlying RDBMS; for details, see Chapter 3, "XQuery Engine and SQL."

3.3.7.1 Simple Projection Queries

Each of the example XQueries shown in Table 3-10 returns elements containing values of LAST_NAME columns from a CUSTOMER table. In all cases, the SQL statement generated by the XQuery engine is the same (see Table 3-10).

Table 3-10 Projection Query

XQuery Statements

SQL Statement

for $c in CUSTOMER() return $c/LAST_NAME

CUSTOMER()/LAST_NAME

for $c in CUSTOMER() return data($c/LAST_NAME)

data(CUSTOMER()/LAST_NAME)

SELECT t1."LAST_NAME" AS

c1 FROM "CUSTOMER" t1

The difference between the first two queries and the last two queries is that the fn:data() function is used in the query to limit the results to values only. Without the fn:data() function, the result is a list of <LAST_NAME> elements containing corresponding column values. If a column value is NULL, the element is skipped. With the fn:data() function, the result is the actual values.

3.3.7.2 Where Clause Pushdown

An XQuery where clause is usually translated into an SQL WHERE clause. An XQuery where clause gets pushed down as SQL when:

- the where expression uses at least one value from a relational source.

- the where expression is pushable (using parameters if needed). See Section 3.3, "SQL Pushdown: Performance Optimization" for more information.

Table 3-11 shows an example of a where clause pushdown.

Table 3-11 Where Clause Pushdown

XQuery Statements

SQL Statements

for $c in CUSTOMER()

where $c/CUSTOMER_ID eq "CUSTOMER01"

return $c/LAST_NAME

SELECT t1."LAST_NAME" AS c1

FROM "CUSTOMER" t1

WHERE t1."CUSTOMER_ID" = 'CUSTOMER01'

for $c in CUSTOMER()

where year-from-dateTime($c/BIRTH_DAY)

eq

year-from-date(current-date())

return

$c/LAST_NAME

(DB2 syntax)

SELECT t1."LAST_NAME" AS c1

FROM "CUSTOMER" t1

WHERE

YEAR(t1."BIRTH_DAY") = ?

However, note that if the WHERE clause follows a group by clause, the WHERE clause is translated into a HAVING clause. See Section 3.3.8.4, "Group-By with a Nested Where Clause Translates to SQL HAVING Clause").

3.3.7.3 Order By Clause Pushdown

An XQuery order by expression comprises:

- ordering expression

- direction property for each ordering expression; that is, ascending or descending

- empty ordering property for each ordering expression; that is, empty least or empty greatest

The XQuery engine can pushdown SQL for ordering expressions, including properties, only when the ordering expression:

- is pushable and uses data from the database.

- is of the kind supported by the underlying data source (some RDBMSs can only support order by columns, not arbitrary expressions; some RDBMSs support non-column expressions in order by clause only if they do not contain aggregate functions.

- when an empty expression can result in empty sequence, the RDBMS must support the same NULL order as the empty order specified by the XQuery. (Some RDBMSs have fixed NULL order, some allow NULL order to be specified—see Chapter 3, "XQuery Engine and SQL" for details).

Table 3-12 shows an example of an order by clause pushdown.

Table 3-12 Order By Pushdown

XQuery Statement

SQL Statement

for $c in CUSTOMER()

order by $c/CUSTOMER_ID  descending

return $c/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

ORDER BY t1."CUSTOMER_ID" DESC

Table 3-13 shows an example of the SQL pushdown that occurs when ordering by a NULLable column (ADDRESS2) in the XQuery clause when the RDBMS supports dynamic setting of NULL order.

Table 3-13 Order By Query When Setting NULL Order Dynamically

XQuery Statement

SQL Statement (Oracle Syntax)

for $c in CUSTOMER()

order by $c/ADDRESS2 ascending

empty greatest

return $c/CUSTOMER_ID, $c/ADDRESS2

SELECT t1."CUSTOMER_ID" AS c1,

t1."ADDRESS2"    AS c2

FROM "CUSTOMER" t1

ORDER BY t1."ADDRESS2" ASC NULLS LAST

If the data source RDBMS does not support the required empty (NULL) order, the order by will not be pushed down.

As another optimization, the Oracle Data Service Integrator XQuery engine can insert order by clauses into generated SQL statements—even when the original XQuery statement does not include them—to offload expensive sorting operations to the RDBMS. They are automatically inserted by the XQuery optimizer prior to execution. You can see these as well in the Query Plan View.

3.3.7.4 Inner Join Pushdown

Joining data from multiple sources is a very common data integration task. In SQL terms, an inner join relates each row in one table (or view) to one or more corresponding rows in another table or view. In XQuery, an inner join is expressed as a FLWR expression comprising several for clauses that iterate over the data sources, where clauses that specify the join predicates, and a return clause returning data values.

If two relational sources are located in the same database, the inner join can sometimes be pushed down as a single SQL statement using either SQL-92 or SQL-89 syntax, depending on the RDBMS of the data source.

An inner join can be pushed down when:

- the condition itself is pushable.

- both join branches belong to the same RDBMS and can be addressed from a single SQL statement (both branches are in the same JNDI data source).

- join condition exists and uses values from both branches (cross joins are not pushed down).

Figure 3-2 XQuery Inner Join Pattern

Although the example in Figure 3-2 shows a simple inner join between two branches, the XQuery engine also supports n-way joins, with each branch comprising a different for statement. See also Table 3-14.

Table 3-14 Rendering of XQuery Inner-Join as SQL-92 and SQL-89 Syntax

SQL-92 Syntax

SQL-89 Syntax

SELECT t1."LAST_NAME" AS c1, t2."ORDER_ID" AS c2

FROM "CUSTOMER" t1 JOIN "CUST_ORDER" t2

ON t1."CUSTOMER_ID" = t2."CUSTOMER_ID"

SELECT t1."LAST_NAME" AS c1, t2."ORDER_ID" AS c2

FROM "CUSTOMER" t1, "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID"

3.3.7.5 Outer Join Pushdown

The XQuery engine interprets nested FLWR expressions (see Figure 3-3) as an outer join and can generate SQL for a data source when:

- both join branches belong to the same database and are addressable from a single SQL statement (both branches must come from the same JNDI datasource), and

- join condition is present and uses values from both branches, and

- join condition is pushable, and

- the underlying RDBMS supports outer join syntax using either SQL-92 or proprietary syntax in its SQL language

Figure 3-3 Outer Join Pattern

The SQL code generated by the XQuery engine depends on the SQL dialect supported by the source database (see Appendix A for details). Table 3-15 shows example SQL-92 and proprietary syntax for the query shown in Figure 3-3.

Table 3-15 SQL-92 and Proprietary Outer Join Syntax Comparison

SQL-92 Syntax

Oracle 11 Syntax

SELECT t1."LAST_NAME" AS c1, t2."ORDER_ID" AS c2

FROM "CUSTOMER" t1 OUTER JOIN "CUST_ORDER" t2

ON t1."CUSTOMER_ID" = t2."CUSTOMER_ID"

SELECT t1."LAST_NAME" AS c1, t2."ORDER_ID" AS c2

FROM "CUSTOMER" t1, "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID" (+)

Variations of the outer-join pattern are obtained from the original query by using equivalent XQuery expressions. Figure 3-4 is an example of a query equivalent to that shown in Figure 3-3 that will also result in a SQL statement with an outer join.

Figure 3-4 Outer Join Pattern

3.3.7.6 Semi-Joins and Anti-Semi-Joins

A semi-join returns data from a single branch of the join condition, when the join condition is satisfied. An anti-semi-join returns data from a single branch when the join condition is false. Although the XQuery language does not have specific constructs for semi-joins and anti-semi-joins, the XQuery engine translates several specific FLWR patterns into SQL semi-join or anti-semi-join patterns, assuming that:

- both sides (outer and inner) belong to the same database and are addressable from a single SQL statement (both branches must come from the same JNDI datasource).

- the join condition exists.

- the join condition is pushable.

- the RDBMS supports the EXISTS function and subqueries (see Appendix A for details).

The XQuery interprets a FLWR query containing an inner existential quantified expression as a semi-join, translating the expression into an SQL query with the EXISTS check in the WHERE clause.

Universal quantified expressions are also supported, but their SQL generation is slightly more complicated. The XQuery engine translates FLWRs with exist() or empty() predicates in the where clause into semi-joins. Table 3-16 shows several examples of such patterns.

Table 3-16 Various XQuery Patterns that Can Generate Semi-Join and Anti-Semi-Join SQL

Pattern

XQuery Statement

SQL Statement

FLWR with existential ("some") quantifier [semi-join]

for $customer in CUSTOMER()

where

some $c_order in CUST_ORDER()

satisfies ($customer/CUSTOMER_ID eq $c_order/ORDER_ID)

and

($c_order/STATUS eq "OPEN")

return

$customer/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE EXISTS(

SELECT 1

FROM "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID" AND t2."STATUS" = 'OPEN'

)

FLWR with negation of existential quantifier [anti-semi join]

for $customer in CUSTOMER()

where not(

some $c_order in CUST_ORDER()

satisfies ($customer/CUSTOMER_ID eq $c_order/ORDER_ID)

and

($c_order/STATUS eq "OPEN")

)

return

$customer/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE NOT EXISTS(

SELECT 1

FROM "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID" AND t2."STATUS" = 'OPEN'

)

FLWR with universal ("every") quantified expression

for $customer in CUSTOMER()

where

every $c_order in CUST_ORDER()

satisfies ($customer/CUSTOMER_ID eq $c_order/ORDER_ID) and

($c_order/STATUS eq "OPEN")

return

$customer/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE NOT EXISTS(

SELECT 1

FROM "CUST_ORDER" t2

WHERE NOT(t1."CUSTOMER_ID" = t2."CUSTOMER_ID" AND t2."STATUS" = 'OPEN')

)

FLWR with exists() predicate

or $customer in CUSTOMER()

where exists(

for $c_order in CUST_ORDER()

where ($customer/CUSTOMER_ID eq $c_order/ORDER_ID) and

($c_order/STATUS eq "OPEN")

return $c_order

)

return

$customer/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE EXISTS(

SELECT 1

FROM "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID" AND t2."STATUS" = 'OPEN'

)

FLWR with empty() predicate

for $customer in CUSTOMER()

where empty(

for $c_order in CUST_ORDER()

where ($customer/CUSTOMER_ID eq $c_order/ORDER_ID) and ($c_order/STATUS eq "OPEN")

return $c_order

)

return

$customer/CUSTOMER_ID

SELECT t1."CUSTOMER_ID" AS c1

FROM "CUSTOMER" t1

WHERE NOT(EXISTS(

SELECT 1

FROM "CUST_ORDER" t2

WHERE t1."CUSTOMER_ID" = t2."CUSTOMER_ID" AND t2."STATUS" = 'OPEN'

))

3.3.8 Grouping and Aggregation

The XQuery engine supports several patterns for group by pushdown and aggregate function pushdown.

3.3.8.1 Group By Pushdown

The Group By clause is a Oracle extension to the XQuery language (see Section 2.2.2.1, "Generalized FLWGOR (group by)" for more information). The XQuery engine implicitly adds a group by expression to some patterns to enable more efficient pushdown and query execution.

Figure 3-5 XQuery Containing a Group By

The XQuery engine translates group-by clauses into equivalent SQL GROUP BY clauses if:

- the expressions defining grouping variables are pushable

- the partition variable is used by an aggregate function only

Since the query shown in Figure 3-5 meets these requirements, the following SQL statement is generated:

SELECT t1."CATEGORY" AS c1, COUNT(*) AS c2
FROM "PRODUCT" t1
GROUP BY t1."CATEGORY"

The group-by pushdown is closely related to the Distinct-by Pushdown. When a group-by clause does not include a partition variable, the XQuery engine generates SQL that includes the DISTINCT keyword, as described in the next section.

3.3.8.2 Distinct-by Pushdown

An XQuery containing a Group By clause (without a partition definition), can be generated into SQL query that uses SQL's DISTINCT keyword to eliminate duplicates in the result. For example, the XQuery statement in Table 3-17 uses a group-by clause but has no partition defined, and the SQL statement created by Oracle Data Service Integrator refines the result by using the DISTINCT keyword.

Table 3-17 Distinct By Pushdown

XQuery Statement

SQL Statement

for $product in PRODUCT()

group by $product/CATEGORY_ID as $category

return $category

SELECT DISTINCT t1."CATEGORY_ID" AS c1

FROM "PRODUCT" t1

3.3.8.3 Simple Aggregate Pattern

An aggregate function operating on a single column from a data source is one of the simplest aggregate patterns that the XQuery engine supports, although it does so in a slightly non-intuitive way. It uses a constant as a single grouping expression (...GROUP ...BY n). The XQuery engine can pushdown the SQL if the RDBMS supports either a GROUP BY operation on a constant or supports sub-queries in the sub-clause (see Table 3-18).

Table 3-18 Aggregate Pushdown

XQuery Statement

SQL StatementFoot 1

SQL StatementFoot 2

for $product in PRODUCT()

group $product

as $price_group

by 1

return min($price_group/LIST_PRICE)

SELECT MIN(t1."LIST_PRICE") AS c1

FROM "PRODUCT" t1

GROUP BY 1

SELECT MIN(t2.c2) AS c3

FROM (

SELECT 1 AS c1, t1."LIST_PRICE" AS c2

FROM "PRODUCT" t1

) t2

GROUP BY t2.c1

Footnote 1 RDBMS supports GROUP BY constant

Footnote 2 RDBMS does not support GROUP BY, but does support sub-queries in the FROM clause

3.3.8.4 Group-By with a Nested Where Clause Translates to SQL HAVING Clause

If a relational data source supports nested WHERE clauses, the XQuery engine can translate a where clause after a group-by clause into a SQL HAVING clause (see Table 3-19), provided that the where clause meets other requirements for XQuery-SQL translation.

Table 3-19 Nested WHERE Clauses

XQuery Statement

SQL Statement

for $product in PRODUCT()

group $product/LIST_PRICE as $price_group

by $product/CATEGORY as $category

where max($price_group) gt 1000

return

<t>

{

$category,

min($price_group)

}

</t>

SELECT t1."CATEGORY" AS c1, MIN(t1."LIST_PRICE") AS c2

FROM "PRODUCT" t1

GROUP BY t1."CATEGORY"

HAVING MAX(t1."LIST_PRICE") > 1000

3.3.8.5 Outer Join with Aggregate Pattern

Another common pattern supported by the XQuery engine is outer join with aggregation of the right branch, which is expressed in XQuery as nested FLWR expressions with aggregate functions in the inner level (Table 3-20).

Table 3-20 Outer Join with Aggregate

XQuery Statement

SQL Statement

for $customer in CUSTOMER()

return

<customer>

<name>{ data($customer/LAST_NAME) }</name>

<order-amount>

{

sum(

for $c_order in CUST_ORDER()

where $customer/CUSTOMER_ID eq $c_order/CUSTOMER_ID

return $c_order/ORDER_AMOUNT

)

}

</order-amount>

</customer>

SELECT t1."LAST_NAME" AS c1, SUM(t2."ORDER_AMOUNT") AS c2

FROM "CUSTOMER" t1

LEFT OUTER JOIN "CUST_ORDER" t2

ON (t2."CUSTOMER_ID" = t1."CUSTOMER_ID")

GROUP BY t1."CUSTOMER_ID"

With this type of query, in order to fully push as much of the query as possible to the data source RDBMS, the XQuery engine evaluates the outer join first and then performs the group-by on the left branch's primary key column, to compute the aggregate. The XQuery engine can perform this optimization only if the left branch of the query has a key column. As shown in Table 3-21, the CUSTOMER does, so the optimization will be performed.

The net effect is that only the XML creation is performed in the XQuery engine.

3.3.8.6 If-Then-Else Pattern

The CASE expression, introduced in SQL:1992, provides a way to use if-then-else logic in SQL statements without having to invoke procedures. The CASE expression correlates a list of values and alternatives.

An XQuery if-then-else pattern can be translated into an SQL CASE expression if:

- the underlying data source (RDBMS) supports CASE expressions.

- the XQuery data type result is not an xs:boolean.

- the data types associated with the then and else expressions are the same (quantifiers are disregarded).

The then and else expressions can contain (or fully consist of) parameters. If the if-then-else expression does not depend on the data source, the entire expression is pushed as a parameter.

An example can be seen in Table 3-21.

Table 3-21 If-Then-Else Pushdown

XQuery Statement

SQL Statement

for $i in CUST_ORDER()

return

if ($i/STATUS eq "SHIPPED")

then data($i/STATUS)

else data($i/CUSTOMER_ID)

SELECT

CASE WHEN (t1."STATUS" = 'SHIPPED')

THEN t1."STATUS"

ELSE t1."CUSTOMER_ID" END AS c1

FROM "CUST_ORDER" t1

3.3.8.7 Subsequence Pushdown

In the typical RDBMS application, it is quite common to paginate the results — output just 20 customer records per page, for example, for printing or other purposes. XQuery meets this need with its subsequence( ) function. XQuery provides two different subsequence functions, shown in Table 3-22 and in Table 3-23.

Table 3-22 Two- and Three-Argument Variants of XQuery Subsequence Function

Two-argument variant

Three-argument variant

fn:subsequence(

$sourceSeq as item()*,

$startingLoc as xs:double

) as item()*

fn:subsequence(

$sourceSeq as item()*,

$startingLoc as xs:double,

$length as xs:double

) as item()*

Table 3-23 Subsequence Pushdown

XQuery Statement

SQL Statement (DB2)

let $s :=

for $i in t2:PRODUCT()

order by $i/LIST_PRICE descending

return $i

for $p in subsequence($s, 1, 10)

return <product>

<name>

{ data($p/PRODUCT_NAME) } </name>

<price>

{ data($p/LIST_PRICE) }

</price>

</product>

};

SELECT t3.c1, t3.c2 FROM(

SELECT ROW_NUMBER() OVER()

as c3, t2.c1, t2.c2

FROM(

SELECT t1."LIST_PRICE" as c1,

t1."PRODUCT_NAME" as c2

FROM "RTLALL"."PRODUCT" t1

ORDER BY t1."LIST_PRICE" DESC

)t2

)t3

WHERE(t3.c3 <11)

The two-argument variant returns the remaining items of an input sequence, starting from the $startingLoc. The three-argument variant returns $length items of the input sequence starting from the $startingLoc. Table 3-24 shows several different examples of the subsequence function in the context of specific queries.

Table 3-24 Examples of XQuery Expressions using Subsequence Function

Query statement

XQuery Expression

Return the 10 most expensive products only.

let $s :=

for $i in PRODUCT()

order by $i/LIST_PRICE descending

return $i

for $p in subsequence($s, 1, 10)

return <product>

<name> { data($p/PRODUCT_NAME) } </name>

<price> { data($p/LIST_PRICE) } </price>

</product>

Return all service cases opened against each of the 10 most expensive products (outer join).

let $s :=

for $i in PRODUCT()

order by $i/LIST_PRICE descending

return $i

for $p in subsequence($s, 1, 10)

return <product>

<name> { data($p/PRODUCT_NAME) } </name>

{

for $sc in SERVICE_CASE()

where $p/PRODUCT_ID eq $sc/PRODUCT_ID and

$sc/STATUS = 'Open'

return <case>{ data($sc/CASE_ID) }</case>

}

</product>

Return the total number of service cases opened against each of the 10 most expensive products (aggregation).

let $s :=

for $i in PRODUCT()

order by $i/LIST_PRICE descending

return $i

for $p in subsequence($s, 1, 10)

return

<product>

<name> { data($p/PRODUCT_NAME) } </name>

{

let $scs :=

for $sc in SERVICE_CASE()

where $p/PRODUCT_ID eq $sc/PRODUCT_ID and $sc/STATUS = 'Open'

return $sc

return <case_count>{ count($scs) }</case_count>

}

</product>

An XQuery subsequence pattern can be translated into an SQL subsequence expression if:

- the fn:subsequence( ) operates on a FLWR expression that returns items from the RDBMS

- the return expression in the inner FLWR must always return a single item (it can be a row element or column element)

- the underlying data source (RDBMS) supports subsequence

Oracle Data Service Integrator can pushdown the subsequence pattern to the underlying RDBMS, thereby enhancing performance, as long as the underlying RDBMS supports it.

- IBM DB2 9.7 and 10.1 support both variants of the subsequence function.

- Oracle 11.x and 12.x support both versions of the subsequence function, without restriction.

- Microsoft SQL Server 2008 R2 and Microsoft SQL Server 2012 support the three-argument version only, and requires that $startingLoc must be 1 (a constant) and $length must be an xs:integer constant.

- Teradata 14.x and 13.x support both versions of the subsequence function, without restriction.

Note:

Subsequence pushdown is not supported for Sybase, or any base RDBMS (see "XQuery-SQL Mapping Reference" for other core and base RDBMS information).

3.3.9 Direct SQL Data Services and Pushdown

Oracle Data Service Integrator lets you create data services not only from relational tables and views, but also from SQL queries. These direct SQL data services, as they are called, can also be composed by the XQuery engine, and pushed down as native SQL to the target RDBMS, if:

- the RDBMS supports sub-queries in the FROM clause.

- for outer join pushdown, key information must be specified in the Direct SQL data service configuration (see Appendix A).

If the RDBMS does not support sub-queries (the FROM clause), the pushdown will not occur.

For example, a user-defined SQL query, "recent_order" is configured as a relational source:

SELECT * from RECENT_ORDER

The XQuery that gets created in the data service and the resulting generated SQL that gets pushed down by the XQuery engine are shown in Table 3-25.

Table 3-25 Direct SQL Data Service Example

XQuery Statement

SQL Statement

declare variable

$external_variable as xs:string external;

for $recent_order in RECENT_ORDER()

where $recent_order/ORDER_ID eq $external_variable

return $recent_order/ORDER_AMOUNT

SELECT t1."ORDER_AMOUNT" AS c1

FROM (

SELECT * FROM RECENT_ORDER

) t1

WHERE t1."ORDER_ID" = ?

SQL pushdown on top of direct SQL is not limited to simple select-project queries. Any operation for which pushdown is supported for table and view sources is also supported for data services created for direct SQL queries. For example, Table 3-26 shows a join query and its generated result.

Table 3-26 Direct SQL Data Service with Join Condition

XQuery Statement

SQL Statement

for $customer in CUSTOMER()

for $recent_order in RECENT_ORDER()

where $customer/CUSTOMER_ID eq $recent_order/CUSTOMER_ID

return

<t>{ $customer/CUSTOMER_ID, $recent_order/ORDER_ID }</t>

SELECT t1."CUSTOMER_ID" AS c1, t2."ORDER_ID" AS c2

FROM "CUSTOMER" t1

JOIN (

SELECT * FROM RECENT_ORDER

) t2

ON t1."CUSTOMER_ID" = t2."CUSTOMER_ID"

3.3.10 Distributed Query Pushdown

Oracle Data Service Integrator uses SQL pushdown to off-load query processing to the underlying data source RDBMS whenever possible. However, as mentioned in Section 3.1.2, "How the XQuery Engine Supports SQL Data Sources," SQL pushdown is not always possible, nor beneficial. For example, when two data sources are running on two different systems, or when a query combines relational data with non-relational data, SQL pushdown may not provide any performance benefit.

In cases such as these, Oracle Data Service Integrator uses special techniques to batch-process the outside portion of a query (the left branch) and send a cluster (or chunk) of data to the right branch as parameters (see Table 3-27).

The XQuery engine chooses this optimization technique (a "clustered parameter passing join," also known as PPK) for a distributed query when:

- join pattern is recognized by the compiler, and

- the join cannot be pushed down in its entirety for any reason, and

- join condition is pushable to either branch when all expressions operating on another branch are treated as parameters in the generated SQL.

Table 3-27 Distributed Query Pushdown — a PPK Join Example

XQuery Statement

SQL Statement

for $customer in CUSTOMER()

for $order in ORDER()

where

$customer/CUSTOMER_ID eq

$recent_order/CUSTOMER_ID

return

<t>{ $customer/CUSTOMER_ID, $order/ORDER_ID }</t>

SELECT t1."CUSTOMER_ID" AS c1,

t1."ORDER_ID" as c2

from "ORDER" t1

WHERE t1."CUSTOMER_ID" = ? OR

t1."CUSTOMER_ID" = ?

...

OR

t1."CUSTOMER_ID" =?

Unless all these conditions are met, the XQuery engine cannot use this optimization technique but will instead use the single parameter join instead (PP1 join).

3.4 Preventing SQL Pushdown

Developers can exercise control over SQL pushdown by using the fn-bea:fence() function (an Oracle extension to XQuery functions and operations) to demarcate sections of XQuery code that the XQuery engine should ignore when it is evaluating query fragments for SQL pushdown.

For the example shown in Table 3-28, even though the upper-case function could be pushed down to the RDBMS, its pushdown is blocked by the fence() function and the upper-case function will be executed by the XQuery engine. Only the fragment comprising the lower-case function is included in the query plan as SQL pushdown. The result of the SQL will be returned to the XQuery engine, which will use the XQuery upper-case function on the result.

Table 3-28 Using the fn-bea:fence() Function

XQuery Statement

SQL Statement

for $c in CUSTOMER()

return

upper-case(

fn-bea:fence(

lower-case( $c/LAST_NAME )

)

)

SELECT LOWER(t1."LAST_NAME") AS c1

FROM "CUSTOMER" t1

Use the fence() function whenever you want SQL to be sent as is, to the RDBMS. For example, if you are accessing an Oracle 8.5.x RDBMS that uses hints and Oracle's rule-based optimizer, you should send the hinted SQL queries to the data source by wrapping them in the fence() function.

To circumvent SQL pushdown for specific clauses, extract those clauses into separate FLWOR expressions with the fence( ) function at the top of the clause, as shown here:

for $x in
fn-bea:fence
(
for $c in CUSTOMER()
return $c/LAST_NAME
)
order by $x
return $x

As you develop data services that use relational data sources, use the Oracle Data Service Integrator Query Plan View to see the results of using the fence( ) function (Figure 3-6). In this example, the order by clause will be executed by the XQuery engine rather than pushed down as SQL.

Figure 3-6 Example of an XQuery Plan without (l) and with (r) the fn-bea:fence() Function

Note that the red triangles displayed in the SQL portions of Figure 3-6 are alerts calling attention to the fact that a where clause is missing from the XQuery statement.

4 Understanding XML Namespaces

XML namespaces are a mechanism that ensures that there are no name conflicts (or ambiguity) when combining XML documents or referencing an XML element. Oracle Data Service Integrator fully supports XML namespaces and includes namespaces in the queries generated in Workshop for WebLogic.

This section includes the following topics:

- Section 4.1, "Introducing XML Namespaces"

- Section 4.2, "Using XML Namespaces in Oracle Data Service Integrator Queries and Schemas"

4.1 Introducing XML Namespaces

Namespaces provide a mechanism to uniquely distinguish names used in XML documents. XML namespaces appear in queries as a namespace string followed by a colon. The W3C uses specific namespace prefixes to identity W3C XQuery data types and functions. In addition, Oracle has defined the fn-bea: namespace prefix to uniquely identify Oracle-supplied functions.

Table 4-1 lists the predefined XQuery namespaces used in Oracle Data Service Integrator queries.

Table 4-1 Predefined Namespaces in XQuery

Namespace Prefix

Description

Examples

fn

The prefix for XQuery functions.

fn:data()

fn:sum()

fn:substring()

fn-bea

The prefix for Oracle Data Service Integrator-specific extensions to the standard set of XQuery functions.

fn-bea:rename()

fn-bea:is-access-allowed()

xs

The prefix for XML schema types.

xs:string

For example, the xs:integer data type uses the XML namespace xs. Actually, xs is an alias (called a prefix) for the namespace URI.

XML namespaces ensure that names do not collide when combining data from heterogeneous XML documents. As an example, consider a document related to automobile manufacturers that contains the element <tires>. A similar document related to bicycle tire manufacturers could also contain a <tires> element.

Combining these documents would be problematic under most circumstances. XML namespaces easily avoid these types of name collisions by referring to the elements as <automobile:tires> and <bicycle:tires>.

4.1.1 Exploring XML Schema Namespaces

XML schema namespaces—including the target namespace—are declared in the schema tag. The following is an example using a schema created during metadata import:

<xsd:schema targetNamespace="http://temp.openuri.org/SampleApp/CustOrder.xsd" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:bea="http://www.oracle.com/public/schemas"
elementFormDefault="unqualified" attributeFormDefault="unqualified">
...

The second line declares the target namespace using the targetNamespace attribute. It this case, the target namespace is:

http://temp.openuri.org/SampleApp/CustOrder.xsd

The third line of the schema contains the default namespace, which is the namespace of all the elements that do not have an explicit prefix in the schema.

For example, if you see the following element in a schema document:

<element name="appliance" type="string"/>

the element element belongs to the default namespace, as do unprefixed types such as string.

The fourth line of the schema binds the xsd prefix to the standard XML Schema URI. The fifth line of the schema contains a namespace declaration which associates a URI with the bea prefix. There can be any number of these declarations in a schema.

References to types declared in this schema document must be prefixed, as illustrated by the following example:

<complexType name="AddressType">
<sequence>
<element name="street_address" type="string"/>
...
</sequence>
</complexType>

<element name="address" type="bea:AddressType"/>

It is recommended that you create schemas with elementFormDefault="unqualified" and attributeFormDefault="unqualified". This enables you to move elements between namespaces by renaming a single complex element, instead of having to explicitly map every element.

4.2 Using XML Namespaces in Oracle Data Service Integrator Queries and Schemas

Oracle Data Service Integrator automatically generates the namespace declarations when generating a query. Oracle Data Service Integrator employs a simple scheme using labels ns0, ns1, ns2, and so forth. Although it is easy to change assigned namespace names, care must be taken to make sure that all uses of that particular namespace are changed.

When a Return type is created, by default it is qualified, meaning that the namespace of the outermost complex element appears in the schema.

Figure 4-1 Schema with Unqualified Attributes and Elements

If you want attributes or nested elements to appear as qualified, you need to use an editor outside Workshop for WebLogic to modify the generated schema for either or both attributeFormDefault and elementFormDefault to be set to qualified.

5 Best Practices Using XQuery

This chapter offers a series of best practices for creating data services using XQuery. The chapter introduces a data service design model, and describes a conceptual model for layering data services to maximize management, maintainability, and reusability.

This chapter includes the following topics:

- Section 5.1, "Introducing Data Service Design"

- Section 5.2, "Understanding Data Service Design Principles"

- Section 5.3, "Applying Data Service Implementation Guidelines"

5.1 Introducing Data Service Design

When designing data services, you should strive to maximize the ability to maintain, manage, and reuse queries. One approach is to adopt a layered design model that partitions services into the following levels:

- Application Services. Data services at the Application Services level are defined by client application requirements. Functions defined in this layer can additionally be used to constraint queries and to aggregate data, among other tasks.

- Logical Services. The Logical Services contain functions that perform general purpose logical operations and transformations on data accessed through Canonical and Physical Services.

- Canonical Services. Data services defined at the Canonical Services level normalize data obtained from the Physical Services level.

- Physical Services. The Physical Services are defined by the system based on introspection of physical data sources. The system creates data service functions that retrieve all rows in a table, offering the greatest flexibility for data service functions defined in higher layers. The system also defines relationships between data services, as required.

Figure 5-1 illustrates the data service design model.

Figure 5-1 Data Service Design Model

Using this design model, you can design and develop data services in the following manner:

1. Develop the Physical Services based on introspection of physical data sources.

2. Define the Application Services based on precise client application requirements.

3. Design the Canonical Services to normalize and create relationships between data accessed using the Physical Services.

4. Design the Logical Services to manipulate and transform data accessed through the Canonical and Physical Services, providing general purpose reusable services to the Application Services layer.

5. Work through the layers from the top down, determining optimal functions for each level and factoring out reusable queries.

5.2 Understanding Data Service Design Principles

This section describes best practices for designing and developing services at each layer of the data service design model. Table 5-1 describes the data service design principles.

Table 5-1 Data Service Design Principles

Level

Design Principle

Description

Application Services

Base design on client needs

Design data services and queries at the Application Services level specifically tuned to client needs, using functions defined at the Logical and Canonical Service levels.

Nest or relate information, as required by the application

Use the XML practice of nesting related information in a single XML structure. Alternatively, use navigation functions to relate associated information, as required by the application.

Introduce constraints at the highest level

Oracle Data Service Integrator propagates constraints down function levels when generating queries. By keeping constraints, such as function parameters, at the highest level, you encourage reuse of lower level functions and permit the system to efficiently optimize the final generated query.

Aggregate data at the highest level

Aggregate data in functions at the highest level possible, preferably at the Application Services level.

Logical Services

Create common functions to serve multiple applications

Design functions that provide common services required by applications. Base function design at the Logical Services level on requirements already established at the Application Services level, based on client needs.

Refactor to reduce the number of functions

Refactor the functions, as necessary, to reduce the overall number of functions to as few as possible. This reduces complexity, simplifies documentation, and eases future maintenance.

Canonical Services

Use function defined in the Physical Services level

Create (public) read functions can then all be expressed in terms of the main "get all instances" function.

Canonical Services

Create navigation functions to represent relationships

Use separate data services with relationships (implemented through navigation functions) rather than nesting data. For example, create navigation functions to relate customers and orders or customers and addresses instead of nesting this information.

This keeps data services and their queries small, making them more manageable, maintainable, and reusable.

Define keys to improve performance

Defining keys enables the system to use this information when optimizing queries.

Establish relationships between unique identifiers and primary keys

Establish relationships between unique identifiers or primary keys that refer to the same data (such as Customer ID or SSN) but vary across multiple data sources. You can use either of the following methods:

- Create navigation functions to create relationships between the data.

- Create a new table in the database to relate the unique identifiers and primary keys.

Physical Services

Employ functions that get all records

Using protected functions that get all records at the Physical Services level provides the system with the most flexibility to optimize data access based on constraints specified in higher level functions.

Do not perform data type transformations

The system is unable to generate optimizations based on constraints specified at higher levels when data type transformations are performed at the Physical Services level.

Do not aggregate

Use aggregates at the highest level possible to enable the system to optimize data access.

5.3 Applying Data Service Implementation Guidelines

Table 5-2 describes implementation guidelines to apply when designing and developing data services.

Table 5-2 Data Service Implementation Guidelines

Level

Design Principle

Description

Application Services

Use the group clause to aggregate

When performing a simple aggregate operation (such as count, min, max, and so forth) over data stored in a relational source, use a group clause as illustrated by the following:

for $x in f1:CUSTOMER()

group $x as $g by 1

return count($g)

instead of:

count( f1:CUSTOMER() )

in order to enable pushdown of the aggregation operation to the underlying relational data source.

Note that the two formulations are semantically equivalent except for the case where the sequence returned by f1:CUSTOMER() is the empty sequence. Of course performance will be better for the pushed down statement.

Application Services

Use element(foo) instead of schema-element(foo)

Define function arguments and return types in data services as element(foo) instead of schema-element(foo). Using schema-element instead of element causes Oracle Data Service Integrator to perform validation, potentially blocking certain optimizations.

Use xs:string to cast data

Use xs:string when casting data instead of fn:string(). The two approaches are not equivalent when handling empty input, and the use of xs:string enables cast operations to be executed by the database.

Be aware of Oracle treating empty strings as NULL, and how this affects XQuery semantics

The Oracle RDBMS treats empty strings as NULL, without providing a method of distinguishing between the two. This can affect the semantics of certain XQuery functions and operations.

For example, the fn:lower-case() function is pushed down to the database as LOWER, though the two have different semantics when handling an empty string, as summarized by the following:

- fn:lower-case() returns an empty string

- LOWER in Oracle returns NULL

When using Oracle, consider using the fn-bea:fence() function and performing additional computation if precise XQuery semantics are required.

Use the function fn:exists() to check for NULL in a database field

Oracle Data Service Integrator does not create elements for fields with NULL value. For example, Oracle Data Service Integrator will not create a <PHONE> element if the corresponding value is NULL in the database.

You can therefore use the function fn:exists() to check for NULL in a database field, as this function is equivalent to the SQL clause PHONE IS NULL.

Application Services

Return plural for functions that contain FLWOR expressions

When a function body contains a FLWOR expression, or references to functions that contains FLWOR, the function should return plural.

For example, consider the following XQuery expression:

For $c in CUSTOMER()

Return

<CUSTOMER>

<LAST_NAME>$c/LAST_NAME</LAST_NAME>

<FIRST_NAME>$c/FIRST_NAME

</FIRST_NAME>

<ADDRESS>{

For $a in ADDRESS()

Where $a/CUSTOMER_ID =

$c/CUSTOMER_ID

Return

$a

}</ADDRESS>

</CUSTOMER>

Defining a one-to-one relationship between a CUSTOMER and an ADDRESS, as in the following, can block optimizations.

<element name=CUSTOMER>

<element name=LAST_NAME/>

<element name=FIRST_NAME/>

<element name=ADDRESS/>

</element>

Application Services

Return plural for functions that contain FLWOR expressions (continued)

This is because Oracle Data Service Integrator determines that there can be multiple addresses for one CUSTOMER. This leads the system to insert a TypeMatch operation to ensure that there is exactly one ADDRESS. The TypeMatch operation blocks optimizations, thus producing a less efficient query plan.

The Query Plan Viewer shows TypeMatch operations in red and should be avoided. Instead, the schema definition for ADDRESS should indicate that there could be zero or more ADDRESSes.

<element name=CUSTOMER>

<element name=LAST_NAME/>

<element name=FIRST_NAME/>

<element name=ADDRESS minOccurs="0"

maxOccurs="unbounded"/>

</element>

Avoid cross product situations

Avoid cross product (Cartesian Product) situations when including conditions. For example, the following XQuery sample results in poor performance due to a cross product situation:

define fn ($p string)

for $c in CUSTOMER()

for $o in ORDER()

where $c/id eq $p

and $o/id eq $p

Instead, use the following form to specify the same query:

define fn ($p string)

for $c in CUSTOMER()

for $o in ORDER()

where $c/id eq $o/id

and $c/id eq $p

6 Oracle XQuery Scripting Extension (XQSE)

This chapter describes the Oracle XQuery Scripting Extension (XQSE) that enables you to add procedural constructs to XQuery-based data services. The chapter describes the language extensions and includes the statement grammar along with one or more examples.

This chapter includes the following topics:

- Section 6.1, "Introducing the XQuery Scripting Extension"

- Section 6.2, "Prolog and Query Body"

- Section 6.3, "Procedure Declaration"

- Section 6.4, "XQSE Function Declaration"

- Section 6.5, "Value Statement and Procedure Call"

- Section 6.6, "Block"

- Section 6.7, "Set Statement"

- Section 6.8, "While Statement"

- Section 6.9, "Return Statement"

- Section 6.10, "Iterate Statement"

- Section 6.11, "Try Statement"

- Section 6.12, "If Statement"

- Section 6.13, "Changed Element"

- Section 6.14, "XQSE Grammar Summary"

6.1 Introducing the XQuery Scripting Extension

Oracle Data Service Integrator data services are based on the XQuery language, which enables you to use the structure of XML to express queries against data. The XQuery Scripting Extension builds on this base by adding procedural constructs, including basic statements, control flow, and user-defined procedures to XQuery.

XQSE is therefore a superset of XQuery, extending it with additional features that enable you to create richer and more complex data services while working within the context of XML and XQuery. You can think of XQSE as an extension to XQuery in the same way as Oracle PL/SQL is an extension of SQL.

6.2 Prolog and Query Body

XQSE extends XQuery by adding procedural capabilities to the declarative query capabilities of XQuery. In XQuery, a query consists of a prolog followed by an XQuery expression. The prolog of a query sets up the environment for the expression by defining namespaces, external variables, and functions, among other information.

In XQSE, a prolog can also contain definitions of procedures and XQSE functions which are, respectively, side-effecting and non-side-effecting callable units of execution written in XQSE.

The following shows the grammar of the XQSE prolog and query body:

Prolog ::= ((DefaultNamespaceDecl | Setter | NamespaceDecl | Import)
Separator)* ((VarDecl | FunctionDecl | ProcedureDecl |
XQSEFunctionDecl | OptionDecl) Separator)*
QueryBody ::= Expr | Block

In XQSE, the body of a top-level query can be either an XQuery expression or an XQSE block. A block is a sequence of statements that are executed sequentially.

6.3 Procedure Declaration

XQSE enables you to declare a procedure in the prolog of an Oracle Data Service Integrator data service. An XQSE procedure is similar to an XQuery function, but unlike a function, a procedure can have one or more side effects. Another difference is that XQuery functions are declarative; the body of an XQuery function is an XQuery expression.

The body of an XQSE procedure, in contrast, consists of a block, which is a list of statements executed in sequential order when the procedure is called. Alternatively, as in XQuery, you can declare a procedure as external, in which case it does not have a body and is implemented by Oracle Data Service Integrator by importing procedures from external data sources such as relational stored procedures or Web services.

The following shows the grammar of the XQSE procedure declaration:

ProcedureDecl ::= "declare" "procedure" QName "(" ParamList? ")"
("as" SequenceType)? (Block | ("external") )

A procedure may, but is not required to, return a value. Individual XQSE statements do not have return values by themselves, so a procedure returns a value only when an explicit return statement is included in the body of procedure. If the return type of a procedure is not specified, its return value is of type item()* by default.

Note:

Since returning a value is optional, a return statement is not required in the body of a procedure. In the absence of a return statement, the return value of a procedure is the empty sequence and its return type is empty().

You can use recursion in procedures. This is another difference between XQuery functions and XQSE procedures in Oracle Data Service Integrator.

Example:

(: Procedure to delete an employee given just their employee ID :)
(: Calls the default delete function on the data service after retrieving the employee object using the ID :)
declare procedure tns:deleteByEmployeeID($id as xs:string?) as empty() {
declare $emp as element(empl:Employee)? := tns:getByEmployeeID($id);
tns:delete($emp);
};

6.4 XQSE Function Declaration

XQSE extends the XQuery function declaration syntax to enable you to declare XQSE-based functions in addition to procedures. An XQSE function is essentially a read-only procedure written in XQSE with no side effects.

As with a procedure, the body of an XQSE function consists of a block, which is a list of statements. The following shows the grammar of the XQSE function declaration:

XQSEFunctionDecl ::= "declare" xqse "function" QName "(" ParamList? ")"
("as" SequenceType)? (Block | ("external") )

Since an XQSE function does not have any side-effects, you can call it from within an XQuery expression anywhere that a normal XQuery function can be called.

Since XQuery functions are declarative and therefore amenable to compile-time query optimization, you should write data service functions using XQuery instead of XQSE where possible. However, it is sometimes necessary (or at least conceptually more convenient) to express certain read-only computations procedurally. XQSE functions are appropriate in these cases.

For example, you could use an XQSE function to perform calculations that would otherwise require the use of tail recursion in XQuery. This is necessary since Oracle Data Service Integrator does not permit the use of recursion in XQuery functions.

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?) as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
};
return value ($mgrCnt);
};

6.5 Value Statement and Procedure Call

XQSE offers the value statement and procedure call statement to distinguish between function and procedure calls.

Note:

You can call a function wherever an expression can be used, but procedures can only be called in certain parts of XQSE because they include side effects.

The following shows the grammar of the XQSE value statement and procedure call statement:

ValueStatement := ExprSingle | ProcedureCall
ProcedureCall ::= FunctionCall
Statement := SimpleStatement | BlockStatement
SimpleStatement ::= SetStatement | IfStatement | ReturnStatement |
ProcedureCall

6.6 Block

An XQSE block contains a list of statements. You can use a block to declare mutable variables (using declare clauses) and manipulate those variables in subsequent statements, which are executed in sequential order.

The following shows the grammar of the XQSE block statement:

Block ::= {" ((BlockDecl ";")* StatementSequence "}"
BlockDecl ::= "declare" "$" VarName TypeDeclaration?
(":=" ValueStatement)?
StatementSequence := ((SimpleStatement ";") | (BlockStatement (";")?))*
BlockStatement := WhileStatement | IterateStatement | TryStatement |
Block

While XQuery expressions have values, statements do not (with the exception of the return statement, described in Section 6.9, "Return Statement"). Therefore, a block does not have a return value since a block is itself a compound statement.

Every variable in a block must be declared before it can be used. If you declare a variable without explicitly specifying a type, the variable will have a default type of item()*.

You also need to initialize declared variables using a set statement or as part of the BlockDecl before they can be used. Referencing uninitialized variables (not appearing on the left hand side of an assignment statement) results in an error.

Note:

Variables in a block are mutable. Unlike let and for variables that appear in XQuery expressions, which are immutable bindings of names to values, variables in an XQSE block are assignable (similar to variables in Java and C++, among other languages).

You can define nested blocks, in which case regular scoping rules apply. For example, a variable with a specific fully-qualified name declared in an inner block will shadow (redefine and hide) variables in a containing outer block that has the identical fully-qualified name.

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?)
as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
}
return value ($mgrCnt);
};

6.7 Set Statement

The XQSE set statement sets the variable VarName to the value specified by ValueStatement. The following shows the grammar of the XQSE set statement:

SetStatement ::= "set" "$" VarName ":=" ValueStatement

Before using the set statement, you must first declare the variable VarName using a declare statement. Only variables declared in this way are mutable and can therefore be changed using the set statement.

Note:

The set statement has copy semantics.

Consider the following instance:

set $z := ($x, $y)

If $x and $y are mutable variables and $x and $y are later changed, $z retains the originally copied values of $x and $y.

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?)
as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
};
return value ($mgrCnt);
};

6.8 While Statement

The XQSE while statement loops and performs the actions in the block while the effective boolean value of the condition evaluates to true. The following shows the grammar of the XQSE while statement:

WhileStatement ::= "while" "(" Expr ")" Block

The while statement reevaluates the condition expression before each loop. Typically, the condition depends upon a mutable variable that is manipulated in the block. The loop therefore terminates when code in the block causes the effective boolean value of the condition to cease being true.

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?)
as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
};
return value ($mgrCnt);
};

6.9 Return Statement

The XQSE return statement computes the expression represented by ValueStatement and returns the resulting value while exiting from the current procedure.

The following shows the grammar of the XQSE return statement:

ReturnStatement ::= "return" "value" ValueStatement

In the special case where a block containing a return statement is the body of the main query, the return statement returns the value to the invoking environment.

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?)
as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
};
return value ($mgrCnt);
};

6.10 Iterate Statement

XQSE offers an iterate statement that is equivalent to the XQuery for clause and enables you to perform data-driven looping over a block of XQSE statements. This enables you to iterate through the result of an XQuery expression, for example.

The following shows the grammar of the XQSE iterate statement:

IterateStatement ::= "iterate" "$" VarName PositionalVar? "over"
ValueStatement Block

The iteration variable VarName is bound to each item in the sequence produced by evaluating ValueStatement, which can be either an XQuery expression or a procedure call. Optionally, the PositionalVar variable represents the index of the current item in the sequence.

Note:

The VarName and PositionalVar variables are both mutable, though it is not advisable that you exploit this capability.

Examples:

(: Procedure to allow only updates that don't violate the salary change business rules :)
declare procedure tns:updateChecked($changedEmps as
changed-element(empl:Employee)*) {
iterate $sourceEmp over $changedEmps {
if (tns:invalidSalaryChange($sourceEmp)) then
fn:error(xs:QName("INVALID_SALARY_CHANGE"), ":
Salary change exceeds the limit.");
};
tns:update($changedEmps);
};

(: Procedure to perform "lite ETL", copying and transforming data from one source to another :)
declare procedure tns:copyAllToEMP2() as xs:integer {
declare $backupCnt as xs:integer := 0;
declare $emp2 as element(emp2:EMP2)?;
iterate $emp1 over ens1:getAll() {
set $emp2 := tns:transformToEMP2($emp1);
emp2:createEMP2($emp2);
set $backupCnt := $backupCnt + 1;
}
return value ($backupCnt);
};

6.11 Try Statement

The try statement enables you to perform procedural error handling in XQSE, such as those raised by the XQuery fn:error function. The try statement works in much the same way as traditional try/catch statements in languages such as Java or C++.

The following shows the grammar of the XQSE try statement:

TryStatement ::= "try" Block CatchClauseStatement+
CatchClauseStatement ::= catch "(" NameTest ("into" VarNameExpr (("," VarNameExpr)? "," VarNameExpr)?)? ")" Block

XQSE enables you to catch all XQuery errors. XQuery errors have an associated QName, enabling you to use the XQuery NameTest to restrict the errors handled by a specific catch clause. In addition, the following variables in the catch clause work similarly to the arguments of the XQuery fn:error function:

- $error—xs:QName

- $description—xs:string

- $error-object—item()*

Similar to exceptions in other languages, you can re-throw errors in XQSE using the fn:error function. When doing so, you need to ensure that all components of the error, including the name, description, and error-object, are properly passed to the new fn:error call.

Example:

(: Procedure to create a replicated employee and return an appropriately specific error message if it fails :)
declare procedure tns:create($newEmps as element(empl:Employee)*)
as element(empl:ReplicatedEmployee_KEY)* {
iterate $newEmp over $newEmps {
declare $newEmp2 as element(emp2:EMP2)? :=
bns:transformToEMP2($newEmp);
try { tns:createEmployee($newEmp); }
catch (* into $err, $msg) {
fn:error(xs:QName("PRIMARY_CREATE_FAILURE"),
fn:concat("Create failed on primary copy due to: ", $err, $msg));
};
try { emp2:createEMP2($newEmp2); }
catch (* into $err, $msg) {
fn:error(xs:QName("SECONDARY_CREATE_FAILURE"),
fn:concat("Create failed on backup copy due to: ", $err, $msg));
};
}
};

6.12 If Statement

XQSE offers an if statement that is equivalent to the XQuery IfExpr expression. The XQSE if statement differs from the XQuery IfExpr expression in the following ways:

- The XQSE if statement is a control flow statement, and does not return a value.

- The else clause is optional in an XQSE if statement.

The following shows the grammar of the XQSE if statement:

IfStatement ::= "if" "(" Expr ")" "then" Statement ("else" Statement)?

Example:

(: Procedure to compute the level of an employee in the org tree :)
declare xqse function tns:distanceFromTop($id as xs:string?)
as xs:integer? {
declare $mgrCnt as xs:integer := 0;
declare $curEmp as element(empl:Employee)? :=
tns:getByEmployeeID($id);
declare $mgrId as xs:string? := fn:data($curEmp/ManagerID);
if (fn:empty($curEmp)) then return value ();
while (fn:not(fn:empty($mgrId))) {
set $mgrCnt := $mgrCnt + 1;
set $curEmp := tns:getByEmployeeID($mgrId);
set $mgrId  := fn:data($curEmp/ManagerID);
};
return value ($mgrCnt);
};

6.13 Changed Element

The XQSE language extends the XQuery data model with information about elements that have been updated and resubmitted to Oracle Data Service Integrator. This enables XQSE to support SDO client updates along with associated server-side update logic.

An XML node that contains changes has the XQSE type changed-element. The following shows the grammar of the XQSE changed-element type:

ItemType := AtomicType | KindTestType | <"item" "(" ")"> | ChangedElementType
ChangedElementType := "changed-element" "(" ElementNameOrWildcard ")"

XQSE provides two built-in functions, fn-bea:old-value and fn-bea:current-value, to access the pre-update and post-update contents of the changed XML node respectively.

Note:

You can only pass instances of changed-element into XQSE as arguments to procedures and functions. Instances of changed-element cannot be created or incrementally modified within XQSE.

You can also use instances of changed-element in variable declarations and assignments.

Example:

(: function to determine whether or not a given salary change is legal according to business rules :)
declare function tns:invalidSalaryChange($emp as
changed-element(empl:Employee)) as xs:boolean {
let $newSalary := fn:data(fn-bea:current-value($emp)/Salary)
let $oldSalary := fn:data(fn-bea:old-value($emp)/Salary)
return (100.0 * fn:abs($newSalary - $oldSalary) div $oldSalary) gt 10.0
};

6.14 XQSE Grammar Summary

The following summarizes the XQSE grammar:

Prolog ::= ((DefaultNamespaceDecl | Setter | NamespaceDecl | Import) Separator)*
((VarDecl | FunctionDecl | ProcedureDecl | XQSEFunctionDecl| OptionDecl) Separator)*
XQSEFunctionDecl ::= "declare" xqse "function" QName "(" ParamList? ")" ("as" SequenceType)? (Block | ("external") )
ProcedureDecl ::= "declare" "procedure" QName "(" ParamList? ")" ("as" SequenceType)? (Block | ("external") )
QueryBody ::= Expr | Block
Statement := SimpleStatement | BlockStatement
SimpleStatement ::= SetStatement | IfStatement | ReturnStatement | ProcedureCall
BlockStatement := WhileStatement | IterateStatement | TryStatement | Block
ValueStatement := ExprSingle | ProcedureCall
ReturnStatement ::= "return" "value" ValueStatement
Block ::= {" ((BlockDecl ";")* StatementSequence "}"
StatementSequence := ((SimpleStatement ";") | (BlockStatement (";")?))*
BlockDecl ::= "declare" "$" VarName TypeDeclaration? (":=" ValueStatement)?
SetStatement ::= "set" "$" VarName ":=" ValueStatement
WhileStatement ::= "while" "(" Expr ")" Block
IterateStatement ::= "iterate" "$" VarName PositionalVar? "over" ValueStatement Block
ProcedureCall ::= FunctionCall
TryStatement ::= "try" Block CatchClauseStatement+
CatchClauseStatement ::= catch "(" NameTest ("into" VarNameExpr (("," VarNameExpr)? "," VarNameExpr)?)? ")" Block
IfStatement ::= "if" "(" Expr ")" "then" Statement ("else" Statement)?
ItemType := AtomicType | KindTestType | <"item" "(" ")"> | ChangedElementType
ChangedElementType := "changed-element" "(" ElementNameOrWildcard ")"

A XQuery-SQL Mapping Reference

This appendix provides the details of Oracle Data Service Integrator core support and base support for relational data, and includes these topics:

- Section A.1, "IBM DB2 9.7, 10.1"

- Section A.2, "Microsoft SQL Server 2008 R2, 2012"

- Section A.3, "Oracle 11.x, 12.x"

- Section A.4, "Sybase Adaptive Server Enterprise 15.7"

- Section A.5, "Teradata 13.x, 14.x"

- Section A.6, "Derby 10.6 (and higher)"

- Section A.7, "Base (Generic) RDBMS Support"

Each section that follows includes information about:

- Database Capabilities Information

- Native RDBMS Data Type Support and XQuery Mappings

- Function and Operator Pushdown

- Cast Operation Pushdown

- Other SQL Generation Capabilities (including join pushdown support and SQL syntax for joins)

A.1 IBM DB2 9.7, 10.1

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for IBM DB2.

A.1.1 Data Type Mapping

Table A-1 lists supported data type mappings for IBM DB2.

Table A-1 IBM DB2 Data Type Mappings

DB2 Data Type

XQuery Type

BIGINT

xs:long

BLOB

xs:hexBinary

CHAR

xs:string

CHAR() FOR BIT DATA

xs:hexBinary

CLOBFoot 1

xs:string

DATE

xs:date

DOUBLE

xs:double

DECIMAL(p,s)Foot 2  (NUMERIC)

xs:decimal (if s > 0), xs:integer (if s = 0)

INTEGER

xs:int

LONG VARCHAR

xs:string

LONG VARCHAR FOR BIT DATA

xs:hexBinary

REAL

xs:float

SMALLINT

xs:short

TIMEFoot 3

xs:timeFoot 4

TIMESTAMPFoot 5

xs:dateTime

VARCHAR

xs:string

VARCHAR() FOR BIT DATA

xs:hexBinary

Footnote 1 Pushed down in project list only.

Footnote 2 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Footnote 3 Accurate to 1 second.

Footnote 4 Values converted to local time zone (timezone information removed) due to TIME and TIMESTAMP limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

Footnote 5 Precision limited to milliseconds.

A.1.2 Function and Operator Pushdown

Table A-2 lists functions and operators that are pushed down to IBM DB2/NT8 RDBMSs. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table A-2 IBM DB2 Functions and Operators

Group

Functions and operators

Logical operators

and, or, not

Numeric arithmetic

+, -, *, div, idivFoot 1

modFoot 2

Numeric comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

Numeric functions

abs, ceiling, floor, round

String comparisonsFoot 3

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concat, upper-case, lower-case, substring(2,3)Footref 5, fn-bea:sql-like(2,3) fn-bea:trimFoot 6 , fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time

Datetime functions

year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists, subsequenceFoot 7

Footnote 1 All numeric types.

Footnote 2 xs:integer (and subtypes) only.

Footnote 3 Arguments must have SQL data type CHAR or VARCHAR.

Footnote 4 If second and third arguments are types xs:double or xs:float, they cannot be parameters.

Footnote 5 Second argument must be a constant or a parameter.

Footnote 6 Argument must be SQL data type CHAR or VARCHAR.

Footnote 7 Both two- and three-argument variants supported.

A.1.3 Cast Operation Pushdown

Table A-3 lists supported cast operations.

Table A-3 IBM DB2 Cast Operations

Source XQuery Type

Target XQuery Type

numeric

xs:double

numeric

xs:float

numeric

xs:int

numeric

xs:integer

numeric

xs:short

xs:decimal (and subtypes)

xs:string

xs:integer (and subtypes)

xs:decimal

xs:string

xs:double

xs:string

xs:float

xs:string

xs:int

xs:string

xs:integer

xs:string

xs:short

xs:dateTime

xs:time

A.1.4 Other SQL Generation Capabilities

Table A-4 lists common query patterns that can be pushed down. See also Section 3.3.7, "Common Query Patterns."

Table A-4 IBM DB2 Other SQL Generation Capabilities

Feature

Description

If-then-else

yes

Inner joins

yes, SQL-92 syntax

Outer joins

yes, SQL-92 syntax

Semi joins, Anti semi joins

yes

Order by

yes

Order by: Empty (NULL) order supported

Fixed (always sorts NULLs high). Order-bys with "empty least" modifier (the XQuery default) are not pushed down.

Order by: Aggregate function in ordering expression

yes

Group by

yes

Distinct pattern

yes

Trivial aggregate pattern

yes (using GROUP BY constant)

Direct SQL composition

yes

A.2 Microsoft SQL Server 2008 R2, 2012

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Microsoft SQL Server 2008 R2, or Microsoft SQL Server 2012.

A.2.1 Data Type Mapping

Table A-5 lists supported data type mappings for Microsoft SQL Server.

Table A-5 SQL Server Data Type Mapping

SQL Data Type

XQuery Type

BIGINT

xs:long

BINARY

xs:hexBinary

BIT

xs:boolean

CHAR

xs:string

DATETIMEFoot 1

xs:dateTimeFoot 2

DECIMAL(p,s)Foot 3  (NUMERIC)

xs:decimal (if s > 0), xs:integer (if s = 0)

FLOAT

xs:double

IMAGE

xs:hexBinary

INTEGER

xs:int

MONEY

xs:decimal

NCHAR

xs:string

NTEXTFoot 4

xs:string

NVARCHAR

xs:string

REAL

xs:float

SMALLDATETIMEFoot 5

xs:dateTime

SMALLINT

xs:short

SMALLMONEY

xs:decimal

SQL_VARIANT

xs:string

TEXT

xs:string

TIMESTAMP

xs:hexBinary

TINYINT

xs:short

VARBINARY

xs:hexBinary

VARCHAR

xs:string

UNIQUIDENTIFIER

xs:string

Footnote 1 Fractional-second-precision up to 3 digits (milliseconds). No timezone.

Footnote 2 Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Footnote 4 Pushed down in project list only.

Footnote 5 Accuracy of 1 minute.

Additionally, the following XQuery data types can be passed as parameters or returned by pushed functions:

- xs:date (see Table A-6 for functions and operators that use xs:date). When xs:date is sent to the database, it is converted to local time zone. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

- xdt:dayTimeDuration (see "Datetime Arithmetic" functions in Table A-6 for details).

- xdt:yearMonthDuration (see "Datetime Arithmetic" functions in Table A-6 for details).

A.2.2 Function and Operator Pushdown

Table A-6 lists functions and operators that are pushed down to Microsoft SQL Server. (See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.)

Table A-6 SQL Server Function and Operator Pushdown

Group

Functions and Operators

Logical operators

and, or, not

Numeric arithmetic

+, -, *, div, idivFoot 1

modFoot 2

Numeric comparisonsFootref 1

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

Numeric functions

abs, ceiling, floor, round

String comparisonsFoot 3

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concat, upper-case, lower-case, substring(2,3)Footref 4, fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration

Datetime functions

year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime

Datetime arithmetic

op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists, subsequenceFoot 6

Footnote 1 For all numeric types

Footnote 2 For xs:integer and its subtypes only.

Footnote 3 Arguments must be of SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.

Footnote 4 Both the 2-argument and 3-argument versions of function supported.

Footnote 5 Second argument must be SQL data type CHAR, NCHAR, VARCHAR, or NVARCHAR.

Footnote 6 Only the three-argument variant of fn:subsequence is supported, with the additionl requirement that the $startingLoc must be 1 (constant) and $length must be xs:integer type.

A.2.3 Cast Operation Pushdown

Table A-7 lists supported cast operations.

Table A-7 SQL Server Cast Operations

Source XQuery Data Type

Target XQuery Data Type

numeric

xs:string

numeric

xs:double

numeric

xs:float

numeric

xs:integer

numeric

xs:long

numeric

xs:int

numeric

xs:short

xs:integer (and subtypes)

xs:decimal

xs:string

xs:doubleFoot 1

xs:string

xs:float

xs:string

xs:integer

xs:string

xs:long

xs:string

xs:int

xs:string

xs:short

xs:dateTime

xs:date

xs:dateTime

xs:string

Footnote 1 Source SQL type must be CHAR, NCHAR, VARCHAR, or NVARCHAR.

A.2.4 Other SQL Generation Capabilities

Table A-8 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)

Table A-8 SQL Server Other SQL Generation Capabilities

Feature

Description

If-then-else

yes

Inner joins

yes, SQL-92 syntax

Outer joins

yes, SQL-92 syntax

Semi joins, Anti semi joins

yes

Order by

yes

Order by: Empty order (NULL order)

fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.

Order by: Aggregate function in ordering expression

yes

Group by

yes

Distinct pattern

yes

Trivial aggregate pattern

yes (using subquery)

Direct SQL composition

yes

A.3 Oracle 11.x, 12.x

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Oracle 11.x, and Oracle 12.x. Note that Oracle treats empty strings as NULLs, which deviates from XQuery semantics and may lead to unexpected results for expressions that are pushed down.

A.3.1 Data Type Mapping

Table A-9 lists supported data type mappings for Oracle 11.x and 12.x.

Table A-9 Oracle Data Type Mapping

Oracle 9 Data Type

XQuery Type

BFILE

not supported

BLOB

xs:hexBinary

CHAR

xs:string

CLOBFoot 1

xs:string

DATE

xs:dateTimeFoot 2

FLOAT

xs:double

INTERVAL DAY TO SECOND

xdt:dayTimeDuration

INTERVAL YEAR TO MONTH

xdt:yearMonthDuration

LONG

xs:string

LONG RAW

xs:hexBinary

NCHAR

xs:string

NCLOB

xs:string

NUMBER

xs:double

NUMBER(p,s)

xs:decimal (if s > 0), xs:integer (if s <=0)

NVARCHAR2

xs:string

RAW

xs:hexBinary

ROWID

xs:string

TIMESTAMP

xs:dateTimeFoot 3

TIMESTAMP WITH LOCAL TIMEZONE

xs:dateTime

TIMESTAMP WITH TIMEZONE

xs:dateTime

VARCHAR2

xs:string

UROWID

xs:string

Footnote 1 Pushed down in project list only.

Footnote 2 When SDO stores xs:dateTime value in Oracle DATE type, it is converted to local time zone and fractional seconds are truncated due to DATE limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

Footnote 3 XQuery engine maps XQuery xs:dateTime to either TIMESTAMP or TIMESTAMP WITH TIMEZONE data type, depending on presence of timezone information. Storing xs:dateTime using SDO may result in loss of precision for fractional seconds, depending on the SQL type definition.

Additionally, these XQuery data types can be passed as parameters or returned by pushed functions:

- xs:date (see Table A-10 for functions and operators that use xs:date)

- xs:integer subtypes (see "Numeric ..." functions and operators in Table A-10 for details)

A.3.2 Function and Operator Pushdown

Table A-10 lists functions and operators that are pushed down to Oracle 11.x and 12.x. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table A-10 Oracle Function and Operator Pushdown

Group

Functions and Operators

Logical operators

and, or, not

Numeric arithmeticFoot 1

+, -, *, div, idiv, mod

Numeric comparisonsFootref 1

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

Numeric functions

abs, ceiling, floor, round

String comparisonsFoot 2

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concat, upper-caseFootref 5, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xdt:yearMonthDuration, xdt:dayTimeDuration

Datetime functions

year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-duration, minutes-from-dateTime, minutes-from-duration, seconds-from-dateTime, seconds-from-duration, fn-bea:date-from-dateTime

Datetime arithmetic

op:add-yearMonthDurations, op:add-dayTimeDurations, op:subtract-yearMonthDurations, op:subtract-dayTimeDurations, op:multiply-yearMonthDuration, op:multiply-dayTimeDuration, op:divide-yearMonthDuration, op:divide-dayTimeDuration, subtract-dateTimes-yielding-yearMonthDuration, subtract-dateTimes-yielding-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-dayTimeDuration-to-dateTime, op:subtract-yearMonthDuration-from-dateTime, op:subtract-dayTimeDuration-from-dateTime, subtract-dates-yielding-yearMonthDuration, subtract-dates-yielding-dayTimeDuration, op:add-yearMonthDuration-to-date, op:add-dayTimeDuration-to-date, op:subtract-yearMonthDuration-from-date, op:subtract-dayTimeDuration-from-date

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists, subsequenceFoot 6

Footnote 1 For all numeric types

Footnote 2 Arguments must be of SQL type (N)CHAR or (N)VARCHAR2

Footnote 3 Empty input (NULL) handling deviates from XQuery semantics—returns empty sequence (instead of empty string).

Footnote 4 Argument must be CHAR, CLOB, NCHAR, NVARCHAR2, or VARCHAR2 data type.

Footnote 5 Second argument must be CHAR, NCHAR, NVARCHAR2, or VARCHAR2 data type.

Footnote 6 Both two- and three-argument variants of fn:subsequence() are supported without restriction.

A.3.3 Cast Operation Pushdown

Table A-11 lists cast operations that can be pushed down.

Table A-11 Oracle Cast Operation

Source XQuery Type

Target XQuery Type

numeric

xs:string

numeric

xs:decimal

numeric

xs:integer

numeric

xs:float

numeric

xs:double

xs:string

xs:decimalFoot 1

xs:string

xs:integer

xs:string

xs:float

xs:string

xs:double

xs:dateTime

xs:date

xs:date

xs:dateTimeFoot 2

Footnote 1 Source SQL type must be CHAR, NCHAR, VARCHAR2, or NVARCHAR2.

Footnote 2 Source SQL type must be DATE or TIMESTAMP to achieve this mapping.

A.3.4 Other SQL Generation Capabilities

Table A-12 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)

Table A-12 Oracle Other SQL Generation Capabilities

Feature

Description

If-then-else

yes

Inner joins

yes, SQL-92 syntax

Outer joins

yes, SQL-92 syntax

Semi joins, Anti semi joins

yes

Order by

yes

Order by: Empty order (NULL order)

dynamic, no restriction on order by pushdown

Order by: Aggregate function in ordering expression

yes

Group by

yes

Distinct pattern

yes

Trivial aggregate pattern pushdown

yes (using GROUP BY constant)

Direct SQL composition

yes

A.4 Sybase Adaptive Server Enterprise 15.7

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Sybase Adaptive Server Enterprise 15.7.

As you read through the tables in this section, be aware that Sybase deviates from XQuery semantics (which ignores empty strings) and treats empty strings as a single-space string.

A.4.1 Data Type Mapping

Table A-13 lists supported data type mappings for Sybase Adaptive Server Enterprise.

Table A-13 Sybase ASE Data Type Mapping

Sybase Data Type

XQuery Type

BINARY

xs:hexBinary

BIT

xs:boolean

CHAR

xs:string

DATE

xs:date

DATETIMEFoot 1

xs:dateTimeFoot 2

DECIMAL(p,s)Foot 3  (NUMERIC)

xs:decimal (if s > 0), xs:integer (if s == 0)

DOUBLE PRECISION

xs:double

FLOAT

xs:double

IMAGE

xs:hexBinary

INT (INTEGER)

xs:int

MONEY

xs:decimal

NCHAR

xs:string

NVARCHAR

xs:string

REAL

xs:float

SMALLDATETIMEFoot 4

xs:dateTime

SMALLINT

xs:short

SMALLMONEY

xs:decimal

SYSNAME

xs:string

TEXTFoot 5

xs:string

TIME

xs:time

TINYINT

xs:short

VARBINARY

xs:hexBinary

VARCHAR

xs:string

Footnote 1 Supports fractional seconds up to 3 digits (milliseconds) precision; no timezone information.

Footnote 2 Values converted to local time zone (timezone information removed) and fractional seconds truncated to milliseconds due to DATETIME limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Footnote 4 Accurate to 1 minute.

Footnote 5 Expressions returning text are pushed down in the project list only.

Additionally, the following data types can be passed as parameters or returned by pushed functions:

- xdt:dayTimeDuration

- xdt:yearMonthDuration

A.4.2 Function and Operator Pushdown

Table A-14 lists functions and operators that are pushed down to base RDBMSs. (See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like( ) function.)

Table A-14 Sybase ASE Function and Operator Pushdown

Group

Functions and operators

Logical operators

and, or, not

Numeric arithmetic

+, -, *, div Foot 1

idivFoot 2

modFoot 3

Numeric comparisonsFootref 1

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

Numeric functions

abs, ceiling, floor, round

String comparisonsFoot 4

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concatFoot 5 , upper-case, lower-case, substring(2,3), string-length, containsFoot 6 , starts-with, ends-with, fn-bea:sql-like(2,3), fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time, xdt:yearMonthDuration, xdt:dayTimeDuration

Datetime functions

year-from-dateTime, year-from-date, years-from-duration, month-from-dateTime, month-from-date, months-from-duration, day-from-dateTime, day-from-date, days-from-duration, hours-from-dateTime, hours-from-time, hours-from-duration, minutes-from-dateTime, minutes-from-time, minutes-from-duration, seconds-from-dateTime, seconds-from-time, seconds-from-duration, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime

Datetime arithmetic

op:add-yearMonthDurations, op:subtract-yearMonthDurations, op:multiply-yearMonthDuration, op:divide-yearMonthDuration, op:add-dayTimeDurations, op:subtract-dayTimeDurations, op:multiply-dayTimeDuration, op:divide-dayTimeDuration, op:add-yearMonthDuration-to-dateTime, op:add-yearMonthDuration-to-date, op:subtract-yearMonthDuration-from-dateTime, op:subtract-yearMonthDuration-from-date, op:add-dayTimeDuration-to-dateTime, op:add-dayTimeDuration-to-date, op:subtract-dayTimeDuration-from-dateTime, op:subtract-dayTimeDuration-from-date, fn:subtract-dateTimes-yielding-yearMonthDuration, fn:subtract-dates-yielding-yearMonthDuration, fn:subtract-dateTimes-yielding-dayTimeDuration, fn:subtract-dates-yielding-dayTimeDuration

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists

Footnote 1 All numeric types (+, -, *, div operators are pushed down for all numeric types).

Footnote 2 xs:decimal (and subtypes) only

Footnote 3 xs:integer (and subtypes) only

Footnote 4 Arguments must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.

Footnote 5 Each argument must be SQL data type CHAR, NCHAR, NVARCHAR, or VARCHAR.

Footnote 6 Second argument must be constant or SQL parameter.

A.4.3 Cast Operation Pushdown

Table A-15 lists supported cast operations.

Table A-15 Sybase ASE Cast Operation Pushdown

Source XQuery Type

Target XQuery Type

numeric

xs:double

numeric

xs:float

numeric

xs:int

numeric

xs:short

numeric

xs:string

xs:decimal (and subtypes)

xs:integer

xs:integer (and subtypes)

xs:decimal

xs:string

xs:doubleFoot 1

xs:string

xs:float

xs:string

xs:int

xs:string

xs:integer

xs:string

xs:short

xs:dateTime

xs:date

xs:dateTime

xs:time

Footnote 1 Source SQL type must be (N)CHAR or (N)VARCHAR

A.4.4 Other SQL Generation Capabilities

Table A-16 lists common query patterns that can be pushed down. See Section 3.3.7, "Common Query Patterns" for details.

Table A-16 Sybase Other SQL Generation Capabilities

Feature

Description

If-then-else

yes

Inner joins

yes, SQL-92 syntax

Outer joins

yes, SQL-92 syntax

Semi joins, Anti semi joins

yes

Order by

yes

Order by: Empty order (NULL order)

fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.

Order by: Aggregate function in ordering expression

yes

Group by

yes

Distinct pattern

yes

Trivial aggregate pattern

yes (using subquery)

Direct SQL composition

yes

A.5 Teradata 13.x, 14.x

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Teradata 13.x, 14.x.

A.5.1 Data Type Mapping

Table A-17 lists supported data type mappings for Teradata.

Table A-17 Teradata Data Type Mapping

Teradata Data Type

XQuery Type

BYTE

xs:hexBinary

BYTEINT

xs:short

CHAR

xs:string

DATE

xs:date

DECIMAL(p,s) (NUMERIC)

xs:decimal (if s > 0),

xs:integer (if s == 0)

FLOAT (REAL, DOUBLE PRECISION)

xs:double

INTEGER

xs:int

LONG VARCHAR

xs:string

SMALLINT

xs:short

TIME

xs:time

TIMESTAMP

xs:dateTime

VARBYTE

xs:hexBinary

VARCHAR

xs:string

A.5.2 Function and Operator Pushdown

Table A-18 lists functions and operators that are pushed down to Teradata. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table A-18 Teradata Function and Operator Pushdown

Group

Functions and operators

Logical operators

and, or, not

Numeric arithmetic

+, -, * Foot 1

divFoot 2

Numeric comparisonsFootref 1

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String comparisonsFoot 3

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concat, upper-caseFootref 4, containsFoot 5 , starts-with, ends-with, fn-bea:sql-like(2,3)

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists

Footnote 1 All numeric types

Footnote 2 Only xs:decimal, xs:float, and xs:double

Footnote 3 CHAR or VARCHAR SQL data types only for all arguments

Footnote 4 CHAR or VARCHAR SQL data type only for first argument

Footnote 5 First argument must be CHAR or VARCHAR SQL data type, second argument must be a constant or parameter

A.5.3 Cast Operation Pushdown

Cast operations are not pushed down.

A.5.4 Other SQL Generation Capabilities

Table A-19 lists common query patterns that can be pushed down. (See Section 3.3.7, "Common Query Patterns" for details.)

Table A-19 Teradata Other SQL Generation Capabilities

Feature

Description

Inner joins

yes, SQL-92 syntax

Outer joins

yes

Semi joins, Anti semi joins

yes

Order by

yes

Order by: Empty order (NULL order)

fixed (always sorts NULLs low). Order-bys with "empty greatest" modifier are not pushed down.

Order by: Aggregate function in ordering expression

no

Group by

yes

Distinct pattern

yes

Trivial aggregate pattern pushdown

yes (using GROUP BY constant)

If-then-else pushdown

yes

Subsequence pushdown

yes

SQL Exit query composition (pushdown on top of SQL Exit)

yes

Runtime connection management

no connection sharing

A.6 Derby 10.6 (and higher)

The tables in this section identify all data type and other mappings that the XQuery engine generates or supports for Derby 10.6 (and higher).

A.6.1 Data Type Mappings

Table A-20 lists supported data type mappings for Derby

Table A-20 Derby Data Type Mappings

Derby Data Type

XQuery Type

BIGINT

xs:long

BLOB

Not supported

CHAR() for bit data

xs:string

CLOB

xs:string

DATE

xs:date

DOUBLE

xs:double

DECIMAL(p,s)

xs:decimal (if s > 0)

xs:integer (if s = 0)

INTEGER

xs:int

LONG VARCHAR

xs:string

LONG VARCHAR for bit data

xs:hexBinary

REAL

xs:float

SMALLINT

xs:short

TIME

xs:datetime

VARCHAR

xs:string

VARCHAR() for bit data

xs:hexBinary

BOOLEAN

xs:boolean

A.6.2 Functions and Operators

Table A-21 lists supported functions and operators for Derby

Table A-21 Derby Functions and Operators

Group

Functions and Operators

Logical operators

and, or, not

Numeric operators

+, -, *, div, idiv, mod

Numeric comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

Numeric functions

abs, ceiling, floor

String comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

concat, upper-case, lower-case, substring(2,3),string-length, contains, starts-with, ends-with, fn-bea:sql-like(2,3) fn-bea:trim, fn-bea:trim-left, fn-bea:trim-right, fn-bea:repeat, fn-bea:pad-left, fn-bea:pad-right

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time

Datetime functions

year-from-dateTime, year-from-date, month-from-dateTime, month-from-date, day-from-dateTime, day-from-date, hours-from-dateTime, hours-from-time, minutes-from-dateTime, minutes-from-time, seconds-from-dateTime, seconds-from-time, fn-bea:date-from-dateTime, fn-bea:time-from-dateTime

Aggregate

min, max, sum, avg, count, count(distinct-values)

Other

empty, exists, subsequence

A.6.3 Cast Operations

Table A-22 lists supported cast operations for Derby

Table A-22 Derby Functions and Operators

Source XQuery Type

Target XQuery Type

numeric

xs:double

numeric

xs:float

numeric

xs:int

numeric

xs:integer

numeric

xs:short

xs:decimal (and subtypes)

xs:string

xs:integer (and subtypes)

xs:decimal

xs:string

xs;double

xs:string

xs:float

xs:string

xs:int

xs:string

xs:integer

xs:string

xs:short

xs:dateTime

xs:time

A.7 Base (Generic) RDBMS Support

Each JDBC drivers provide information about inherent properties and capabilities of the RDBMS with which it is associated. During the metadata import process, Oracle Data Service Integrator queries a configured data source's JDBC driver for basic properties 7 capabilities information. Much of the information obtained is stored in the metadata section of the data service definition file (.ds). See "Understanding Data Service Annotations" in the Data Services Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Understanding Data Service Annotations.html for more information.

A.7.1 Database Capabilities Information

The database capabilities listed in Table A-23 are obtained from the operative JDBC driver and stored as properties in the .ds (data service) definition file.

Table A-23 Database Properties Derived from the JDBC Driver

Property

Description

Possible Values

supportsSchemasInDataManipulation

Boolean that identifies whether SQL statements can include schema names

true, false

supportsCatalogsInDataManipulation

Boolean that identifies whether database catalogs can be addressed by SQL

true, false

supportsLikeEscapeClause

Boolean that identifies if the database supports ESCAPE clause in LIKE expression

true, false

nullSortOrder

Order in which NULLs are sorted

low, high, unknown

identifierQuote

String used as delimiter to denote (offset) identifier labels

String value (can be empty)

catalogSeparator

String used as delimiter (separator) between catalog (or schema) and table name

String value

The Oracle Data Service Integrator XQuery engine typically quotes the names (identifiers) of object names to properly handle any special characters. The identifierQuote property is obtained from the JDBC driver. However, different RDBMSs may use different identifiers for different database object names:

- catalogs

- schemas

- tables

- columns

If necessary, you can manually override the identifier quote property for each type of identifier.

Typically, the identifierQuote property obtained from the JDBC driver is used. However, if the specific quote property is available and the RDBMS uses it, you can modify the annotation settings in the .ds file (see "Understanding Data Service Annotations" in the Data Services Developer's Guide at http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/datasrvc/Understanding Data Service Annotations.html for more information about these properties). The XQuery engine (metadata importer sub-system) uses the specific quote property (see Table A-24) if it is available, otherwise, it uses the "identifierQuote" property provided by the JDBC driver.

The only exception to this rule is for Sybase versions below Sybase 12.5.2, which is treated as a base platform. Sybase does not use quotes for catalogs even though JDBC drivers return double quote ('"') for "identifierQuote" property. The XQuery engine accommodates this mismatch by automatically setting "catalogQuote" property to the empty string.

Table A-24 Optional Quote Properties for Database Objects

Property

Description

Possible Values

catalogQuote

Special character used as quote to denote name of catalog

string

schemaQuote

Special character used as quote to denote name of schema

string

tableQuote

Special character used as quote to denote name of table

string

columnQuote

Special character used as quote to denote name of column

string

A.7.2 Data Type Mapping

When mapping SQL to XQuery datatypes, the XQuery engine first checks the JDBC typecode. If the typecode has a corresponding XQuery type, Oracle Data Service Integrator uses the matching native type name. If no matching typecode or type name is available, the column is ignored. Table A-25 shows this mapping.

Table A-25 Base Platform Data Type Mapping (JDBC<–>XQuery Equivalents)

JDBC Data Type

Typecode

XQuery Data Type

BIGINT

-5

xs:long

BINARY

-2

xs:string

BIT

-7

xs:boolean

BLOB

2004

xs:hexBinary

BOOLEAN

16

xs:boolean

CHAR

1

xs:string

CLOBFoot 1

2005

xs:string

DATE

91

xs:dateFoot 2

DECIMAL (p,s)Foot 3

3

xs:decimal (if s > 0), xs:integer (if s =0)

DOUBLE

8

xs:double

FLOAT

6

xs:double

INTEGER

4

xs:int

LONGVARBINARY

-4

xs:hexBinary

LONGVARCHARFootref 1

-1

xs:string

NUMERIC (p,s)Footref 3

2

xs:decimal (if s > 0), xs:integer (if s =0)

REAL

7

xs:float

SMALLINT

5

xs:short

TIMEFoot 4

92

xs:time

TIMESTAMP

93

xs:dateTime

TINYINT

-6

xs:short

VARBINARY

-3

xs:hexBinary

VARCHAR

12

xs:string

OTHER

1111

Oracle Data Service Integrator uses native data type name to map to an appropriate XQuery data type.

Other vendor-specific JDBC type codes

Footnote 1 Pushed down in project list only.

Footnote 2 Values converted to local time zone (timezone information removed) due to DATE limitations. See Section 3.2.1, "Date and Time Data Type Differences: Timezones and Time Precision" for more information.

Footnote 3 Where p is precision (total number of digits, both to the right and left of decimal point) and s is scale (total number of digits to the right of decimal point).

Footnote 4 Precision of underlying RDBMS determines the precision of TIME data type and how much truncation, if any, will occur in translating xs:time to TIME.

Table A-26 lists functions and operators that are pushed down to base RDBMSs. See Section 2.1.2.1, "fn-bea:is-access-allowed" for details about two-argument and three-argument versions of the fn-bea:sql-like() function.

Table A-26 Base Platform Functions and Operators

Group

Functions and Operators

Logical operators

and, or, not

Numeric arithmetic

+, -, *Foot 1

divFoot 2

Numeric comparisonsFootref 1

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String comparisonsFoot 3

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge

String functions

containsFoot 4 , starts-with, ends-with, fn-bea:sql-like(2), fn-bea:sql-like(3), upper-case, lower-case

Datetime comparisons

=, !=, <, <=, >, >=, eq, ne, lt, le, gt, ge on xs:dateTime, xs:date, xs:time

Other

empty, exists

Footnote 1 All numeric types

Footnote 2 Support for xs:decimal, xs:float, and xs:double data types only.

Footnote 3 Arguments must be CHAR or VARCHAR SQL data types.

Footnote 4 First argument must be SQL data type CHAR or VARCHAR; second argument must be a constant or parameter; and RDBMS must support LIKE (with ESCAPE) clause.

A.7.3 Cast Operation Pushdown

For base RDBMS, cast operations are not pushed down.

A.7.4 Other SQL Generation Capabilities

Table A-27 displays other SQL Pushdown capabilities, as discussed in Section 3.3.7, "Common Query Patterns."

Table A-27 Base Platform SQL Generation Capabilities

Query

Supported

If-Then-Else

no

Inner joins

yes (SQL-89 syntax)

Outer joins

no

Semi-joins, Anti-semi-joins

no

Order by

yes

Order by: Empty (NULL) order supported

Database-dependent

Order by: Aggregate function in ordering expression

no

Group by

yes (by column only)

Distinct pattern

yes

Trivial aggregate pattern

no

Direct SQL composition

no

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2018, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Pre-General Availability Draft Documentation Notice

If this document is in public or private pre-General Availability status:

This documentation is in pre-General Availability status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Pre-General Availability Draft Documentation Notice

If this document is in private pre-General Availability status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your pre-General Availability trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.