Is there any auto commit happens after executing stored procedures in oracle?

Question

I have 3 tables in oracle DB. I am writing one procedure to delete some rows in all the 3 tables based on some conditions.

I have used all three delete statements one by one in the procedure. While executing the mentioned stored procedure, is there any auto-commit happening in the at the time of execution?

Otherwise, Should I need to manually code the commit at the end?

Answer 1

There is no auto-commit on the database level, but the API that you use could potentially have auto-commit functionality. From Tom Kyte.

That said, I would like to add:

Unless you are doing an autonomous transaction, you should stay away from committing directly in the procedure: From Tom Kyte.

Excerpt:

I wish PLSQL didn't support commit/rollback. I firmly believe transaction control MUST be done at the topmost, invoker level. That is the only way you can take these N stored procedures and tie them together in a transaction.

In addition, it should also be noted that for DDL (doesn't sound like you are doing any DDL in your procedure, based on your question, but just listing this as a potential gotcha), Oracle adds an implicit commit before and after the DDL.

Answer 2

There's no autocommit, but it's possible to set commit command into stored procedure.

Example #1: no commit

create procedure my_proc as
begin
  insert into t1(col1) values(1);
end;

when you execute the procedure you need call commit

begin
  my_proc;
  commit;
end;

Example #2: commit

create procedure my_proc as
begin
  insert into t1(col1) values(1);
  commit;
end;

When you execute the procedure you don't nee call commit because procedure does this

begin
  my_proc;
end;