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

Abdul 0

5 Comments

Join the conversation! Your thoughts help the community grow.

Mahesh Chand17y ago
Here are two articles on how to execute Oracle stored procedures from C#. Calling Oracle stored procedures from Microsoft.NET by John Charles Olamendy on Jun 29, 2007 This article is intended to illustrate how to illustrate how to call Oracle stored procedures and functions from Microsoft.NET through the Microsoft.NET Oracle provider and its object model residing in the namespace System.Data.OracleClient. I will cover several possible scenarios with advanced examples. How to Execute Oracle Stored Procedures Dynamically in C# by Ashish Singhal on Dec 26, 2005 In this article, I wiil show how we can store schema of stored procedures in an XML file and load and run the stored procedure from UI application using C# and Oracle.
vaasulk Kandhagatla17y ago
Hi I am using oracle adapter for calling an sp which has select statement (by using refcursor type). And I am not able to generate the schema. It simply creates two files, one for schema and one for orchestration. And this schema file which is generated , does not give any request root node or response root node. Can you please let me know the steps to generate the schema. Do i need to use any wcf adapter or etc. Vaasu
Instant Host17y ago
Hi, The SQLEvent schema generated gives the same namespace everytime it is generated for the same service name. If there is an already deplpoyed project that is using the same schema type then what should be the solution. Do remember we cannot make changes in the existing project as it is live in our enterprise. <a href="http://www.marblehost.com/how-long-does-it-take-for-my-account-to-be-setup-after-signing-up.php">Instant Host</a>
Abdul 017y ago
Hi Ravi, 1- You can use the native SQL schema if you want specific columns unfortunately all the columns are returned including the ROWID by the Oracle adapter. You can view the post on blog which explains in detail how to use the maps with its response which is the most tricky part. http://abdulrafaysbiztalk.wordpress.com/2008/07/10/using-polling-statement-and-executing-custom-sql-using-biztalk-oracle-adapter/ 2- The namespaces will continue to creep in you cant avoid that. Thats how the adapter and biztalk mechanism works. 3- Sure applying filter is easy in queries you can visit my blog which explains filters in select, update and delete queries. http://abdulrafaysbiztalk.wordpress.com/2008/07/10/using-polling-statement-and-executing-custom-sql-using-biztalk-oracle-adapter/ Hope it helps! Thanks Abdul Rafay http://abdulrafaysbiztalk.wordpress.com/
Ravi17y ago
Thanks for the tips on organizing schemas. I have configured the Oracle ODBC adapter to access a table of records, and I have a few questions. 1. I have modified the QueryResponse section of the generated schema to read only the columns I am interested in. But when I run the query, I get back all columns, including ROWID ! How can I restrict the adapter to give me what I just need? 2. Every record and column has namespace references. How can I avoid namespace from creeping in ? 3. The SQL query has some WHERE clauses as follows: WHERE DEALER_CODE='119989' and DIVISION_CODE='03' and TERMINATION_DATE <SYSDATE; How do I provide multiple filters in the "Filters" section of the Query ? Any help is sincerely appreciated ! Ravi

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/[OracleDb://{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/[OracleDb://OLTPDEV/NativeSQL]#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.

View Original Post