Database Xpath-extension Functions in SOA 12c

 In SOA, we can connect with databases using a database jca adapter and configuring the data source and connection factory in the WebLogic server. Sometimes, we may want to query the database directly within the BPEL component or XSLT without configuring a database jca adapter. For such use cases, we can take leverage of database functions that can be used within BPEL Assign Activity or XSLT transformations.

We have 3 different database functions available, that can be used in different use cases.

  1. oraeext:sequence-next-val()
  2. oraext:lookup-table()
  3. oraext:query-database()

1. oraext:sequence-next-val()

Syntax:

oraext:sequence-next-val(sequence as string, dataSource as string)
sequence: Specify the sequence available in the database for which the next value needs to be extracted.
dataSource: Specify the Data source JNDI name as configured in WebLogic server or JDBC string in the format jdbc:oracle:thin:username/password@host:port:sid. 
Note: Only oracle thin driver is supported if used as JDBC string.

This function is used when we want to fetch the next value of defined sequence in the database. It can be used in cases where we want each request to adhere with a particular sequence id which can be used to identify each unique record where using a db adapter might create overhead and extra transformation. This function is basically an alternative to the below SQL statement.

SELECT SEQUENCE_TEST.NEXTVAL FROM DUAL;

Here, SEQUENCE_TEST is the name of the sequence defined in the database.

Example usage in XSLT:

XSL:
<xsl:template match="/">
    <root>
      <seqJDBCString><xsl:value-of select="oraext:sequence-next-val('SEQUENCE_TEST','jdbc:oracle:thin:mark1/password@localhost:1521:XE' )"/></seqJDBCString>
    </root>
  </xsl:template>
OUTPUT:
<root>
   <seqJDBCString>2</seqJDBCString>
</root>

2. oraext:lookup-table()

Syntax

oraext:lookup-table(tableName as string, inputColumn as string, inputData as string, outputColumn as string, dataSource as string)

tableName: Name of the table in database, from which value need to be fetched.
inputColumn: Column name of the table for which we will supply the data.
inputData: Data that will be provided as input to match a value in inputColumn in the table.
outputColumn: Column name of table for which value needs to fetched.
dataSource: Specify the Data source JNDI name as configured in WebLogic server or JDBC string in the format jdbc:oracle:thin:username/password@host:port:sid. 
Note: Only oracle thin driver is supported if used as JDBC string.

Suppose we have a table in our database which contains names of employee corresponding to the empIds. Now, we want to extract the name of employee corresponding to empId which we are getting in input. To do so, one way could be to have a db adapter created and use similar SQL statement as below to retrieve data.

SELECT EMP_NAME FROM EMPLOYEE_TABLE WHERE (EMP_ID=#inputId)

If we want to fetch the value directly in assign activity or xslt, we can use the oraext:lookup-table(). This can be treated as alternative approach to dvm lookup where in dvm files are stored local to composite or in MDS.

EMPLOYEE_TABLE

EMP_ID EMP_NAME
1002 DecipherMiddleware

INPUT:
<input xmlns="http://test.com/tns"> <empId>1002</empId>
</input>
XSL:
<xsl:template match="/">
    <root>
      <empName><xsl:value-of select="oraext:lookup-table('EMPLOYEE_TABLE','EMP_ID',/ns0:input/ns0:/empId,'EMP_NAME','jdbc:oracle:thin:mark1/password@localhost:1521:XE' )"/></empName>
    </root>
</xsl:template>
OUTPUT:
<root>
   <empName>DecipherMiddleware</empName>
</root>

3. oraext:query-database()

oraext:query-database(sqlQuery as string, rowSet as boolean, row as boolean, dataSource as string)

sqlQuery: SQL query that needs to be executed.
rowSet: true(), if we need tag for output rowSet().
row: true(), if we need tag for each row.
dataSource: Specify the Data source JNDI name as configured in WebLogic server or JDBC string in the format jdbc:oracle:thin:username/password@host:port:sid. 
Note: Only oracle thin driver is supported if used as JDBC string.

This function can be used to execute SQL statements without configuring a JCA adapter in SOA composite. Let us assume we have a category table like below, from where we need to query all columns. 

CATEGORY TABLE

CATEGORYID CATEGORYNAME DESCRIPTION
1 Beverages Soft drinks, coffees, teas, beers, and ales
2 Condiments Sweet and savory sauces, relishes, spreads, and seasonings
3 Confections Desserts, candies, and sweet breads
4 Dairy Products Cheeses
5 Grains/Cereals Breads, crackers, pasta, and cereal

SQL Query: SELECT * FROM category;
XSL:
<xsl:template match="/">
  <xsl:variable name="dbData" select="oraext:query-database('SELECT * FROM category',true(),true(),'jdbc:oracle:thin:mark1/password@localhost:1521:XE' )"/>
    <root>
      <categoryData>
      <xsl:copy-of select="$dbData"/>
      </categoryData>
    </root>
</xsl:template>
OUTPUT:
<root>
   <categoryData>
      <ROWSET>
         <ROW num="1">
            <CATEGORYID>1</CATEGORYID>
            <CATEGORYNAME>Beverages</CATEGORYNAME>
            <DESCRIPTION>Soft drinks, coffees, teas, beers, and ales</DESCRIPTION>
         </ROW>
         <ROW num="2">
            <CATEGORYID>2</CATEGORYID>
            <CATEGORYNAME>Condiments</CATEGORYNAME>
            <DESCRIPTION>Sweet and savory sauces, relishes, spreads, and seasonings</DESCRIPTION>
         </ROW>
         <ROW num="3">
            <CATEGORYID>3</CATEGORYID>
            <CATEGORYNAME>Confections</CATEGORYNAME>
            <DESCRIPTION>Desserts, candies, and sweet breads</DESCRIPTION>
         </ROW>
         <ROW num="4">
            <CATEGORYID>4</CATEGORYID>
            <CATEGORYNAME>Dairy Products</CATEGORYNAME>
            <DESCRIPTION>Cheeses</DESCRIPTION>
         </ROW>
         <ROW num="5">
            <CATEGORYID>5</CATEGORYID>
            <CATEGORYNAME>Grains/Cereals</CATEGORYNAME>
            <DESCRIPTION>Breads, crackers, pasta, and cereal</DESCRIPTION>
         </ROW>
      </ROWSET>
   </categoryData>
</root>

Since we have kept rowSet and row as true(), we are seeing tags coming in output from function result.

References:

https://docs.oracle.com/en/middleware/soa-suite/soa/12.2.1.3/develop/xpath-extension-functions.html#GUID-73F8AE17-41AC-474E-8AF3-6027CAEADB55

Please share your valuable feedback in the comments section below 😊



-

Comments

  1. Vibhav Singh21 July, 2021 18:52

    Nice post

    ReplyDelete
  2. Praveen Chauhan21 July, 2021 19:42

    great article !!

    ReplyDelete
  3. Middleware Practice23 July, 2021 10:48

    Good post..!

    ReplyDelete
  4. Tom27 July, 2022 14:50

    Is it possible to use SQL Server Database with oraext:lookup-table? Can you give an example?

    ReplyDelete
  5. chanakya17 January, 2024 12:27

    This is a great article and help. How can we avoid hardcoding the password? Appreciate your great help. Thank you!

    ReplyDelete

Post a Comment

Popular posts from this blog

DateTime formatting using xp20:format-dateTime ()

xs:dateTime Format In XML, we generally use xs:dateTime(xs:xmlns="http://www.w3.org/2001/XMLSchema") data type to store date time data. The generic representation of date time, that is supported by xs:dateTime in xml: YYYY-MM-DDThh:mm:ss[.SSS][Z|(+|-)hh:mm] Example :  2019-09-11T10:16:31.943+05:30 Code Description Example YYYY represents a 4-digit year 2019 MM represents a 2-digit month 09 DD represents 2-digit date 11 hh represents 2-digit hours 10 mm represents minutes in 2-digits 16 ss represents seconds in 2-digits 31 SSS represents milliseconds 943 T represents time separator T Z represents UTC time zone or 0 time zone offset (+|-)hh:mm represents time zone offset +5:30 Few more valid examples : 2019-09-11T21:32:52, 2019-09-11T21:32:52+02:...
Read more

Create Delimited String from XML Nodes and Vice Versa in SOA 12c

A delimited string is a string representation of data separated by a delimiter(e.g. ","). A simple representation of the delimited string will look like. Delimited String Examples Sample-1-- Delimiter as ","        Value1,Value2,Value3   Sample-2-- Delimiter as "|"        Value1|Value2|Value3 If we want to represent the above set of delimited values in the form of an XML document under some root and parent tag. The representation will look like this. XML Example <root xmlns="http://test.com/sample/xml">   <Values>     <value>Value1</value>     <value>Value2</value>     <value>Value3</value>   </Values> </root> Sometimes we may need to convert values coming in delimited string to an xml document or vice versa. Since delimited strin...
Read more

Import and Export MDS artifacts in SOA 12c

Image
Oracle SOA-MDS (Metadata Store) repository can be used to access artifacts that can be shared among various SOA composites. For example, we have one common schema for a business fault that is consumed by various applications. Instead of having a localized copy of that schema file in each SOA composite, we can have it in a centralized repository (SOA-MDS). Another benefit of using SOA-MDS is that we can change the file at runtime and need not redeploy the complete code. While developing SOA composites, we use SOA design-time repository, later these changes can be synced to the SOA servers. In this article, we will discuss 2 ways to sync the artifacts available in the SOA design-time repository and the SOA servers. Establishing the connection to SOA-MDS in Jdeveloper. Using SOA em console or Fusion Middleware control console. Moving a file between SOA composite to SOA design-time MDS repository Let's assume, we created a businessFault.xsd which wi...
Read more