Using Polling Statement and Executing Custom SQL using BizTalk Oracle Adapter

The BizTalk Oracle adapter can be used to poll a table after specific intervals. The oracle adapter transport properties must be set to poll the specific table. Username, password and Service Name (The TNS alias used in the TNS file) should be configured along with the Bin Path of the Oracle Client installed. You can check whether all the properties are configured properly by clicking the ellipsis in the managing events property in the Oracle transport property. Select the SQLNative as receive property.

This type of schema should be generated in the project by adding the oracle adapter metadata as shown in the section. A message of Type NativeSQL schema is returned after the execution of the polling statement query containing the data. Optionally a post polling statement query can be set that will run before the polling statement.

In the orchestration, we have to create the type of Schema NativeSQL from the Oracle Adapter metadata.

Caution: It is better to keep the Oracle Schemas project separate because if another project uses the same service with NativeSQL schema, a routing failure will occur.

When you create the metadata from Visual Studio, multi-part messages and port types will be created along with the orchestration by selecting NativeSQL service. If you are using a separate project for Oracle adapter metadata and your working project, configure your port and multi-part messages accordingly.

In this working example, create a Request Message of type SQLEvent by configuring the message type property and referencing the message part from the Oracle Schemas project assembly. The response message returned will be of type “SQLEventResponse”.

Create a new receive only port and connect your receive shape of the request message you configured in the Orchestration.

The response message however is not normalized according to your needs and contains two records “ColumnMetaData” and “RowMetaData”. The Column meta data contains names of the columns and rowdata contains data but with no expected column name as XML tag. Therefore you need an intelligent map to normalize it to make it more useful. I have seen a lot of tutorials on the internet and have never come across a single post or article that explains what this map will look like. I tried it with a table looping funcoid and indexing functoid but it won’t work.

At the end, we are left with a simple choice of using XSLT for normalizing our response to our desired schema. Below is a simple XSLT inline script that maps the result from SQLEventResponse message to our IFX based schema message. We have to use a scripting functoid and use Inline XSLT Call template script. The script is below which is self explanatory. The for-each loops that iterate over the rows and we map it onto our destination schema record.

//Name of the template
<xsl:template>

//For each loop that iterates over "Column data" which actually makes a record row
<xsl:for-each select="/*[local-name()='SQLExecuteResponse' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' 
and namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' 
and namespace-uri()='http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘]">

//Use a variable for indexing a record
<xsl:variable select=’position()’ />

//Log_Reference is a column in the destination schema
//The text highlighted in the script is the $index variable which returns the 
//row being iterated and the column order is known so I hard coded it, 
//alternatively you can use another foreach loop and index for columns as well

  <LOG_REFERENCE><xsl:value-of select="/*[local-name()='SQLExecuteResponse' 
and namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='Return' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='rowData' and 
namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘]/*[local-name()='columnData' 
and namespace-uri()='http://schemas.microsoft.com/
[OracleDb://OLTPDEV/NativeSQL]‘ and position() = 
$index]/*[local-name()='string' and namespace-uri()=
'http://schemas.microsoft.com/[OracleDb://OLTPDEV/NativeSQL]‘ 
and position()=1]" /></LOG_REFERENCE>

In this way, the result is a normalized message other than which is returned from the Oracle Adapter.

Alternatively NativeSQL schema is used when we have a custom query that cross-references different tables and Oracle Adapter Metadata cannot be generated for such type of queries. You can create a message of type “SQLEvent” which will contain your custom SQL Query that can be a string created dynamically in your orchestration. To generate the XML, change the Root reference property of your NativeSQL schema from Default to SQLEvent. Right click the schema and click generate instance. In the message assignment shape load the XML through the XMLDocument type variable in your orchestration and assign the XMLDocument variable to SQLEvent Type message variable.

The response will be of type SQLEventResponse which can be normalized to the procedure explained above.