Monday, February 16, 2009

Oracle Cross Reference Table

In Oracle ESB the crosse reference table was introduced to create domain value maps.

Within Oracle AIA this mechnism is one of the technical foundation on which AIA runs.

The documentaion was limited on this functionality. Some core functions used in stylesheets were explained. Popular cross-reference functions are:
  • xref:populateXRefRow() - Insert a record in the XRef table
  • xref:markForDelete() - Delete a record from the XRef table
Note: The xref:markForDelete() will update the delete-column in the XRef-table. In future releases, this function could change that it will physicly delete the table instead of update.

Since Oracle SOA Suite 10.1.3.4, the documentati
on has improved dramaticly. Detailes description and examples are now documented. It can be found here:

http://download.oracle.com/docs/cd/E12524_01/doc.1013/e12638/esb_xref.htm

From conceptualpoint of view and almost technical it can be used a reference documentation in olde versions 10.1.3.3 and AIA 2.0.x.

Note that there are some challenges when you use the cross-reference table.

Check that the table has is optimzed for perfomance. That is has to correct indexes and contraints. The latest patches of the Oracle SOA Suite, applies these indexes and contraints.

One question on this cross-reference table is the date format. The table contains two columns that specifies the date since 1 january 1970. You can obtain the correct format as follows:


select
xref_table_name
, xref_column_name
, row_number
, value
, is_deleted
, to_char((to_date('01/01/1970','mm/dd/yyyy')
+ last_modified/86400000), 'dd-mm-yyyy hh24:mi:ss')

, to_char((to_date('01/01/1970','mm/dd/yyyy')
+ last_accessed/86400000), 'dd-mm-yyyy hh24:mi:ss')

from xref_data;

Post a Comment