Saturday, November 12, 2011

Oracle SOA Suite - Utility for bulk import of Xref data.


Oracle SOA Suite - Utility for bulk import of Xref data:

In 10g, we had the xrefimport tool to bulk import the data from file to the XREF_DATA table but in Oracle SOA Suite 11g or Oracle SOA Suite 12c, there is no out of box solution to bulk import the data to XREF_DATA table.
We have implemented a utility to bulk import the data to XREF_DATA table and thought of sharing because it may help the people whoever looking for the utility.

The utility will generate the Xref data file (XREFData.LST) based on the data available in the staging table (XREF_STG) and using the java program the file contents are imported to XREF_DATA table.

The steps to import the bulk data:

1. Create the temporary table XREF_STG with the required cross reference columns (as per your requirement).
E.g.: To load cross reference data for ITEM_ No and ASSET_INT_ID


2. Insert all the records to the XREF_STG table (without generating the file using the script the sample file can be modified manually to import the data).

3.
Execute the below script sql script(xref_import.sql), this will generate the XREFData.LST file based on the data available in the XREF_STG table; Change the XREF table and the column names before executing the script.
/***************************************************************************
* This script will select records to insert into cross-reference table from XREF_STG table and * * *create the file XREFData.LST
*****************************************************************************/
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 100
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET SERVEROUTPUT ON
SPOOL XREFData
SELECT '<xref xmlns="http://xmlns.oracle.com/xref">',
'<table name="oramds:/apps/EAIMetaData/Xref/OM_ASSET_INT_ID_ITEM_NO.xref">',
'<columns>',
'<column name="ASSET_INT_ID"/>',
'<column name="ITEM_NO"/>',
'</columns>',
'<rows>'
FROM DUAL;
SELECT '<row><cell colName="ASSET_INT_ID">'||ASSET_INT_ID||
'</cell><cell colName="ITEM_NO">'||ITEMNO||'</cell></row>'
FROM XREF_STG;
SELECT '</rows>',
'</table>',
'</xref>'
FROM DUAL;
DELETE FROM XREF_STG
/
SPOOL OFF
commit
EXIT

The sample file generated by the script:



4. Change the database user details where the XREF_DATA table is available (dbUrl, dbUser and dbPassword) and XREFData.LST file path (xreffilePath) in com.tr.xrefimport. XREFImportUtility.java file.

5.
Execute com.tr.xrefimport. XREFImportUtility.java, this will insert all the records from the file XREFData.LST to the XREF_DATA table.

6.
If we are inserting the same record again the record will be ignored, this behavior can be changed by setting the mode as overwrite in the java program then the existing row will be overwritten.

7.
Verify the inserted data in XREF_DATA table.

DOWNLOAD XREFIMPORTUTILITY


3 comments:

  1. Hi,

    There is a coloum ROW_NUMBER in XREF_DATA table, how is it ensured that the coloumn will remain unique and a value inserted by this utility will not be inserted again but SOA??

    ReplyDelete
  2. There is a column named ROW_NUMBER in XREF_DATA table, how is it ensured that the column value will remain unique and a value inserted by this utility will not be inserted again by SOA??

    ReplyDelete
  3. The ROW_NUMBER is generated based on the approach followed in the 10g XREF utility.
    The id is a Universal Unique id, so i don't think there will be any issue.

    Regards
    Albin I

    ReplyDelete