Oracle stored Procedure

[sajithamol]

I have an Oracle stored Procedure from which I will have to make call to few other SPs. My requirement is that the transactions in the subsequent SPs should be independent of the main transactions i.e. transactions of the Main SP. When I issue a Commit in child SPs it should not Commit the Parent transactions. How can I do this?

[debasisdas]

Use PRAGMA AUTONOMOUS TRANSACTION in the inner SPs.

That way if u commit in the inner one it will not affect the outer one.

[debasisdas]

Please find the details with some sample examples regarding AUTONOMOUS TRANSACTION here.