Mapping Data with Cross-References

21.1 Introduction to Cross References

Cross reference tables map identifiers that represent equivalent objects across multiple applications, associating like objects created in different external applications.

For example, you can use cross references to map customer identifiers for records that were created in multiple customer management systems. Cross references are similar to domain value maps (DVMs), but cross references can be updated during runtime, allowing you to dynamically integrate values between systems. Cross reference data updated at runtime is persisted in the database.

Cross references can be used across Oracle SOA Suite components. In Service Bus, you can create cross reference tables in both JDeveloper and the Oracle Service Bus Console.

21.1.1 Cross Reference Database Tables

All cross reference mappings are stored in the form of tables. When you create a cross reference table in a Service Bus project in JDeveloper, you only define the metadata for the table (that is, the keys and not the values). All cross reference tables are stored in the database, by default in the XREF_DATA table. Cross reference tables can either be custom or generic (the default). Generic tables are all stored in a single database table that is created during installation (XREF_DATA); custom tables are each stored in individual database tables that you create. Service Bus uses the standard SOA Suite data source, jdbc/SOADataSource, to access the database tables. You can create your own data source with the following requirements:

The data source must be named jdbc/xref, otherwise the runtime will use the default schema.
The data source must be XA enabled.

For instructions on creating a custom database table, see "How to Create Custom Database Tables" in Developing SOA Applications with Oracle SOA Suite.

21.1.2 Cross Reference Functions

In the Service Bus message flow, you can reference cross reference tables from XQuery expressions and XSLT transformations using a set of XRef functions to lookup, populate, and update cross reference entries at runtime based on information in incoming messages. Use these functions to populate the values for the keys you defined when you first created the cross reference table.

You can access the XRef functions in JDeveloper from the XSLT mapper, the XQuery mapper, and the expression editors. In the Oracle Service Bus Console, the functions are available from the expression and condition editors. For information about the Service Bus XRef functions, see Cross-Reference Functions.

21.1.3 Managing Cross Reference Data at Runtime

Fusion Middleware Control provides features to help you manage cross reference data in the runtime. For information and instructions, see "Managing Cross References" in Administering Oracle SOA Suite and Oracle Business Process Management Suite.

21.2 Creating Cross Reference Tables in JDeveloper

You can create cross reference tables in a Service Bus project in JDeveloper, and then use them in XQuery expressions in pipelines and split-joins to map objects between external systems.

When you create a cross reference table, you only need to specify a name for the table and the names of the end systems that are sharing the data. You do not need to specify the values for each system in the design time.

21.2.1 How to Create Cross Reference Tables in JDeveloper

To create cross reference tables in JDeveloper:

In the Application Navigator in JDeveloper, right-click the Service Bus project or folder in which you want to create the cross reference.
In the menu that appears, point to New and select Cross Reference(XREF).

The Create Cross Reference(XREF) File dialog appears.
In the File Name field, enter a unique name for the cross reference file.

Two cross reference tables cannot have same name in the cross reference repository. The file name is the name of the cross reference table with an extension of .xref.
Optionally enter a new directory location and a brief description for the cross reference.

The location must be within the directory structure of the current application.
In the End System fields, enter the names for up to two end systems.

The end systems map to the cross reference columns in a cross reference table. You can add more system names once you create the cross reference table. Each name must be unique within a table.
Click OK.

The Cross Reference Editor appears with the new cross reference table displayed.
To add new end systems to the cross reference table, click Add above the End Systems table, double-click in the newly added row, and enter the end system name.
To create a custom database table for this cross referencing mapping, see How to Create Custom Database Tables in Developing SOA Applications with Oracle SOA Suite.

21.3 Working with Cross Reference Resources in the Oracle Service Bus Console

If you are using the Oracle Service Bus Console, you can create new cross reference tables or you can upload cross reference tables that you first created in JDeveloper into a cross reference (XRef) resource.

21.3.1 How to Create Cross Reference (XRef) Resources in the Console

When you create a cross reference table, you only need to specify a name for the table and the names of the end systems that are sharing the data. You do not need to specify the values for each system in the design time.

Before you Begin

If you are uploading a cross reference file already created in JDeveloper, as described in Creating Cross Reference Tables in JDeveloper, make sure the file is available on your system.

To create a cross reference resource in the console:

In the Project Navigator, right-click the project or folder to contain the new cross reference mapping, point to Create, and select Resource. Click Miscellaneous, click Cross Reference (XRef), and then click OK.

The Create Cross Reference (XRef) dialog appears.
Do one of the following:
- To upload an existing cross reference table, click Browse next to the File Upload field and then navigate to and select the file you created in JDeveloper.
  
  The Resource Name field is automatically populated with the file name minus the file extension. You can change this name.
- To create a new cross reference table, enter a unique name for the cross reference resource.
Optionally, enter a brief description of the resource.
Click Create.

The new cross reference table appears on the Cross Reference (XREF) Definition Editor.
In the Cross Reference (XREF) Definition Editor toolbar, click Save.
To end the session and deploy the configuration to the runtime, click Activate.

21.3.2 How to Edit Cross Reference Resources in the Console

If you are using the Oracle Service Bus Console, use the following procedure to edit cross reference resources.

To edit a cross reference resource in the console:

In the Project Navigator, expand the project and folders containing the cross reference to edit.
Right-click the cross reference name, and select Open.
To modify the source file, do the following:
1. Click Edit Source in the toolbar.
  
  The Edit Source dialog appears.
2. To browse to and select a new cross reference file to upload, click Browse.
3. To modify the contents of the file, update the code directly in the Contents section of the dialog.
4. Click Save.
To add end systems to the cross reference table, click Add above the End System table and enter the name of the system in the new row that appears.
In the Cross Reference (XREF) Definition Editor toolbar, click Save.
To end the session and deploy the configuration to the runtime, click Activate.

21.3.3 How to Create a Custom Database Table in the Console

As mentioned previously, all the runtime data is stored in the XREF_DATA table by default. If you want to create custom database tables, then perform the following steps.

Note:

When you create a cross reference table in JDeveloper, you can run the custom database creation script directly from the Cross Reference Editor. If you use the Oracle Service Bus Console, you create the custom database table manually. For more information, see "How to Create Custom Database Tables" in Developing SOA Applications with Oracle SOA Suite.

To create a custom database table in the console:

On the Cross Reference (XRef) Definition Editor, select Enable Optimization.
In the Table Name field, enter a custom name for the database table.

This custom database table name must be prefixed with xref_, and cannot be XREF_DATA or XREF_DELETED_DATA.
Click Save.
Create the custom table in the soainfra schema of the Oracle Fusion Middleware database using the following syntax:
```
CREATE TABLE TABLE_NAME (
    ROW_ID VARCHAR2(48) NOT NULL,
    SYSTEM1 VARCHAR2(100),
    SYSTEM2 VARCHAR2(100),
    SYSTEM3 VARCHAR2(100),
    LAST_MODIFIED TIMESTAMP NOT NULL
);
```
Where TABLE_NAME is the name you specified in step 2 for the custom table, and SYSTEM1, SYSTEM2, and SYSTEM3 are the names of the end systems being cross referenced.

21.4 Deleting a Cross Reference Resource

If any resources reference the resource you want to delete, remove those references before deleting the resource.

You can delete the resource even if it is referenced by other resources, though this might result in conflicts due to unresolved references to the deleted resource.

21.4.1 How to Delete a Cross Reference Resource

Before deleting a cross reference, check for references and dependencies. In the Oracle Service Bus Console, open the cross reference resource in the Cross Reference (XREF) Definition Editor and click the Tools icon in the upper right, and then select References to find out whether there are any references. In JDeveloper, right-click the XQuery resource and select Explore Dependencies.

To delete a cross reference resource:

In the Application Navigator or Project Navigator, expand the project and folders containing the resource to delete.
Right-click the name of the resource, and select Delete.
If you are using JDeveloper, a confirmation dialog displays the number of references for the transformation. Click Show Usages to view information about the references, and then click Yes to confirm that you want to delete the resource.
If you are using the Oracle Service Bus Console, click Activate to end the session and deploy the configuration to the runtime.

21.5 Populating Cross Reference Tables in Oracle Service Bus

Before using a cross reference to look up a particular value, you must populate it at runtime. Use the cross reference XPath functions provided with Service Bus to populate the cross-reference tables.

The XPath functions let you populate a cross reference column, perform lookups, and delete a column value. These XPath functions can be used in the Expression Builder to create an expression or in the XSLT Mapper to create transformations. You can access the Expression Builder dialog through several pipeline activities and split-join operations.

For information about the XRef functions provided with Service Bus, see Cross-Reference Functions. For general information and instructions on using functions in the Oracle Service Bus Console expression and condition editors, see Building Expressions in the Editor Workspace Text Fields.

For information, examples, and instructions on managing cross reference data in JDeveloper, see the following topics in Developing SOA Applications with Oracle SOA Suite. The information in these topics is also helpful when working in the Oracle Service Bus Console.