Skip to content

Latest commit

 

History

History
280 lines (192 loc) · 21.8 KB

run-functions-and-procedures-with-record-types-in-oracle-db-with-biztalk-server.md

File metadata and controls

280 lines (192 loc) · 21.8 KB
Raw
description title ms.custom ms.date ms.service ms.reviewer ms.suite ms.topic
Learn more about: Invoke Functions and Procedures with RECORD Types in Oracle Database using BizTalk Server
Invoke Functions and Procedures with RECORD Types in Oracle Database using BizTalk Server
06/08/2017
biztalk-server
article

Invoke Functions and Procedures with RECORD Types in Oracle Database using BizTalk Server

Oracle RECORD types are used to represent hierarchical information in parameters passed to PL/SQL functions and procedures. The [!INCLUDEadapteroracle] surfaces RECORD types as complex XML types. For more information about how the [!INCLUDEadapteroracle_short] supports RECORD Types, see Operations on Functions and Procedures with RECORD Types in Oracle Database.. For information about the XML structure for RECORD Types, see Message Schemas for RECORD Types.

How to Invoke Functions in an Oracle Database?

Performing an operation on an Oracle database using [!INCLUDEadapteroracle_short] with [!INCLUDEbtsBizTalkServerNoVersion] involves procedural tasks described in Building blocks to develop BizTalk Applications with Oracle Database. To invoke a function in an Oracle database that returns simple and nested RECORD types, these tasks are:

  1. Create a BizTalk project and generate schema for the function you want to invoke in an Oracle database.

  2. Create messages in the BizTalk project for sending and receiving messages from the Oracle database.

  3. Create an orchestration to invoke the function in the Oracle database.

  4. Build and deploy the BizTalk project.

  5. Configure the BizTalk application by creating physical send and receive ports.

  6. Start the BizTalk application.

    This topic provides instructions to perform these tasks.

Sample Based On This Topic

A sample, Func_RecordTypes, based on this topic is also provided with the [!INCLUDEadapterpacknoversion]. For more information, see Adapter Samples.

Generating Schema

In this topic, to demonstrate how the [!INCLUDEadapteroracle_short] supports invoking function that return RECORD type parameters we will invoke:

  • The GET_ACCOUNTADDRESS function that returns a simple RECORD type.

  • The GET_ACCOUNTINFO function that returns a nested RECORD type.

    These functions are available as part of the ACCOUNT_PKG created by running the SQL scripts provided with the samples. To know more about the samples and the SQL scripts, see Schema Samples.

    So, we must generate schema for the both the functions, GET_ACCOUNTADDRESS and GET_ACCOUNTINFO, available under the SCOTT\Package\ACCOUNT_PKG schema. See Retrieve metadata for Oracle operations in Visual Studio for more information about how to generate schema.

Defining Messages and Message Types

The schema that you generated earlier describes the "types" required for the messages in the orchestration. A message is typically a variable, the type for which is defined by the corresponding schema. You must link the schema you generated in the first step to the messages from the Orchestration View window of the BizTalk project.

For this topic, you must create two request-response message sets—one request-response set to invoke the GET_ACCOUNTADDRESS function and receive a response; the other request-response message set to invoke the GET_ACCOUNTINFO function and receive a response.

Perform the following steps to create messages and link them to the schema.

To create messages and link to schema

  1. Open the Orchestration View window of the BizTalk project, if it is not already open. To do so, click View, point to Other Windows, and then click Orchestration View.

  2. In Orchestration View, right-click Messages, and then click New Message.

  3. Right-click the newly created message, and then select Properties Window.

  4. In the Properties pane for Message_1, do the following:

    Use this To do this
    Identifier Type Request.
    Message Type From the drop-down list, expand Schemas, and select Func_RecordTypes.OracleDBBindingSchema.GET_ACCOUNTINFO, where Func_RecordTypes is the name of your BizTalk project. OracleDBBindingSchema is the schema generated for the GET_ACCOUNTADDRESS function.

  5. Repeat the previous step to create three more messages. In the Properties pane for the new messages, do the following:

    Set Identifier to Set Message Type to
    Response Func_RecordTypes.OracleDBBindingSchema.GET_ACCOUNTINFOResponse
    Request2 Func_RecordTypes.OracleDBBindingSchema.GET_ACCOUNTADDRESS
    Response2 Func_RecordTypes.OracleDBBindingSchema.GET_ACCOUNTADDRESSResponse

Setting up the Orchestration

You must create a BizTalk orchestration to use [!INCLUDEbtsBizTalkServerNoVersion] for invoking a function returns simple and complex RECORD types. In this orchestration, you drop two request messages:

  • One for the GET_ACCOUNTADDRESS function that returns a simple RECORD type.

  • One for the GET_ACCOUNTINFO function that returns a nested RECORD type.

    These messages are dropped at a receive location. The [!INCLUDEadapteroracle_short] consumes the messages and passes them on to the Oracle database via ODP. The response from the Oracle database is saved to another location. Because the orchestration picks two requests simultaneously, you need to include a Parallel Actions shape in the orchestration. For each parallel action, you must include Send and Receive shapes to send messages to Oracle database and receive responses, respectively. However, you could use the same ports for sending and receiving messages for both operations. A typical orchestration for performing both operations simultaneously would contain:

  • Send and Receive shapes to send messages to Oracle database and receive responses.

  • A one-way receive port to receive request messages to send to the Oracle database.

  • A two-way send port to send request messages to Oracle database and receive responses.

  • A one-way send port to send the responses from Oracle database to a folder.

    A sample orchestration resembles the following:

    Orchestration for using Record Types in Oracle

Adding Message Shapes

Make sure you specify the following properties for each of the message shapes. The names listed in the Shape column are the names of the message shapes as displayed in the just-mentioned orchestration. The following table lists the shapes you must include for one of the parallel actions.

Shape Shape Type Properties
ReceiveMessage Receive - Set Name to ReceiveMessage
- Set Activate to True
SendMessage Send - Set Name to SendMessage
ReceiveResponse Receive - Set Name to ReceiveResponse
- Set Activate to False
SendResponse Send - Set Name to SendResponse

The following table lists the shapes that you must include for the other parallel action.

Shape Shape Type Properties
ReceiveMessage2 Receive - Set Name to ReceiveMessage2
- Set Activate to True
SendMessage2 Send - Set Name to SendMessage2
ReceiveResponse2 Receive - Set Name to ReceiveResponse2
- Set Activate to False
SendResponse2 Send - Set Name to SendResponse2

Adding Ports

Make sure you specify the following properties for each of the logical ports. The names listed in the Port column are the names of the ports as displayed in the orchestration.

Port Properties
FileIn - Set Identifier to FileIn
- Set Type to FileInType
- Set Communication Pattern to One-Way
- Set Communication Direction to Receive
LOBPort - Set Identifier to LOBPort
- Set Type to LOBPortType
- Set Communication Pattern to Request-Response
- Set Communication Direction to Send-Receive
SaveResponse - Set Identifier to SaveResponse
- Set Type to SaveResponseType
- Set Communication Pattern to One-Way
- Set Communication Direction to Send

Because you will be processing two request and response messages using these ports, you must create two operations for each port, where each operation corresponds to one message type. To create an operation, right-click the port shape and select New Operation. Name the first operation for each port as NestedRecType and the second operation for each port as SimpleRecType.

Using Correlations

Correlation is the process of matching an incoming message with the appropriate instance of an orchestration. In the orchestration you will be dropping two request messages, one for each overload. Using correlation, you associate a request message with the right orchestration. For more information about correlation, see Using Correlations in Orchestrations

To use correlations

  1. Promote a property from the schema generated for each function. For example, promote the CUSTNAME property from the schema of GET_ACCOUNTADDRESS function; promote the AID property from the schema of GET_ACCOUNTINFO function. To promote a property, right-click the property in the schema view, point to Promote, and then select Quick Promotion. This adds a PropertySchema.xsd file to your BizTalk project.

    For information about promoting a property, see Promoting Properties.

  2. From Orchestration View, right-click Correlation Types, and then select New Correlation Type.

  3. The Correlation Properties dialog box lists the properties you promoted in step 1. Select a property, and then click Add.

  4. Click OK.

  5. To create correlation types for the other promoted property, repeat these steps.

  6. Rename the correlation types based on the property to which they are associated. You could rename the correlation types to CorrelationType_CUSTNAME (for CUSTNAME property) and CorrelationType_AID (for AID property).

  7. From Orchestration View, right-click Correlation Sets, and then select New Correlation Set.

  8. Right-click the newly added correlation set, and then click Properties. In the Properties pane, do the following:

    Use this To do this
    Correlation Type Func_RecordTypes.CorrelationType_CUSTNAME
    Identifier Correlation_CUSTNAME

  9. Add another correlation set and specify the following properties from the Properties pane.

    Use this To do this
    Correlation Type Func_RecordTypes.CorrelationType_AID
    Identifier Correlation_AID

Specify Messages for Action Shapes, and Connect Them to Ports

The following table specifies the properties and their values that you should set to specify messages for action shapes and to link them to the ports. The names listed in the Shape column are the names of the message shapes as displayed in the orchestration mentioned earlier.

Shape Properties
ReceiveMessage - Set Initializing Correlation Sets to Correlation_AID
- Set Message to Request
- Set Operation to FileIn.NestedRecType.Request
SendMessage - Set Message to Request
- Set Operation to LOBPort.NestedRecType.Request
ReceiveResponse - Set Message to Response
- Set Operation to LOBPort.NestedRecType.Response
SendResponse - Set Message to Response
- Set Operation to SaveResponse.NestedRecType.Request
ReceiveMessage2 - Set Initializing Correlation Sets to Correlation_CUSTNAME
- Set Message to Request2
- Set Operation to FileIn.SimpleRecType.Request
SendMessage2 - Set Message to Request2
- Set Operation to LOBPort.SimpleRecType.Request
ReceiveResponse2 - Set Message to Response2
- Set Operation to LOBPort.SimpleRecType.Response
SendResponse2 - Set Message to Response
- Set Operation to SaveResponse.SimpleRecType.Request

After you have specified these properties, the message shapes and ports are connected and your orchestration is complete.

You must now build the BizTalk solution and deploy it to a [!INCLUDEbtsBizTalkServerNoVersion]. For more information, see Building and Running Orchestrations.

Configuring the BizTalk Application

After you have deployed the BizTalk project, the orchestration you created earlier is listed under the Orchestrations pane in the BizTalk Server Administration console. You must use the BizTalk Server Administration console to configure the application. For a walkthrough, see Walkthrough: Deploying a Basic BizTalk Application.

Configuring an application involves:

  • Selecting a host for the application.

  • Mapping the ports that you created in your orchestration to physical ports in the BizTalk Server Administration console. For this orchestration you must:

  • Define a location on the hard disk and a corresponding file port where you will drop the request messages, one for each overloaded procedure. The BizTalk orchestration will consume the request messages and send it to the Oracle database.

  • Define a location on the hard disk and a corresponding file port where the BizTalk orchestration will drop the response messages, one for each function, containing the response from the Oracle database.

    • Define a physical WCF-Custom or WCF-OracleDB send port to send messages to the Oracle database. You must also specify the action in the send port. For information about how to create WCF-Custom or WCF-OracleDB ports, see Manually configure a physical port binding to the Oracle Database Adapter. Because the WCF-Custom or WCF-OracleDB send port sends and receive messages conforming to more than one schema and performs two operations, you must set dynamic action for both the operations. For more information about actions, see Configure the SOAP action for Oracle Database. For this orchestration, the action should be set as follows:

      <BtsActionMapping>  
  <Operation Name="NestedRecType" Action="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTINFO" />  
  <Operation Name="SimpleRecType" Action="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS" />  
</BtsActionMapping>

      [!NOTE] Generating the schema using the [!INCLUDEconsumeadapterservlong] also creates a binding file containing information about the ports and the actions to be set for those ports. You can import this binding file from the BizTalk Server Administration console to create send ports (for outbound calls) or receive ports (for inbound calls). For more information, see Configure a physical port binding using a port binding file to Oracle Database.

Starting the Application

You must start the BizTalk application for invoking a function in an Oracle database table. For instructions on starting a BizTalk application, see How to Start an Orchestration.

At this stage, make sure:

  • The FILE receive port to receive request messages for the orchestration is running.

  • The FILE send port to receive the response messages from the orchestration is running.

  • The WCF-Custom or WCF-OracleDB send port to send messages to the Oracle database is running.

  • The BizTalk orchestration for the operation is running.

Executing the Operation

After you run the application, you must drop two request messages (one for each function) to the FILE receive location. The schema for the request messages must conform to the schema for the functions you generated earlier. The orchestration consumes the request messages and sends them to the Oracle database. The response from the Oracle database is saved at other FILE location defined as part of the orchestration.

See Message Schemas for Functions and Procedures for more information about the request message schema for invoking functions using the [!INCLUDEadapteroracle_short].

For example, the request message to invoke the GET_ACCOUNINFO function is:

<GET_ACCOUNTINFO xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG">  
  <AID>100000</AID>  
</GET_ACCOUNTINFO>

Similarly, the request message to invoke the GET_ACCOUNTADDRESS function is:

<GET_ACCOUNTADDRESS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG">  
  <CUSTNAME>Mindy Martin</CUSTNAME>  
</GET_ACCOUNTADDRESS>

The first request message invokes the GET_ACCOUNTINFO function that returns a nested record type. The response message for invoking the GET_ACCOUNTINFO function is:

<?xml version="1.0" encoding="utf-8" ?>   
<GET_ACCOUNTINFOResponse xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG">  
  <GET_ACCOUNTINFOResult>  
    <ACCT xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTINFO">  
      <ACCTID>100000</ACCTID>   
      <NAME>Kim Ralls</NAME>   
      <BALANCE>10000</BALANCE>   
    </ACCT>  
    <ADDRESS xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTINFO">  
      <STREET>1234 Main St.</STREET>   
      <CITY>Seattle</CITY>   
      <STATE>WA</STATE>   
    </ADDRESS>  
  </GET_ACCOUNTINFOResult>  
</GET_ACCOUNTINFOResponse>

The second request message invokes the GET_ACCOUNTADDRESS function that returns a simple record type. The response message for invoking the GET_ACCOUNTADDRESS function is:

<?xml version="1.0" encoding="utf-8"?>  
<GET_ACCOUNTADDRESSResponse mlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG">  
  <GET_ACCOUNTADDRESSResult>  
    <ID xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS">100004</ID>  
    <NAME xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS">Mindy Martin</NAME>  
    <STREET xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS">6789 Cherry St.</STREET>  
    <CITY xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS">New York</CITY>  
    <STATE xmlns="http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/ACCOUNT_PKG/GET_ACCOUNTADDRESS">NY</STATE>  
  </GET_ACCOUNTADDRESSResult>  
</GET_ACCOUNTADDRESSResponse>

Possible Exceptions

For information about the exceptions you might encounter while invoking functions or procedures using [!INCLUDEbtsBizTalkServerNoVersion], see Exceptions and error handling with the Oracle Database adapter.

Best Practices

After you have deployed and configured the BizTalk project, you can export configuration settings to an XML file called the bindings file. Once you generate a bindings file, you can import the configuration settings from the file so that you do not need to create the send ports, receive ports, etc. for the same orchestration. For more information about binding files, see Reuse Oracle Database Adapter bindings.

See Also

Building Blocks to Develop BizTalk applications with Oracle Database