Creating Oracle Adapter Metadata from Visual Studio in BizTalk 2006 – Points to consider in Enterprise


1-     Build a separate project for Oracle adapter metadata which gives auto-generated schema types and port types because if any one else in the enterprise uses the same table it will be generated with the default target namespace “http://schemas.microsoft.com/%5BOracleDb://{ServiceName}/{TableName}]#{Operation}” and if both the projects gets deployed on the same server you will get a routing failure. For e.g. the mostly used schema is the NativeSQL for generic SQL statements and of course used for polling statement results.

“There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “XML disassembler” Receive Port: “ReceivePortOracleDemo” URI: “OracleDb://OLTPDEV_6d833f94-9fb8-423e-be29-bc7a75884bc0” Reason: Cannot locate document specification because multiple schemas matched the message type “http://schemas.microsoft.com/%5BOracleDb://OLTPDEV/NativeSQL%5D#SQLEvent”.  

2-     When generating metadata first create a Static Solicit-Response send port in the default BizTalk project which is BizTalk Application 1 in the Management Console. Create one port for each of your service in Oracle. This send port will only be used for generating schemas as when generating schemas in the following dialogue. To retrieve Oracle adapter metadata perform the following steps.

  • Right click your project, go to Add and then select Add Generated Items.
  • In Categories select Add Adapter Metadata
  • In templates double Click Add adapter Metadata

You will get the following dialogue.

oracle_adapter_metadata 

 Select the Oracle Database adapter and then select the port you created in the BizTalk Application 1 project. Select your Table or Native SQL type and you will get Orchestration File with Type Orchestration_1, Port Types with Operations of Select, Insert, Remove and Update. Also multi-part messages will be created for all the operations request and responses. All the multi-part message parameters message part will be of the type of the schema generated.

 3- There are a little catches and you will face problems when generating metadata, the first thing is every time an orchestration file is generated the Orchestration Type is Orchestration_1 in the same project which should not be the case however the filename is Orchestration_(Index+1). The second thing is all the generated Multi-part messages will be of the same name Query, QueryResponse etc. So If you are adding metadata for two tables you will run into troubles with conflicting message part types as they will have same name and same namespace.

 

The work around for this is before you create a metadata for the adapter change the default namespace of your schema project to lets say MyEnterprise.Oracle.Schemas.[Tablename]. In this way the multi-part message types created will be of different namespaces and of course the Oracle Port created will point to the same message types and there will be no conflict. Then change the name of your multi-part messages with a prefix of your tablename so that the referencing project can identify the message names.

 Oracle_adapter_MessageTypes2

 4- At the end the open the Orchestration file created, change its type from Orchestration_1 to TableName type. As you will be using this project for schema types don’t forget to change the access modifier properties of Ports and multi-part messages from Internal to Public.


technorati tags :

Executing Stored Procedure using BizTalk Oracle Adapter


Stored procedures from the Oracle adapter can only be called when they are within a package, this is due to the Oracle driver that does not provide the list of parameters. Visit Microsoft MSDN to read more about Oracle adapter calling the stored procedure.

Limitations of Oracle adapter calling a stored procedure wrapped in a package

For procedures, large-object (LOB) types are supported as IN parameters only. When they appear as INOUT, RETURN and OUT, the procedure is not displayed in the browser and therefore is not callable. LOBs are supported in tables in the Insert, Update, or Query methods.

In this article we will assume the package containing the procedure as stored procedures. In order to call a stored procedure wrapped up in a package generate the metadata of the stored procedure as shown below.

metadata

 
The schema containing all the types of the package, port type and multi-part message will be generated. In order to construct the request message select the root-reference property of the schema generated to the desired Request Message. Construct the message in a message assignment shape by loading the xml into an XMLDocument variable. The oracle adapter creates request, response and exception type messages and schema. Create a new solicit request-response port in the orchestration and select the exiting port created in the orchestration as below.

 Oracle_SP_port


technorati tags :

How to Select, Insert, Update and Delete in BizTalk using Oracle Adapter


Select, Insert, Update and Delete statements can be executed using the BizTalk Oracle adapter by generating the Oracle Adapter metadata for a specific table. Right click the solution and click add generated items and click “Add adapter metadata”. A schema along with multi-part message types and port will be created along with an Orchestration file. For creating Oracle adapter metadata see Generating MetaData From Oracle Adapter.

Multi-part messages for Selecting (Query, QueryResponse), Update(Update, UpdateResponse), Insert(Insert, InsertResponse) and Delete(Remove, RemoveResponse) will be created with a message part named parameter for each message.

You can generate requests for each Request messages by changing the Root Reference property of the schema to the request message you want. Generate the instance and load the xml into the XMLDocument type variable with the SQL Statement in the message.

Bulk Insertion is an advantage using Oracle adapter and if you have several records to be inserted or updated you can create their messages and send it to the oracle adapter. In the response message the rows affected is returned.

For Select, Update and Delete you have to fill the Filter tag. For filters we supply the where clause just as in any other SQL Statement. For e.g. If we want to select particular records we give “columnname1 = value and columnname2 = value”.

  • Select and Remove Type multi-part message only requires the filter parameter to be filled.

 Oracle_select_filter

  • For Update we supply the values of the columns that have to be changed in the request message along with the filter parameter.

 Oracle_Update_filter

 

  • Insert type messages only require records to be inserted without filters.

 Oracle_Insert
Create a new port with solicit request-response type. Select the existing port type which was auto-generated with the metadata created.

 Oracle_Port_type


technorati tags :
%d bloggers like this: