Create Custom Xpath function in OSB - SOA 11g

In this post, we are going to see how to create Custom Xpath function for OSB 11g. I'm going to take a example of replicating the XREF functions which are available in BPEL,but not in OSB.

The Custom Xref functions are created using a Java Code and deployed into our Server. After restarting the Server, Xref functions will be available in sbconsole. The available Xref Functions will be 

• populateXrefRow(),
• deleteXrefRow(),
• lookupXRef(),
• lookupXRef1M().

STEPS:

1.  All the Cross reference values will be stored in XREF_DATA inside SOA_Infra Schema(Or we can change the schema location, Please find the SQL below to create tables in desired schema).  So In Java code we will be connecting to the database(via DataSource) and will retrieve the corresponding values.
2. Please verify data source “jdbc/SOADataSource” is available and connecting to the schema (which has Xref_data table).
3. And For Connecting to Database we need a external Jar(ojdbc6.jar and weblogic.jar) to be Imported inside your java Project. It can be imported like, Right click on the Project ->Java Build Path ->Add Jar.
4. Implement the Java code which will contain the four below functions. (Code attached Below)
5. Once the code is compiled successfully, we need to export the Java Code as “JAR” file and Copy the Jar File into $OSB_HOME/config/xpath-functions.
6. Now Create a XML File which will contain the namespace,Class and function name,etc., Like attached (CustomXrefFunctions.xml),
7. After Placing the XML file in the Same location ($OSB_HOME/config/xpath-functions), Restart the Server. Then Xref functions will be available in the sbconsole.

SQL Statements to create XREF Tables (If you are not planning to use default schemas)

CREATE TABLE XREF_DATA

(

    XREF_TABLE_NAME VARCHAR2(2000) NOT NULL,

    XREF_COLUMN_NAME VARCHAR2(2000) NOT NULL,

    ROW_NUMBER VARCHAR2(48) NOT NULL,

    VALUE VARCHAR2(2000) NOT NULL,

    IS_DELETED VARCHAR2(1) NOT NULL,

    LAST_MODIFIED TIMESTAMP NOT NULL

);

ALTER TABLE XREF_DATA

    ADD CONSTRAINT XREF_DATA_PK PRIMARY KEY (XREF_TABLE_NAME, XREF_COLUMN_NAME, VALUE);

CREATE INDEX XREF_ROW_NUMBER ON XREF_DATA

    (ROW_NUMBER );

CREATE INDEX XREF_VALUE ON XREF_DATA

    (VALUE );

JAVA Code:

Click Here Java Code

Reference:

http://docs.oracle.com/cd/E14571_01/doc.1111/e15866/custom_xpath.htm

http://docs.oracle.com/cd/E17904_01/integration.1111/e10224/med_xrefs.htm