I am invoking a stored procedure from BPEL process.
The procedure first truncates table A and then fetches some rows from some other tables and dump those rows in table A.
The procedure dumps around 150,000+ rows. The developer of the procedure says the procedure completes in around 5 minutes.
But when I invoke it from SOA the BPEL just get stuck at the invoke activity and the instance goes into running state forever.
Any pointers/suggestions in this regard are highly appreciated.
Check with the developer to log statuses in the PL/SQL when you make the call.
Also it might help to start with smaller chunks before processing something this large.
Also, you are probably dealing with a huge file, might not be a bad idea to make sure you are tuned to support large payloads.