none
How do I call an Oracle Stored Procedure W/O an orchestration? RRS feed

  • Question

  • I have been bending my pick on this for a while now. And I am wondering if it is even possible. I want to receive a message which contains three values which I will use in a subsequent call to a stored procedure. For modeling purposes I am putting the values in a text file and consuming that in a receive port (using a custom pipeline with a file disassembler). The schema for the stored procedure was generated by using the wizard and consuming an adapter service (type of oracledb, etc.). The final component is a map between the flat file schema and the schema for the stored procedure.

    I created a receive port for the flat file and bound it to the custom pipeline. I imported the bindings from the stored procedure and created a send port. I set the filter to the receive port and add the map in the inbound maps of the send port.

    When a flat file is consumed the send port complains (as if it doesn't even see the map):

    I hope someone can spot something simple I have missed or let me know if what I am attempting is not possible outside an orchestration. It just seems, in theory, that it should be doable.

     

    Thanks

    Log Name:      Application
    Source:        BizTalk Server
    Date:          1/14/2011 4:15:14 PM
    Event ID:      5754
    Task Category: BizTalk Server
    Level:         Error
    Keywords:      Classic
    User:          N/A
    Computer:      WIN-821H1CGTT80
    Description:
    A message sent to adapter "WCF-Custom" on send port "WcfSendPort_OracleDBBinding_S2KDBA_Procedure_Custom" with URI "oracledb://pdev/" is suspended.
     Error details: Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Unexpected start node "Root" with namespace "http://OracleProcWFC.FlatFileSchema1" found.

    Server stack trace:
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)
     MessageId:  {3E0270FA-4583-47DF-B277-E9CC5C3A94AD}
     InstanceID: {A4A6AD29-53CD-4E3E-A058-063C0360836D}
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="BizTalk Server" />
        <EventID Qualifiers="49344">5754</EventID>
        <Level>2</Level>
        <Task>1</Task>
        <Keywords>0x80000000000000</Keywords>
        <TimeCreated SystemTime="2011-01-15T00:15:14.000000000Z" />
        <EventRecordID>4903</EventRecordID>
        <Channel>Application</Channel>
        <Computer>WIN-821H1CGTT80</Computer>
        <Security />
      </System>
      <EventData>
        <Data>{3E0270FA-4583-47DF-B277-E9CC5C3A94AD}</Data>
        <Data>{A4A6AD29-53CD-4E3E-A058-063C0360836D}</Data>
        <Data>WCF-Custom</Data>
        <Data>oracledb://pdev/</Data>
        <Data>WcfSendPort_OracleDBBinding_S2KDBA_Procedure_Custom</Data>
        <Data>Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: Unexpected start node "Root" with namespace "http://OracleProcWFC.FlatFileSchema1" found.

    Server stack trace:
       at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
       at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData&amp; msgData, Int32 type)
       at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
       at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)</Data>
      </EventData>
    </Event>

     

    The flat file schema:

      <?xml version="1.0" encoding="utf-16" ?>
    - <xs:schema xmlns="http://OracleProcWFC.FlatFileSchema1" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://OracleProcWFC.FlatFileSchema1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    - <xs:annotation>
    - <xs:appinfo>
      <b:schemaInfo standard="Flat File" root_reference="Root" default_pad_char="" pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" />
      <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
      </xs:appinfo>
      </xs:annotation>
    - <xs:element name="Root">
    - <xs:annotation>
    - <xs:appinfo>
      <b:recordInfo structure="delimited" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" sequence_number="1" child_delimiter_type="hex" child_delimiter="0x0D 0x0A" />
      </xs:appinfo>
      </xs:annotation>
    - <xs:complexType>
    - <xs:sequence>
    - <xs:annotation>
    - <xs:appinfo>
      <b:groupInfo sequence_number="0" />
      </xs:appinfo>
      </xs:annotation>
    - <xs:element name="SalesZone" type="xs:string">
    - <xs:annotation>
    - <xs:appinfo>
      <b:fieldInfo sequence_number="1" justification="left" />
      </xs:appinfo>
      </xs:annotation>
      </xs:element>
    - <xs:element name="OrderSeqNumber" type="xs:string">
    - <xs:annotation>
    - <xs:appinfo>
      <b:fieldInfo sequence_number="2" justification="left" />
      </xs:appinfo>
      </xs:annotation>
      </xs:element>
    - <xs:element name="ShipRefNumber" type="xs:string">
    - <xs:annotation>
    - <xs:appinfo>
      <b:fieldInfo sequence_number="3" justification="left" />
      </xs:appinfo>
      </xs:annotation>
      </xs:element>
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:schema>

    Harold Rosenkrans
    • Edited by Hal Rose Tuesday, January 18, 2011 4:45 PM Misspelling
    Tuesday, January 18, 2011 4:42 PM

Answers

  • At this point I would open the BizTalk admin console and click the group hub page and then find the suspended instances. You can drill in to see the source of the message. I think the error here actually sounds like a response is coming back from the Oracle proc but then not going anywhere.

    I was guessing you had set the send port filter to BTS.ReceivePortName == <name of the receive port>.

    If the message looks like the response from the Oracle port then just make another send port (this time maybe make it a FILE one) with a filter for BTS.MessageType == <namespace of the response schema>.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Hal Rose Thursday, January 20, 2011 7:10 PM
    Thursday, January 20, 2011 1:30 AM
    Moderator

All replies

  • It sounds like it is a little confusing what your pipeline looks like. Is it just the FlatFileDisassembler?

    If you are setting an inbound map, it has to use the XmlReceive pipeline or have the Xml disassembler in the pipeline. But there can only be one disassembler per receive pipeline. Probably the send port is getting the flat file's Xml version of the file and then there is the schema mismatch which is the error.

    In order to accomplish it in the flat file disassembler and a map in pipeline you would need to use the ESB toolkit to handle taking the message and executing the transform. You need to look at the ESB Dispatcher component (http://msdn.microsoft.com/en-us/library/ff648567.aspx) and then use a STATIC resolver to the name of the map.

    So your custom pipeline would have the FlatFileDisassembler and the ESB Dispatcher in it.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, January 18, 2011 6:39 PM
    Moderator
  • In the resolvers, the TransformType key refers to the fully qualified (strong named) type of the map.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, January 18, 2011 6:42 PM
    Moderator
  • Thanks for your reply Ben - if this is a repeat I apologize, I clicked the link in your first reply and it didn't open in a separate window. BTW, I am working in VS 2010 and BizTalk Server 2010 on a windows 2008 virtual machine. I mention this since when I went to look at the ESB dispatcher link you gave me, there is a caveat that essentially says it is deprecated technology [that may be a harsh paraphrase, I don't know]

    Anyway, I am using a custom pipeline with a flat file disassembler. And I bind the map to the inbound maps of the send port. The overall data flow looks like [as best I can depict with the editor]

    Text file -> receive port [Receive pipeline] -> XML -> [Inbound Map] -> [XMLReceive] send port [XMLTransmit][WCF machinery] -> Oracle stored procedure

    I have verified the flat file against the flat file schema and have tested the map [with the flat file as input] successfully in VS 2010.

    So I thought if the map tested ok in VS it should work in BizTalk just as well. I am not sure if I follow your directions. Are you directing me to use the ESB Dispatcher component in the custom pipeline I already created. The article says "The Dispatcher component also uses the Transform libraries that ship with the ESB Guidance to execute BizTalk maps." So are you saying I need to execute the map in the receive pipeline? If that's the case I am thinking to myself htat I am better off doing this in an orchestration. I am failing to grasp the subtleties of the architecture. I keep thinking BizTalk knows how to use the map but you seem to be indicating that it doesn't and that I have to include an extra piece to facilitate.

    If you could step through your suggestion in a little more detail I would appreciate it.


    Harold Rosenkrans
    Tuesday, January 18, 2011 10:12 PM
  • The error is confusing, it seems like the message is still in the flat file Xml when it is sent to the Oracle stored proc and has not been transformed into the request message for the stored proc call. Using the Flat file disassembler there is not an XmlReceive happening so the map never executes.

    You could specify the map as an outbound one on the send port and be sure to use XmlTransmit. This would be a simpler solution than what I was thinking before. Since you are just going directly to the send port this would be fine.

    If you had to have the transform happen in the receive pipeline the ESB dispatcher would work for this. The ESB dispatcher in the Toolkit 2.1 (BizTalk 2010 / VS 2010) now has a property called MapName. Some people want the messaging-only to just work - if you are ok with an orchestration this would a simpler architecture.

    Thanks,

     


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, January 18, 2011 11:20 PM
    Moderator
  • Yep, I tried that [outbound map on the send port] but I get:

    ERROR

    The Messaging engine failed to process a message submitted by adapter:WCF-Custom Source URL:oracledb://pdev/. Details:The published message could not be routed because no subscribers were found. This error occurs if the subscribing orchestration or send port has not been enlisted, or if some of the message properties necessary for subscription evaluation have not been promoted. Please use the Biztalk Administration console to troubleshoot this failure.

    WARNING

    The adapter failed to transmit message going to send port "WcfSendPort_OracleDBBinding_S2KDBA_Procedure_Custom" with URL "oracledb://pdev/". It will be retransmitted after the retry interval specified for this Send Port. Details:"Exception of type 'Microsoft.BizTalk.Message.Interop.BTSException' was thrown.".

    And the receive port is bound to the send port using the "BTS.RecievePortName == []" binding

    Not sure what that means.


    Harold Rosenkrans
    Wednesday, January 19, 2011 6:42 PM
  • Well, some additional info - I set up a unit test on the custom pipeline and generated the XML file. Then I used that as an input to a unit test of the Map. Everything passed and the XML generated from the Map unit test looked like it is supposed to. That is in line with your supposition that the Map isn't being executed, for some reason, in BizTalk, otherwise the Send Port should not even see the XML from the receive port and thus should not see a Root node [in reference to the error message "...Unexpected start node "Root"..."]. Is there perhaps a configuration parameter I missed on the send port that might enable proper execution of the Map?

    Thanks for your help - I would think this isn't rocket science but so much happens under the hood that can't really be seen.


    Harold Rosenkrans
    Wednesday, January 19, 2011 10:46 PM
  • At this point I would open the BizTalk admin console and click the group hub page and then find the suspended instances. You can drill in to see the source of the message. I think the error here actually sounds like a response is coming back from the Oracle proc but then not going anywhere.

    I was guessing you had set the send port filter to BTS.ReceivePortName == <name of the receive port>.

    If the message looks like the response from the Oracle port then just make another send port (this time maybe make it a FILE one) with a filter for BTS.MessageType == <namespace of the response schema>.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    • Marked as answer by Hal Rose Thursday, January 20, 2011 7:10 PM
    Thursday, January 20, 2011 1:30 AM
    Moderator
  • HI Hal Rose,

    You can call the stored procedure in OracleDB with out orchestration. I did a smilar problem where I had to insert data into oracleDB. I used ESB and WCF-Custom Binding for oracleDB.

    Multi-record Insertion into Oracle DB using ESB Messaging Scenario gives you an idea. Other issuse I eventually had was storing credintails in plain text in BRE. I used SSO for that.  Create a Custom Itinerary Service for Calling BRE Rules.

    You can accomplish same in a custom pipeline  but you will need to transfrom and promote context properties. I fell ESB would offer flexibilty to accomidate changes.

    Thanks,

    TenaliNaga

    Thursday, January 20, 2011 1:38 PM
  • Thanks Tenali - I have made progress in understanding the nature of the problem a little better. The simpelist way to do it is to have the receive port pick up the file and put the map on it's inbound maps collection and bind it to a custom pipeline with a file disassembler using the flat file schem - then I created a one-way static send port WCF-Custom and configured the action for the URI of the Stored procedure. works great. The file disassembler in the pipeline converts the text (parameters to the stored procedure) to an xml document and the map transforms it into xml expected by the WCF action.

    But, I am trying to figure out how to process the response from the stored procedure (success / failure) which means a two-way send port (this is what you get when you import the bindings schema for the stored procedure). When I was working with it before I was putting the map in send port inbound maps and the map was not executing. So at this stage I know where to put the map so it will execute.

    So now the XML is getting to the send port but now it is complaining:

    The Messaging engine failed to process a message submitted by adapter:WCF-Custom Source URL:oracledb://pdev/. Details:The published message could not be routed because no subscribers were found. This error occurs if the subscribing orchestration or send port has not been enlisted, or if some of the message properties necessary for subscription evaluation have not been promoted. Please use the Biztalk Administration console to troubleshoot this failure.

    I created a send port (type file) that I intended to handle the reponse from the stored procedure and then with the two way send port from the bindings import I added a criteria to the filter where I pointed the BTS.AckSendPort name to the file send port I just created. Anyway it runs and no errors are generated but the stored procedure doesn't execute either.

    So I just need to figure out how to handle [subscribe to] the response message and all will be well in the land that forgot orchestrations.


    Harold Rosenkrans
    Thursday, January 20, 2011 6:52 PM
  • Thanks Ben - that is exactly what I was looking for - that plus putting the map on the receive port as an inbound map is the complete solution
    Harold Rosenkrans
    Thursday, January 20, 2011 7:11 PM
  • I would just make the send port filter BTS.MessageType == schemanamespace\#Response or whatever the appropriate name is for the response message type. If you open the admin console and see the suspended message it will tell you the namespace value to use.

    The admin console can show you the suspended message while the application is still running right after the proc sends the response back.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, January 20, 2011 7:14 PM
    Moderator
  • Yes, I am just starting to figure out how to use the console better - the message context has the most helpful information - The message type looks almost like the action except for the addition of the # sign and the removal of the / before the Response part. in my case it ended up being

    [http://Microsoft.LobServices.OracleDB/2007/03/S2KDBA/Procedure#P_TEST_WFC_STORED_PROCResponse]


    Harold Rosenkrans
    Thursday, January 20, 2011 7:25 PM
  • I  was pretty sure you were still trying to navigate through the console - it takes a little while and you have to click a couple times to get to where you need to go. But I am glad you got it working!

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, January 20, 2011 7:29 PM
    Moderator
  • Hi Harold,

    I felt this is so good when I first knew about this. So you have maps in receive location and sending it to static solicit-resp send port (WCF - oracleDB/custom configured for oracle DB).  

    you can test your case if you have receive-response input port and your solict-resp subscribes to this receive port in which case response from solicit-resp goes to receive port send handler.

    Ben's right about subscribing based on message type for response. 

    Thanks,

    TenaliNaga. 

    Saturday, January 22, 2011 3:01 AM