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:
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.14, "Restrictions Related to Client Ad-Hoc Queries"
Section 2.1.15, "Implementation-Specific Functions and Operators"
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 |
|
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 |
|
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 |
|
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. |
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:
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()
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.
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.
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
This section describes the following duration, date, and time function extensions to the Oracle implementation of XQuery:
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.
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.
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".
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.
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".
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.
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.
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".
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.
This section describes the following Oracle Data Service Integrator execution control function extensions to the Oracle implementation of XQuery:
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))
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.
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:
Does not raise an error and
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()
)
)
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.
This section describes the following numeric function extensions to the Oracle implementation of XQuery:
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/.
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.
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.
This section describes the following function extensions to the Oracle implementation of XQuery:
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.
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".
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>
This section describes the following QName function extensions to the Oracle implementation of XQuery:
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.
This section describes the following sequence function extensions to the Oracle implementation of XQuery:
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>)
This section describes the following string function extensions to the Oracle implementation of XQuery:
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
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%.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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);
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:
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);
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>
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>
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>
This section provides several examples showing how to use the built-in mutator functions to perform common update operations.
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"
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.
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);
}
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);
}
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);
}
}
}
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);
}
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);
}
}
}
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
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.
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. |
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.3, "Implementation-Defined Values for XQuery Language Processing"
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
Beyond compliance with the specification, Oracle's XQuery language implementation (the Oracle Data Service Integrator XQuery engine) extends the XQuery language via the following:
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.
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) }"
...
/>
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 — 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 |