Hi,

The problem i will present is a lock problem.
I have Store procedures that make a Foreach Select to get information an for each row return will performe several update, inserts, and more select depending on the information that was return by the First FOREACH.
The problem is that when you have multiThread these foreach will coul lock many rows during may time.

To resolve the problem i tried to:
1- put a commit inside FOREACH (Didn't work because the cursor will ends)
2- I try to insert the FOREACH SELECT information into a collection array an then iterate tha collection. But to do that i need a FOREACH iterate the collection and then the COMMIT problem will exists.

Anyone have any ideia?
FOREACH
SELECT col1, col2,col,3 FROM Tab1, Tab2
INTO var1,var2,var3....
WHERE ......









END FOREACh

Try declaring your cursor 'with hold' as in

foreach with hold select ... into ... from ...
end foreach;

These cursors will not be closed whenever you make a commit.

Regards,

Hi everyone specially iaguigon,

Thanks for the advice, did work but only for the Select Statements.

My problem is that inside the Store procedures I invoke another’s store procedures that return several rows.

I do:

FOREACH EXECUTE PROCEDURE SP1(Args....)
INTO.....
END FOREACH;

But I change as you told me I change SP1 to make commit inside and because of that I was force to change the call for:
FOREACH WITH HOLD EXECUTE PROCEDURE SP1(Args....)
INTO.....
END FOREACH;

But a syntax error occurred: SQL Error (-201) : A syntax error has occurred.

Can somebody help me?

Thanks again and have a nice weekend

If you want to check detail information of the error codes. such as SQL,4GL RUNTIME.....MORE...
I found a site offers these error code details, you can study on
http://www.oninit.com/errorcode/index.html
it's do a little help for me at work.