In a recent project we were faced with a problem of De-Queueing messages from PeopleSoft Advance Queue using BizTalk.

BizTalk LOB adapters pack have both a “Microsoft BizTalk Adapter for Oracle Database” and a “Microsoft BizTalk Adapter for Oracle E-Business Suite,” but none of these options will let you consume Oracle Advance Queue directly, rather you can use these adapters to connect to Oracle. You can use 3rd party adapters to consume AQ but here is a simple approach.

BizTalk-1

As using these adapters allows you to call the Store Procedure (SP) in Oracle, a simple approach is to let the SP do the De-Queuing, and consume messages in BizTalk returned by the SP. Here is some sample code:

BizTalk-2

This sample SP “PROC_DQUEUE” will De-Queue messages from “Your_Q” and will return them as XML.

Once you have your SP ready you can use Add Generated Items from Visual Studio to generate schema and binding.

BizTalk-3

Use “Consume Adapter Service”

BizTalk-4

And Click “Add”.

Select “OracleESBBinding” and provide Oracle Server URI for example “oracleebs://DataSource”, click on configure to provide user credentials.

BizTalk-5

Click on “Connect”

BizTalk-6

Browse to your SP and click “Add”, click “OK”. That will generate required schemas and binding into your current BizTalk Project.

You can get fancier and batch multiple messages in Store Procedure and then De-Batch them in BizTalk.

OracleAQ Orchestration was triggered by receiving XML file which contains a node declaring how many messages to De-Queue. The advantage of this approach is that either the schedule for calling SP or number of messages in batch can be changed on the fly, which require no code changes in BizTalk or configuration changes in BizTalk adapter. All batched messages were De-Batched and dropped in MsgBox for consumption for anyone who has subscription for that message.