Does the commit in called program commits transactions in calling program or main procedure?

Yes, in normal process. But if we do not want this, then we have to process called program or child program transactions as autonomoustransactions.

An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction

To start an autonomous transaction, we need to use AUTONOMOUS_TRANSACTION pragma (compiler directive) at the top of the section.

Autonomous Transactions Vs Nested transactions

By default, if the main procedure/function shares its context with nested/child procedures/functions. We can create autonomous transactions using the AUONOMOUS_TRANSACTION pragma. This will mark the nested transaction as autonomous hence this autonomous transaction will run in its own context.

Therefore, autonomous transactions

·       Do not share resources such as locks with main transactions

·       Do not depend on main transactions

·       Its committed transactions are visible to other transactions immediately

·       When an autonomous transaction calls another non-autonomous transaction, then this non-autonomous will become nested therefore this child will share same context as parent autonomous transaction

Other useful reading related this

http://www.orafaq.com/node/1915