Dears,

I am calling an oracle procedure using WCF-Oracle adapter.  I intended to call the procedure multiple time by repeating stored procedure request node in BizTalk map.

I generated schemas using Consume Adapter service contract type is "Client-Outbound operation".

Now, the generated schema has two root nodes. One for the request and other for the response.

Then, i added a map , i have a repeated node in input schema and output schema is the generated schema of stored procedure request .

The problem is that since my stored request node is a root node of schema . How call i repeat the SP request node, so that adapter calls the stored procedure multiple times?

I hope guys get me problem. Please suggest some solution.

Regards,

Umair

You can call you Stored Procedure, or perform any Operations, multiple times by using Composite Operations, supported by the WCF SQL Server and Oracle adapters.

Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

• Edited by Johns-305 [boatseller]MVP, Moderator 17 hours 38 minutes ago

Hi Umair,

There is small workaround which you need to do with generated schema from from SQL server . Try making the request node element to unbounded and create a simple plain xsd as per your sql request xml.

There is already a post with similar requirement and you can follow the steps to get things done .

Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape

Thanks

Abhishek

You can call you Stored Procedure, or perform any Operations, multiple times by using Composite Operations, supported by the WCF SQL Server and Oracle adapters.

Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

• Edited by Johns-305 [boatseller]MVP, Moderator Saturday, August 29, 2015 1:26 PM

There is small workaround which you need to do with generated schema from from SQL server . Try making the request node element to unbounded and create a simple plain xsd as per your sql request xml.

There is already a post with similar requirement and you can follow the steps to get things done .

Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape

Sorry, but this completely wrong answer.  Thiago's post is about the legacy SQL Adapter, the OP is asking about the WCF Oracle Adapter.

Hi Abhishek,

Thanks for the feedback, i am using WCF-oracle adapter. 

The main problem is that, unlike SQL add adapter metadata, in WCF-oracle consume adapter service makes the root node that corresponds to the stored procedure request. So, we cannot make the root-node as max-occurs:unbounded.

However, in SQL add adapter metadata; it wraps the stored procedure request into a root node. So, here we can make the stored procedure request node as max-occurs:unbounded.

Regards,

Umair

Hi Umari Ahmen, please read my post above, it tells you exactly how to accomplish what you want.

It was mistake from my side to share the link for native sql adapter . Similar approach can be done  to call multiple stored procedures in a single transaction.

BizTalk Server 2010 - Using the WCF-SQL adapter to make multiple stored procedure calls

You can generate schema for composite Operation having root node corresponding to each stored procedure

This is same what boatseller is trying to explain.

Thanks

Abhishek

For clarity, the OP asked about multiple operations with the WCF-Oracle Adapter.

Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

For clarity, the OP asked about multiple operations with the WCF-Oracle Adapter.

Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP 22 hours 24 minutes ago
• Marked as answer by Umair Ahmed 22 hours 22 minutes ago

For clarity, the OP asked about multiple operations with the WCF-Oracle Adapter.

Oracle Composite Operations: https://msdn.microsoft.com/en-us/library/dd788415.aspx?f=255&MSPPError=-2147217396

More details and example of Composite Operations: http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

• Proposed as answer by Abhishek0127[Abhishek kumar]MVP Sunday, August 30, 2015 8:40 AM
• Marked as answer by Umair Ahmed Sunday, August 30, 2015 8:42 AM

Many Many thanks for the help.

I can call multiple records by using CompositeOperation.

I want to clarify one thing.: I donot want parrallel execution of stored procedue. I want it to be sequential, I made my send port "ordered delivery" as enabled. Will it gaurantee, sequential execution of stored procedure?

The reason i want make sequential calls is because i am getting exception: "Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-02049: timeout: distributed transaction waiting for lock"