Inserting a record in Oracle using biztalk 2010 issue

3
votes

I am trying to follow this blog to insert a record into an oracle table using BizTalk 2010 http://biztalk2010changes.blogspot.co.nz/2011/04/insert-update-delete-select-operation.html No orchestration was created I only created a WCF-custom generated item using OracleDBBinding contract type client/(outbound operation).

I selected the table and picked Insert category. An xsd was generated with the following entries

  • xxRECORDINSERT,
  • arrayOfxxxRECORDINSERT,
  • Insert,
  • InsertResponse.

I generated an instance of the above schema, my goal is using that instance as a file with content to be inserted into my oracle table. I deployed the application successfully, set up FilePort when BizTalk will pick up the file and the oracle port based on the bindings created by BizTalk (the steps I followed as similar to the link I provided above. I have also set up the filter for the application to pickup the file and the message

enter image description here

However, when the file was drop into the directory, it was indeed picked up but I got this routing error:

enter image description here

enter image description here

and the I got this error msg log:

WcfSendPort_OracleDBBinding_ORASCHEMA_Table_ROTATION_REQ_Custom oracledb://oracleServer/?PollingId=TEST_00042 Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Unexpected start node "ROTATION_REQRECORDINSERT" with namespace "http://Microsoft.LobServices.OracleDB/2007/03/ORASCHEMA/Table/ROTATION_REQ" found.

This is an extract of my schema:

<?xml version="1.0" encoding="utf-16" ?> 
- <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:tns="http://Microsoft.LobServices.OracleDB/2007/03/ORASCHEMA/Table/ROTATION_REQ" elementFormDefault="qualified" targetNamespace="http://Microsoft.LobServices.OracleDB/2007/03/ORASCHEMA/Table/ROTATION_REQ" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema">

and this is an instance of my sample I tried to insert into my table:

<ns0:ROTATION_REQRECORDINSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/ORASCHEMA/Table/ROTATION_REQ">
  <ns0:RotationID InlineValue="InlineValue_0">RotationID_0</ns0:RotationID>
  <ns0:Year InlineValue="InlineValue_0">2015</ns0:Year>
  <ns0:Class InlineValue="2015">T4</ns0:Class>
  <ns0:Rotation InlineValue="InlineValue_0">Rotation_0</ns0:Rotation>
  <ns0:From InlineValue="InlineValue_0">1999-05-31T13:20:00.000-05:00</ns0:From>
  <ns0:To InlineValue="InlineValue_0">1999-05-31T13:20:00.000-05:00</ns0:To>
  <ns0:NumberOfConsecutiveSession InlineValue="InlineValue_0">500</ns0:NumberOfConsecutiveSession>
  <ns0:AmOrPM InlineValue="InlineValue_0">3</ns0:AmOrPM>
  <ns0:DayOfWeek InlineValue="InlineValue_0">6</ns0:DayOfWeek>
</ns0:ROTATION_REQRECORDINSERT>

Anything I did was incorrect?

Update: The context of the message:

enter image description here

2
oraclebiztalkbiztalk-2010

2 Answers

1
votes

After a while banging my head against the wall, I finally managed to figure out what I need to do to make it works. The post was accurate at what it described but it was what not there that confused me and caused issue for me.

I updated this for myself or anyone else that might run into my same scenario in the future.

These are the things I did incorrectly for lack of instructions:

1) I used xxRECORDINSERT to create my message which is not correct. I should have used Insert instead. It was actually there in the sample but I have overlooked it and have let BizTalk auto generate it. The insert should have been moved to the beginning of the file so that BizTalk could generate the message correctly. That was probably the reason why BizTalk was complaining about the "unexpected start node".

2) I need to create another port to catch the response from BizTalk after the record was inserted otherwise it will cause the routing error I had experienced earlier.

These are very fundamental errors that I have overlooked. Hope it will save someone else time should they come across the same issue in the future.

0
votes

To resolve routing errors you need to look at the context properties of the message and compare it to the active subscription and see what doesn't match. You can see subscription via the BizTalk Server Administration Console, BizTalk Group, New Query, Search For Equals Subscriptions.