OSB: Use of fn-bea:execute-sql

Oracle Service Bus has a custom Oracle XQuery function  fn-bea:execute-sql() available that provides low-level database access from XPath  within Oracle Service Bus message flows.

Using the fn-bea:execute-sql function from the OSB, we can call the pure SQL query from Xpath expression.

Suppose we want to get the result directly in the pipeline stage, then we can use the fn-bea:execute-sql() as shown below.

We are gonna to explain with multiple examples:

1) Without where clause

2) Where clause with single parameter

3) Where clause with multiple parameters

1) Without where clause

Add the below expression in the Assign activity:

<USERS>{
fn-bea:execute-sql('jdbc/hrds',  
xs:QName('USER'),
'select ID, NAME from USERS')}
</USERS>

Explain the highlighted part:

1) USERS: Will be the parent element of the generated xml
2) jdbc/hrds: This is the data source created in weblogic
3) USER: Will be the child element
4) select ID, NAME from USERS: Query that you want to execute

Run the Pipeline and see the result:

This is how, we can use fn-bea:execute-sql function to execute query directly from the pipeline.

2) Where clause with single parameter

You can add parameter in the fn-bea:execute-sql function as below:


<USERS>{
fn-bea:execute-sql('jdbc/hrds', 
xs:QName('USER'),
'select ID, NAME from USERS where ID=?',
$id)}
</USERS>

Run the pipeline and see the output:

3) Where clause with multiple parameters

<USERS>{
fn-bea:execute-sql('jdbc/hrds',  
xs:QName('USER'),
'select ID, NAME from USERS where NAME=? AND ADDRESS =?',
'Ankit', 
'Delhi')}

</USERS>

 Ankit is the first parameter(Name) value and Delhi is the second parameter(Address) value. You can assign the parameter values from xpath expression as well as shown in the previous example.

That's it !!