Figure 1. Diagram of the scenario
Connecting to the LOB system has failed.
Could not load file or assembly 'Oracle.DataAccess, Version=x.xxx.x.x, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxx' or one of its dependencies. The system cannot find the file specified.
To implement the scenario the following tasks are required to create a message based solution for table operations on Oracle database (11g XE):
Figure 2. Consume Adapter Service Template (click to enlarge).
Figure 3. Configure URI, the connection string and other settings (click to enlarge).
Figure 4. Browse Oracle Objects.
Figure 5. Add Table operations.
Figure 6. BizTalk Project layout.
Next step is to generate the messages from generated schemas, which can be routed from Receive location in Receive Port via BizTalk to a send port with the WCF-Custom Adapter using the OracleDb binding (see the diagram in the introduction). Generating the messages is easy, by opening first OracleDBBindingHR.Table.EMPLOYEES.xsd in the file-editor so you can move the desired operation to the top. You can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for all the table operations, save to file as xml (type all files, encoding UTF-8!).
Select
<
ns0:Select
xmlns:ns0="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
ns0:COLUMN_NAMES
>COLUMN_NAMES_0</
>
ns0:FILTER
>FILTER_0</
</
Update
ns0:Update
ns0:RECORDSET
ns0:EMPLOYEE_ID
>70</
ns0:FIRST_NAME
>FIRST_NAMEFIRST_NAME</
ns0:LAST_NAME
>LAST_NAMELAST_NAMELAST_NA</
ns0:EMAIL
>EMAILEMAILEMAILEMAILEMAIL</
ns0:PHONE_NUMBER
>PHONE_NUMBERPHONE_NU</
ns0:HIRE_DATE
>1999-05-31T13:20:00.000-05:00</
ns0:JOB_ID
>JOB_IDJOB_</
ns0:SALARY
>4</
ns0:COMMISSION_PCT
>32</
ns0:MANAGER_ID
>2</
ns0:DEPARTMENT_ID
>81</
Insert
ns0:Insert
ns0:EMPLOYEESRECORDINSERT
InlineValue
=
"InlineValue_0"
>10.4</
>FIRST_NAME_0</
>LAST_NAME_0</
>EMAIL_0</
>PHONE_NUMBER_0</
>JOB_ID_0</
ns0:QUERY
>QUERY_0</
Delete
ns0:Delete
Figure 7. Send Port Configuration (click to enlarge).
The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HR_Table_EMPLOYEES_Custom" with URL "oracledb://win-8bpntqktj5m:1521/XE/Dedicated". It will be retransmitted after the retry interval specified for this Send
Port. Details:"System.InvalidOperationException: An action mapping was defined but BTS.Operation was not found in the message context.
Th reason that this error appears is that an action header consisting of four actions is not clear to the BizTalk system. It cannot determine which action is the appropriate action. So by taking it down to a one line/action declaration corrects the problem. You will need to create three similar send ports based on first generated send port to support all the operations. The next step is setting the filters on WCF-Custom Send Ports and the File Send Port. The configuration is as follows:
The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_HR_Table_EMPLOYEES_Custom" with URL "oracledb://win-8bpntqktj5m:1521/XE/Dedicated". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-00936: missing expression ---> Oracle.DataAccess.Client.OracleException: ORA-00936: missing expression
xmlns="http://microsoft.lobservices.oracledb/2007/03/HR/Table/EMPLOYEES%22>
COLUMN_NAMES
>*</
FILTER
>where LAST_NAME='King'</
Figure 8. Select Operation Message Instance (click to enlarge).
Read suggested related topics:
Steef-Jan Wiggers edited Revision 4. Comment: Added text
Steef-Jan Wiggers edited Revision 3. Comment: Added resource link
Steef-Jan Wiggers edited Revision 2. Comment: Added text and picture
Steef-Jan Wiggers edited Revision 1. Comment: Added text
Steef-Jan Wiggers edited Original. Comment: Added resource links
Sandro Pereira edited Revision 33. Comment: Change Title
Steef-Jan Wiggers edited Revision 36. Comment: Added text to image
Steef-Jan Wiggers edited Revision 37. Comment: Added text to image,
Steef-Jan Wiggers edited Revision 38. Comment: Added text image and formatting
Steef-Jan Wiggers edited Revision 39. Comment: Added text to image, formatting