Oracle® Enterprise Service Bus Developer's Guide 10g (10.1.3.3.0) Part Number E10295-01 |
|
|
View PDF |
The cross referencing feature of Oracle Enterprise Service Bus enables you to associate identifiers for equivalent entities created in different applications. For example, you can use cross references to associate a customer entity created in one application (with native id Cust_100
) with an entity for the same customer in another application (with native id CT_001)
.
This chapter explains how to create, populate, and use cross references. It contains the following topics:
Many a time, when you create or update objects in one application, you also want to propagate the changes to another application. For example, when a new customer is created in a SAP application, you might want to create a new entry for the same customer in your Oracle E-Business Suite application named as EBS
.
However, the applications that you are integrating could be using different entities to represent the same information. For example, for a new customer in a SAP application, a new row is inserted in its Customer
database with a unique identifier such as SAP_001
. When the same information is propagated to an Oracle E-Business Suite application and a Siebel application, a new row should be inserted with different identifiers, such as EBS_1001
and SBL001
. In such cases, you need some kind of functionality to map these identifiers with each other so that they could be interpreted by different applications to be referring to the same entity. This can be done by using cross references tables. Table 8-1 shows a cross reference table containing information about customer identifiers in different applications.
The identifier mapping is also required when information about a customer is updated in one application and the changes need to be propagated in other applications also. You can also integrate different identifiers by using a common value integration pattern, which maps to all identifiers in a cross reference table. For example, you can add one more column Common
to the cross reference table shown in Table 8-1. The updated cross reference table would appear as shown in Table 8-2.
Table 8-2 Cross Reference Table with the Common Column
SAP | EBS | SBL | Common |
---|---|---|---|
SAP_001 |
EBS_1001 |
SBL001 |
CM001 |
SAP_002 |
EBS_1002 |
SBL002 |
CM002 |
Figure 8-1 shows how you can use the common value integration pattern to map identifiers in different applications.
Figure 8-1 Common Value Integration Pattern Example
A cross reference table consists of following two parts, metadata and the actual data. The metadata is created by using the cross reference command line utilities and is stored in the repository as an XML file. The actual data is stored in the database.
You can use a cross reference table to look up column values at run time. However, before using a cross reference to look up a particular value, you need to populate it at run time. This can de done by using the cross reference XPath functions. The XPath functions enable you to populate a cross reference, perform lookups, and delete a column value. These XPath functions can be used in the Expression builder dialog box to create an expression or in the XSLT Mapper dialog box to create transformations.
The Expression builder dialog box is displayed when you click the Invoke Expression Builder icon in the routing rules panel. Figure 8-2 shows how you can select the cross reference functions in the Expression builder dialog box.
Figure 8-2 Expression Builder Dialog Box with Cross Reference Functions
The XSLT Mapper dialog box is displayed when you create an XSL file to transform data from one XML schema to another. Figure 8-3 shows how you can select the cross reference functions in the XSLT Mapper dialog box.
Figure 8-3 XSLT Mapper Dialog Box with Cross Reference Functions
Oracle Enterprise Service Bus provides a set of command line utilities that you can use for cross reference administration. The command line utilities are xreftool, xrefimport, and xrefexport.
Note:
Before using these command line utilities, you need to start the Oracle SOA Suite Server.The xrefimport and xrefexport utility enables you to import and export cross reference metadata and data.
The xreftool utility enables you to create cross reference metadata such as creating cross reference table and columns. However, you cannot populate the cross reference tables by using the xreftool commands. You can also use the xreftool commands to modify, and delete cross reference tables. To use the xreftool utility, perform the following steps:
Create two variables named OC4J_USERNAME
and OC4J_PASSWORD
as environment variables.
Start the command prompt.
At the prompt, type the following command:
cd <ORACLE_HOME>\integration\esb\bin
At the prompt, type the following command:
xreftool -shell
This command starts the cross reference shell where you can run xreftool commands.
Table 8-3 lists various xreftool commands.
You can use the xreftool utility to create, modify, and delete cross reference tables. To create a cross reference table, use the following command in the cross reference shell:
createTable TableName
For example, the createTable customers
command creates a cross reference table named customers
:
Note:
The table names and column names are not case-sensitive.To view a list of all cross reference tables present in the repository, you can use the following xreftool command:
listTables
You can modify the cross reference tables by adding, and deleting columns. Table 8-4 lists various xreftool utility commands that you can use to modify a cross reference table.
Table 8-4 xreftool Commands for Modifying a Cross Reference Table
Functionality | Command | Example |
---|---|---|
Adding a column to a cross reference table |
|
|
Adding multiple columns to a cross reference table |
|
|
Deleting a column When you delete a column, the data corresponding to the column is deleted from the database. |
deleteColumn |
|
Viewing all columns of a cross reference table |
|
|
A cross reference table needs to be populated at run time before being used. This can be done by using the following XPath extension functions:
You can use the xref:populateXRefRow
function to populate a cross reference column with a value. This function returns a string value which is the cross reference value being populated. The syntax of the xref:populateXRefRow
function is as follows:
xref:populateXRefRow(xrefTableName as string, xrefReferenceColumnName as string, xrefReferenceValue as string, xrefColumnName as string, xrefValue as string, mode as string) as string
xrefTableName
: The name of the cross reference table.
xrefReferenceColumnName
: The name of the reference column.
xrefReferenceValue
: The value corresponding to the reference column name.
xrefColumnName
: The name of the column to be populated.
xrefValue
: The value to be populated in the column.
mode
: The mode in which the xref:populateXRefRow
function populates the column. You can specify any of the following values: ADD
, LINK
, or UPDATE
. Table 8-5 describes these modes.
Table 8-5 xref:populateXRefRow Function Modes
Mode | Description | Exception Reasons |
---|---|---|
ADD |
Adds the reference value and the value to be added. For example, |
Exceptions can occur due to the following reasons:
|
LINK |
Adds the cross reference value corresponding to the existing reference value. For example, |
Exceptions can occur due to the following reasons:
|
UPDATE |
Updates the cross reference value corresponding to an existing reference column-value pair. For example, |
Exceptions can occur due to the following reasons:
|
Note:
Themode
parameter values are case-sensitive and should be specified in the upper case only as shown in Table 8-5.Table 8-6 describes the xref:populateXRefRow
function modes and exception conditions for these modes.
Table 8-6 xref:populateXRefRow Function Results with Different Modes
Mode | Reference Value | Value to be Added | Result |
---|---|---|---|
ADD |
Absent Present Present |
Absent Absent Present |
Success Exception Exception |
LINK |
Absent Present Present |
Absent Absent Present |
Exception Success Exception |
UPDATE |
Absent Present Present |
Absent Absent Present |
Exception Exception Success |
The xref:populateXRefRow
function can be used in transformation to populate a column of a cross reference table by performing the following steps:
In the XSLT Mapper dialog box, expand the trees in the Source and Target panes.
Drag and drop the source element to the target element.
In the Component Palette, click the down arrow list and then select Advanced Functions.
Drag and drop populateXRefRow onto the line that connects the source object to the target object.
A populateXRefRow icon appears on the connecting line.
Double-click the populateXRefRow icon.
The Edit Function – populateXRefRow dialog box is displayed, as shown in Figure 8-4.
Figure 8-4 The Edit Function – populateXRefRow Dialog Box
Specify the following values for the fields in the Edit Function – populateXRefRow dialog box:
In the tableName field, enter the name of the cross reference table.
In the referenceColumnName field, enter the name of the cross reference column.
In the referenceValue field, you can manually enter a value or press Ctrl-Space to launch XPath Building Assistant. Press the Up and Down arrow keys to locate an object in the list and press Enter to select that object.
In the columnName field, enter the name of the cross reference column.
In the value field, you can manually enter a value or press Ctrl-Space to launch the XPath Building Assistant.
In the mode field, enter a mode in which you want to populate the cross reference table column, for example, ADD
.
Click OK.
A populated Edit Function – populateXRefRow dialog box is shown in Figure 8-5.
Figure 8-5 The Populated Edit Function – populateXRefRow Dialog Box
Many a time, two values in a system can correspond to a single value in another system. For example, as shown in Table 8-7, the SAP_001
and SAP_0011
values refer to one value of the EBS and the SBL application.
Table 8-7 A Cross Reference Table with Multiple Column Values
SAP | EBS | SBL |
---|---|---|
SAP_001 SAP_0011 |
EBS_1001 |
SBL001 |
SAP_002 |
EBS_1002 |
SBL002 |
To populate a column in the cross reference table with multiple values, you can use the xref:populateXRefRow1M
function. The syntax of the xref:populateXRefRow1M
function is as follows:
xref:populateXRefRow1M(xrefTableName as string, xrefReferenceColumnName as string, xrefReferenceValue as string, xrefColumnName as string, xrefValue as string, mode as string) as string
xrefTableName
: The name of the cross reference table.
xrefReferenceColumnName
: The name of the reference column.
xrefReferenceValue
: The value corresponding to reference column name.
xrefColumnName
: The name of the column to be populated.
xrefValue
: The value to be populated in the column.
mode
: The mode in which the xref:populateXRefRow
function populates the column. You can specify either of the two values, ADD
or LINK
. Table 8-8 describes these modes:
Table 8-8 xref:populateXRefRow1M Function Modes
Mode | Description | Exception Reasons |
---|---|---|
ADD |
Adds the reference value and the value to be added. For example, |
Exceptions can occur due to the following reasons:
|
LINK |
Adds the cross reference value corresponding to the existing reference value. For example, |
Exceptions can occur due to the following reasons:
|
Table 8-9 describes the xref:populateXRefRow1M
function modes and exception conditions for these modes.
Table 8-9 xref:populateXRefRow1M Function Results with Different Modes
Mode | Reference Value | Value to be Added | Result |
---|---|---|---|
ADD |
Absent Present Present |
Absent Absent Present |
Success Exception Exception |
LINK |
Absent Present Present |
Absent Absent Present |
Exception Success Exception |
The design-time steps for using the xref:populateXRefRow1M
function are similar to the xref:populateXRefRow
function described in "Using the xref:populateXRefRow Function".
After populating the cross reference table, you can use it to look up for a value. This can be done by using the following XPath extension functions:
You can use the xref:lookupXRef
function to look up a cross reference column for a value that corresponds to a specific value in a reference column. For example, the following function looks up the Common
column of the cross reference table described in Table 8-2 for a value corresponding to the SAP_001
value in the SAP
column.
xref:lookupXRef("customers","SAP","SAP_001", "Common", true())
The syntax of the xref:lookupXRefRow
function is as follows:
xref:lookupXRef(xrefTableName as string, xrefReferenceColumnName as string, xrefReferenceValue as string, xrefColumnName as string, needAnException as boolean) as string
xrefTableName
: The name of the cross reference table.
xrefReferenceColumnName
: The name of the reference column.
xrefReferenceValue
: The value corresponding to the reference column name.
xrefColumnName
: The name of the column to be looked up for the value.
needAnException
: Specify true
or false
.
If needAnException
parameter is set to true
, an exception is thrown if the value, being looked up in the table, is not found. If needAnException
parameter is set to false
, an empty value is returned if the value, being looked up in the table, is not found.
An exception can occur because of the following reasons:
The cross reference table with the given name is not found.
The specified column names are not found.
The specified reference value is empty.
Multiple values are found.
You can use the xref:lookupXRef
function to look up a cross reference table column by performing the following steps during transformation:
In the XSLT Mapper dialog box, expand the trees in the Source and Target panes.
Drag and drop the source element to the target element.
In the Component Palette, click the down arrow list and then select Advanced Functions.
Drag and drop lookupXRef onto the line that connects the source object to the target object.
A lookupXRef icon appears on the connecting line.
Double-click the lookupXRef icon.
The Edit Function – lookupXRef dialog box is displayed, as shown in Figure 8-6.
Figure 8-6 The Edit Function – lookupXRef Dialog Box
Specify the following values for the fields in the Edit Function – lookupXRef dialog box:
In the tableName field, enter the name of the cross reference table.
In the referenceColumnName field, enter the name of the cross reference column.
In the referenceValue field, you can manually enter a value or press Ctrl-Space to launch the XPath Building Assistant. Press the Up and Down arrow keys to locate an object in the list and press Enter to select that object.
In the columnName field, enter the name of the cross reference column.
In the needException field, enter Yes to raise an exception if no value is found, else enter No.
Click OK.
A populated Edit Function – lookupXRef dialog box is shown in Figure 8-7.
Figure 8-7 Populated Edit Function – lookupXRef Dialog Box
You can use the xref:lookupXRef1M
function to look up a cross reference column for multiple values corresponding to a specific value in a reference column. This function returns a node-set containing the multiple nodes. Each node in the node-set contains a value.
For example, the following function looks up the SAP
column of Table 8-7 for multiple values corresponding to EBS_1001
value in the EBS
column:
xref:lookupXRef1M("customers","EBS","EBS_1001", "Common", true())
The syntax of the xref:lookupXRefRow1M
function is as follows:
xref:lookupXRef1M(xrefTableName as string, xrefReferenceColumnName as string, xrefReferenceValue as string, xrefColumnName as string, needAnException as boolean) as node-set
xrefTableName
: The name of the cross reference table.
xrefReferenceColumnName
: The name of the reference column.
xrefReferenceValue
: The value corresponding to the reference column name.
xrefColumnName
: The name of the column to be looked up for the value.
needAnException
: Specify true
or false
.
If needAnException
parameter is set to true
, an exception is thrown if the value, being looked up in the table, is not found. If needAnException
parameter is set to false
, an empty value is returned if the value, being looked up in the table, is not found.
An exception can occur because of the following reasons:
The cross reference table with the given name is not found.
The specified column names are not found.
The specified reference value is empty.
The design-time steps for using the xref:lookupXRef1M
function are similar to the xref:lookupXRef
function explained in "Using the xref:lookupXRef Function".
You can use the xref:markForDelete
function to delete a value in a cross reference table. The value in the column is marked as deleted. This function returns true
if deletion was successful, else it returns false
.
A cross reference table row should have at least two mappings. Therefore, if you have only two mappings in a row and you mark one value for deletion, then the value in the other column is also deleted.
Any column value marked for deletion is treated as if the value does not exist. Therefore, you can populate the same column with the xref:populateXRefRow
function in the ADD
mode.
However, using the column value marked for deletion as a reference value in the LINK
mode of the xref:populateXRefRow
function, would raise an error.
The syntax for the xref:markForDelete
function is as follows:
xref:markForDelete(xrefTableName as string, xrefColumnName as string, xrefValueToDelete as string) return as boolean
xrefTableName
: The name of the cross reference table.
xrefColumnName
: The name of the column from which you want to delete a value.
xrefValueToDelete
: The value to be deleted.
An exception can occur due to the following reasons:
The cross reference table with the given name is not found.
The specified column name is not found.
The specified value is empty.
The specified value is not found in the column.
Multiple values are found.
Perform the following steps to delete a value from a cross reference table column:
In the XSLT Mapper dialog box, expand the trees in the Source and Target panes.
Drag and drop the source element to the target element.
In the Component Palette, click the down arrow list and then select Advanced Functions.
Drag and drop markForDelete onto the line that connects the source object to the target object.
A markForDelete icon appears on the connecting line.
Double-click the markForDelete icon.
The Edit Function – markForDelete dialog box is displayed, as shown in Figure 8-8.
Figure 8-8 Edit Function – markForDelete Dialog Box
Specify the following values for the fields in the Edit Function – markForDelete dialog box:
In the tableName field, enter the name of the cross reference table.
In the columnName field, enter the name of the column.
In the value field, you can manually enter a value or press Ctrl-Space to launch the XPath Building Assistant. Press the Up and Down arrow keys to locate an object in the list and press Enter to select that object.
A populated Edit Function – markForDelete dialog box is shown in Figure 8-9.
Figure 8-9 Populated Edit Function – markForDelete Dialog Box
Click OK.
You can import and export the cross reference tables by using the xrefimport and xrefexport utilities. However, before using the xrefimport and xrefexport utilities, you need to create the following environment variables:
DB_URL
: Contains the connection string of the database.
Example: jdbc:oracle:thin:@stapm21.us.oracle.com:1521:orcl
DB_USER
: The user name of the schema where the cross reference tables are created.
Example: DB_PASSWORD=oraesb
DB_PASSWORD
: The password associated with the user specified in the DB_USER
variable.
Example: DB_USER=oraesb
Note:
If you have not createdOC4J_USERNAME
and OC4J_PASSWORD
environment variables earlier, then you also need to create these environment variables.The following sections explain how to import and export cross reference tables:
The xrefexport utility enables you to export a cross reference table metadata along with the values. The exported data is stored in an XML file which is based on the schema defined in "Schema Definition (XSD) File for Cross References". If the table that you are exporting contains columns without any values, then the missing column values are replaced with an empty cell
element in the exported XML file.
To export a cross reference table, use the following command:
xrefexport -file FILENAME -table TABLENAME
The FILENAME
and TABLENAME
parameters are mandatory. The FILENAME
parameter specifies the location of the file to which the data will be exported. The TABLENAME
parameter specifies the name of the cross reference table to be exported.
The xrefimport utility enables you to import a cross reference table metadata from an XML file. The XML file that you are importing should be based on the schema defined in "Schema Definition (XSD) File for Cross References".
To import cross reference metadata, use the following command:
xrefimport -file FILENAME [-mode <ignore | overwrite>] [ -generate <columnName>]
The following list explains the various parameters of the xrefimport function:
FILENAME
: The FILENAME
parameter specifies the location of the file from which the data will be imported.
mode
: The mode
parameter specifies how the conflicts with existing data will be handled. The mode
parameter can consist of one of the two values, ignore
or overwrite
. The value ignore
specifies that the existing data should be kept in the repository. The value overwrite
specifies that existing data should be overwritten with the data present in the XML file. The mode parameter is optional and is used only in case of a conflict. The default value is ignore
.
generate
: The generate
parameter can be used to create a column automatically while importing the metadata. For example, generate SAP
creates a SAP
column automatically while importing the metadata.
Example 8-1 shows the cross reference XSD file. All imported cross reference XML files are validated against this schema definition file. All functions in the schema definition file should be in the following namespace:
http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions
Example 8-1 Cross Reference XSD File
<?xml version="1.0" encoding="UTF-8" ?> <schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://xmlns.oracle.com/xref" xmlns:tns="http://xmlns.oracle.com/xref" elementFormDefault="qualified"> <element name="xref" type="tns:xrefType"/> <complexType name="xrefType"> <sequence> <element name="table"> <complexType> <sequence> <element name="columns" type="tns:columnsType" minOccurs="0" maxOccurs="1"/> <element name="rows" type="tns:rowsType" maxOccurs="1" minOccurs="0"/> </sequence> <attribute name="name" use="required"> <simpleType> <restriction base="string"> <minLength value="1"/> </restriction> </simpleType> </attribute> </complexType> </element> </sequence> </complexType> <complexType name="columnsType"> <sequence> <element name="column" minOccurs="1" maxOccurs="unbounded"> <complexType> <attribute name="name" use="required"> <simpleType> <restriction base="string"> <minLength value="1"/> </restriction> </simpleType> </attribute> </complexType> </element> </sequence> </complexType> <complexType name="rowsType"> <sequence> <element name="row" minOccurs="1" maxOccurs="unbounded"> <complexType> <sequence> <element name="cell" minOccurs="1" maxOccurs="unbounded"> <complexType> <simpleContent> <extension base="string"> <attribute name="colName" use="required"> <simpleType> <restriction base="string"> <minLength value="1"/> </restriction> </simpleType> </attribute> </extension> </simpleContent> </complexType> </element> </sequence> </complexType> </element> </sequence> </complexType> </schema>