Batch Procedure Calls?


I’m currently working on an integration project which basically consists on getting data from one database and inserting it to another. At first it seems simple, but the deal is: we only have access to procedures which retrieves the data set from the origin database and procedures which insert the data for one row in the destination database. What we were doing at first was calling the get procedures, putting it into documents, transforming and mapping the data into a document list and looping through the list to call the insert procedures for each document in the list, containing the data for one record.

The problem is: we were asked if there is a way to do all the procedure calls at a time, in a single round-trip to the database (like ODP ArrayBinding in .NET, which is the current ‘integration platform’ here). I tried to create various sql statement dinamically and pass it into a dynamic SQL adapter service, but it didn’t manage to work.

Is there any way to do this?

Just thinking if batch insert would help in your case…