Is commit necessary after DML operation in Function/Procedure?

Question

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure?

Example:

create or replace function test_fun
return number is
begin
   delete from a;
   return 0;
end;

or procedure

create or replace procedure aud_clear_pro
as
begin
   delete from a;
end;

does it need commit after delete?

Cannot understand the following situation:

1. If I call the function/procedure from SQL window then it requires commit

   but

2. If I schedule function/procedure using dbms_scheduler and run the job, delete statement is automatically committed.

   WHY?

Answer 1

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction cannot simply call the procedure directly.

If you call a procedure interactively, you will have to explicitly commit or rollback the transaction because Oracle has no idea if you intend the procedure call to be a logical transaction or if you intend to compose a larger transaction involving multiple procedure calls. If you use dbms_scheduler, dbms_scheduler assumes that a job is a logical transaction and commits at the end of the job assuming it was successful (dbms_job does the same thing).

Functions should not manipulate data in the first place. A function that manipulates data cannot be called from a SQL statement (barring the corner case where the function itself is declared to use an autonomous transaction which is almost never appropriate). The whole point of having both functions and procedures is that functions can be embedded in SQL statements and can be more freely granted to users because they do not change any data.

Answer 2

To answer your question; WHY?

You probably already know this by now since the post is 2 years old. But I'll respond just for the record.

The reason #1 requires a commit and #2 doesn't is because the default database setting in Oracle is to commit a transaction when a session ends. If you are in sqlplus and run your code manually, it will not commit the transaction right away. If you issue an explicit commit OR you log off of sqlpus, then the transaction will commit.

The reason why you get an automatic commit on #2 is because it creates a session to run your script. When it completes it automatically logs off, which will cause an automatic commit.