Monday, September 17, 2012

Storing the Cross reference data to a custom table - Oracle SOA Suite

Storing the Cross reference data to a custom table - Oracle SOA Suite:

Cross references enable you to dynamically map values for equivalent entities created in different applications. Cross references are stored in the form of tables.

Oracle SOA Suite 11g provides the new feature to store the cross reference data to a custom table instead of storing to a standard XREF_DATA table, this feature enable as to manage the data properly.

Different tables can be created based on the data like order data can be stored in XREF_ORDER table and account data can be stored in the XREF_ACCOUNT. This will improve the performance of populating and looking up the data whenever the volume of the data is more.
  • Right-click the project and select New.
  • Select SOA Tier from the Categories section, and then select Transformations.
  • Select Cross Reference (XREF) from the Items section.

The screen is different in Oracle SOA Suite 12c to create the custom XREF.


  • Click OK.
  • In the File Name field, specify the name of the cross reference file.
  • In the End System fields, enter the end system names.

  • Click OK.
  • From the Optimize list, select Yes in the Cross Reference Editor.
  • Enter the table name. Table name should begin with 'xref_' and cannot be 'xref_data' or 'xref_deleted_data' which are reserved table names for XREF runtime.

  • Click on Generate Table DDL
  • Save the DDL to a file and run it through a SQL tool or Click on Run to directly run it from here. If the table exist already then select the option Generate Drop DDL also.

  • Click on Run and select the connection  -  the table should be created under the SOAINFRA schema.

  • Click on Run.
  • Click on OK then OK.This will create the xref definition as shown below.

 Populating the xref data:  

Lookup the xref data:


  1. Thanks for information,
    Im nt able to insert rows into the db after creating can u help me to sort out

  2. can you provide me the error details what you are receiving

  3. Hi Albin
    I tried to follow the above steps to create a xref table in external schema and tried executing the below xref fn.
    xref:populateXRefRow("Test.xref","ERM","S001","SFDC","e001","ADD"). I am getting the below error:

    "Unable to access Cross Reference Values from Database.The SQL Exception is: "ORA-00942: table or view does not exist[[
    Please ensure that the database is accessible. If accessible, please look at the stack trace and fix the issue. If unable to fix contact Oracle Support

    Can you please help me in fixing the issue.


  4. can you verify the xref name and path(Test.xref) is correct and also verify the table name specified while creating the xref is created in SOAINFRA schema?

  5. XPath expression failed to execute.
    An error occurs while processing the XPath expression; the expression is xref:populateXRefRow('oramds:/apps/XRef/Oracle_SOA.xref','SOA','soa','Oracle','oracle','ADD')
    The XPath expression failed to execute; the reason was: oracle.fabric.common.xml.xpath.XPathFunctionException: Invalid metadata for Cross Reference Table "oramds:/apps/XRef/Oracle_SOA.xref"
    Please ensure the metadata confirms to the XSD.
    Check the detailed root cause described in the exception message text and verify that the XPath query is correct.

    Pls find solution