What is the effect of placing the commit after DML in procedure?

I have created a procedure in a package which is doing insert/delete in the table and after successful transaction, commit is done.

like this:

create or replace package pac is    
procedure pr_test(emp_id number)
is
begin
-- some code
if something then
  insert
else
  delete

commit;
end pr_test;
end pac ;

Should I make this transaction as AUTONOMOUS_TRANSACTION? What is the effect of placing the commit like this in program?

What is the effect of placing the commit like this in program?

The Oracle Documentation describes COMMIT as:

Purpose

Use the COMMIT statement to end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit. This statement also erases all savepoints in the transaction and releases transaction locks.

If you have three PROCEDURE and each one contains a COMMIT statement then you cannot run all three then, if an exception occurs in a latter one, ROLLBACK them all as the changes from the first two will already be COMMITted.

As a general rule, you should not use COMMIT in a PROCEDURE or FUNCTION but should leave it up to the caller to COMMIT the transaction so they can bundle multiple actions together.

There are, of course, use cases where you will want to violate this rule but you should consider each case separately and take time to fully understand your business logic before you break this rule so you know what is COMMITted in each instance.

Should I make this transaction as AUTONOMOUS_TRANSACTION?

One use-case is logging - you may have a PROCEDURE which calls another PROCEDURE to log the user's actions and, regardless of whether the initial action succeeds or fails you want to keep a log of the action and ensure that log is COMMITted. In this case, the logging PROCEDURE should be an AUTONOMOUS_TRANSACTION and contain a COMMIT statement and the calling statement should (probably) have neither.

So, if the COMMIT of one PROCEDURE is always required and is independent of whether the caller COMMITs other data then make the PROCEDURE an AUTONOMOUS_TRANSACTION. If the PROCEDUREs can be bundled together and then ROLLBACK as a group then you do not want to make them AUTONOMOUS_TRANSACTIONs.

Depends on what you want to do. If you want the procedure to be a stand-alone transaction then I would recommend adding pragma autonomous_transaction.

Ex: First scenario

1. update table 1
2. pragma autonomous_transaction - >update table 2 -> commit
3. rollback

table 2 will be commited and table 1 will be rolled back

Ex: Second scenario

1. Update table 1
2. update table 2 -> commit
3. rollback

both table 1 and 2 will be commited cause it treats it as one transaction