BizTalk Wcf-Custom OracleDBBinding – Multiple identical db objects in the same Oracle instance

Spiral binding

When integrating BizTalk with Oracle, you might come across a practise where the dba has created database objects, such as a PL/SQL package, under different Oracle database schemas (i.e. user credentials) but all under the same oracle instance.


This is a way for them to save on Oracle servers, having only one instance, and simulating different environments e.g. dev, integration, test, by using different db schemas. Fair enough.


On the BizTalk side, this means that when you generate your BizTalk schema under one user credentials on a particular Oracle server instance, your will get a different schema if you use another user credentials on that same Oracle server instance.


Example: Oracle instance A, db schema ERPHR

Oracle Schema


And Oracle instance A, db schema ERPHR_DEV

Oracle Schema2


This means that for a same end-to-end interface, the data contract or signature of the Oracle package you are interfacing with, will change based on the user credentials you are using i.e. BizTalk binding.


Note : the flag ‘useSchemaInNameSpace = false’ that you can set in the schema generation settings will only remove the schema name for the outbound schema i.e. the one you send to Oracle in solicit-response endpoint. But the inbound response BizTalk schema will always have the db schema name in the targetnamespace. So you will have to deal with multiple targetnamespaces anyway.


Note that the Action will also change and contain the db schema name.

In order to deal with this situation you have no other options that dealing with tenth of schemas instead of 2 or 3 for a standard PL/SQL package.


What your BizTalk solution will need to provide:

-        Bindings per environment that changes not only the oracle instance, user credentials but also the Action corresponding to the Operation.

-        A way of dealing with multiple versions of the same schema coming through from Oracle. Two options here:

  • Generate as many version of the schema as db schema you can encounter in the different Oracle environments.
  • Generate only one version of the BizTalk schema and use a custom pipeline component to replace the targetnamespace with the generic one in the Decode shape before disassembling.
Written by Vincent Rouet at 00:00

Categories :



Comments closed