Orchestration with multiple stored procedures and maps!

malmeida

Hi,

I am new to biztalk and i have some problems constructing my orchestration.
My schenario is:

I have a oracle database and i need to do a poll to information from it. -->
For this i have oracle adapter for biztalk. I have use the table change
event, and it works.
Next, with an ID from the poll that i have done i need to to execute a
stored procedure that do a search with this ID and inserts the information on
a temporary table... -- > I know how to execute the stored procedure, but how
can i pass the ID, like a parameter? Use a map for this?
Next, this procedure returns an integer.
Now, it is necessary to do a query to the temporary table --> I don't now
how to the query when using multiple stored procedures. i know how to do this
query with a xml file.
After this i need to construct a message with the information from the
first poll and with the information of the temporary table --> I know that i
can use multiple schemas, but i don't know how to do this.
Finally i need to execute a stored procedure, that receives the integer that
the first stored procedure returns (procedure that insert into the temporary
table). This stored procedure delete the information from the temporary table.
I also need to send the message that i have constructed to a web service -->
this part i have done and it is working.

can some one guide me how to construct this orchestration? I know how to
execute the stored procedures, i have done it with separated projects, only
for test and with constant values. Now i want to use a single project and use
dynamic values. For example, pass the ID like a parameter. I can get this
values with xpath expression shape, but i don't know how to do this in a
stored procedure...

Thank you for your attention!
Regards,
Marco Almeida.