Using BizTalk Oracle Adapter - NativeSQL

Steps to run a sample scenario which retrieves data from the Oracle DB

1. System DSN - ODBC - keep the name same as the name of the database, else the adapter will not recognize the Database

2. Create a Physical Port using the BizTalk Administration Console.
   a. Select the type of the port as Oracle Database.
   b. Click configure and provide data as mentioned in steps below
   c. Provide the Oracle password
   d. In Path - provide the path to the BIN directory of your Oracle Home.
   e. In Service Name specify the name of the DSN - which is the name of the database
   f. Provide the user name, and click OK.
   g. Select the Send Pipeline as XML Transmit.
   h. Select the Receive Pipeline as XML Receive.

3. In the project where you need to create an orchestration, right click and select add generated items.

4. Select Add Adapter MetaData and click Add

5. Select Oracle(r) Database,
   a. select the SQLServer instance where BizTalk is installed
   b. Select the BizTalkMgmtDb
   c. In Port select the port created in Step 2.
   d. Click Next   

6. In Select the set of Services to be added to the database, click on OracleDb, then click on the DSN name (created in Step 1) and select appropriate table(s) or Native SQL. For out example we would be using the NativeSQL.

7. Click on Finish, the wizard will generate 3 schema and 1 orchestration file.
   a. The schema files generated includes the following:
        1. NativeSQLService_1.xsd
        2. NativeSQLService_NativeSQL_x5d_1.xsd
        3. NativeSQLService_obj_1.xsd

   b. An empty Orchestration named BizTalk Orchestration.odx is generated.

8. Open the NativeSQLService_NativeSQL_x5d_1.xsd file and and you will see the following nodes:
   a. SQLExecute
   b. SQLExecuteResponse
   c. SQLEvent
   d. SQLEventResponse

9. We would be focusing on the SQLExecute and SQLExecuteResponse. SQLExecute has StatementText and ParameterSet. In this exercise we will only use the StatementText. To use the StatementText, make it a distinguish field so that it can be accessed and set in Orchestration.

10. Now Open the Orchestration, created. Define 2 messages one corresponding to SQLExecute type - say "MessageOracleSQL" and other SQLExecuteResponse type - say "MessageOracleSQLResponse".

11. Configure a new port - in wizard select Use Existing port and select BizTalk_Server_Oracle_Adapter.NativeSQL.

12. Select Port direction as "I will be sending a Request and receiving a response", and port binding as specify later.

13. In the Orchestration now add a Construct Message Shape and specify the output as "MessageOracleSQL".

14. Add a new Transform Shape which takes some input message and outputs MessageOracleSQL.parameters, this step is required to initialize the message.

15. Add a new MessageAssignment Shape and set the StatementText property of the "MessageOracleSQL" to the Oracle SQL Statement to be executed. "MessageOracleSQL.parameters.StatementText = {Oracle SQL Statement}", note since the SQLExecuteResponse is a generic data structure, to return the data from the Oracle generate a statement that returns the data as XML in One Column.

15. Add a Send Shape and select Message as "MessageOracleSQL", and connect it to the SQL Execute - Request of the port configured in step 11/12.

16. Add a Receive Shape, whic connects with the Response of SQL Execute Port, with the Message as SQLOracleResponse.

17. Now in order to retrive the data from the Message, define a Transformation which takes the input as SQLExecuteResponse schema and generates the output as required. Here you would be required to use the Scripting Functiod to break the XML from the columndata into the required message.