Saturday, October 08, 2011

WCF-Oracle and Oracle Advanced Queing (AQ) – tips

If you use the WCF-Oracle adapter to communicate with Oracle Advanced Queing you are in for a treat. We followed the steps suggested in a very good MSDN post. There are however some caveats;

  • Username is case sensitive
  • The generated schemas have a reference to the package, and Database schema, make sure this is consistent throughout the environments
  • The generated schemas are slightly different then what the WCF-Oracle adapter expects in some cases (more on that below)
  • Use a count procedure to determine if there are messages

Generated the schemas is done by following the standard ‘Add Generated items’ wizard so that should not be hard to do. When you generate schemas for Oracle they will typically have the targetnamespace:

/Package/http://Microsoft.LobServices.OracleDB/2007/03/<DATABASESCHEMA>/Package/<PACKAGENAME>

Where the <DATABASESCHEMA> could be something like ‘DBOWNER’ and the packagename could be whatever.

When you implement this everything seems ok, until you start testing, receiving messages won’t work (not supported exception stating that the action is invalid;

image

This is because the WCF-Oracle adapter expects a different targetnamespace!!

See the namespace below with the subtile change from ‘Package’ to ‘PollingPackage’ ;

 

http://Microsoft.LobServices.OracleDB/2007/03/<DATABASESCHEMA>/PollingPackage/<PACKAGENAME>

  • I therefore strongly recommend to generate a separate schema for each receiving procedure (this allows you to alter the targetnamespace), as it works fine for non receiving procedures which can be generated into a single schema.

 

Another thing that will get in the way is that the generated schema is somehow now in lign with the message returned;

The message structure is pretty logical;

clip_image001

The generated items wizard however generates somewhat different;

clip_image002

  • Take this into account when receiving the message in my case I had to extract the message body out of an envelope so I used the WCF feature to extract this body out using XPath

/*[local-name()='DEQUEUE']/*[local-name()='DEQUEUEResult']

 

To be sure to only receive messages when there are messages on the queue, define a stored procedures that checks for messages.

The adapter can then be configured like this:

Property

Configuration

PolledDataAvailable Statement

SELECT <DATABASESCHEMA>.<PACKAGE>.<PROCEDURE> FROM DUAL

PollingAction

http://Microsoft.LobServices.OracleDB/2007/03/<DATABASESCHEMA>/PollingPackage/<PACKAGENAME>/<PROCEDURE>

PollingStatement

<ns0:DEQUEUE xmlns:ns0=/Package/">http://Microsoft.LobServices.OracleDB/2007/03/<DATABASESCHEMA>//Package/">Package/Package/">/<PROCEDURE> />

 

This should get you right on track!

 

Sander

No comments: