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


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

or procedure

create or replace procedure aud_clear_pro
   delete from a;

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


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



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.

| improve this answer | |
  • 1
    Is it not possible in Oracle for the caller to begin a transaction that binds procedure calls? In SQL Server you can commit inside the procedure, but if the caller opened a transaction before calling that procedure, nothing gets committed until the caller commits too. – Nick Chammas May 7 '12 at 16:18
  • 4
    @NickChammas - Oracle doesn't have the concept of a nested transaction, no. If the procedure commits, everything the caller had done up to that point would commit. The caller always starts a transaction implicitly with its first statement (whether that is a procedure call or something else) so it should always be up to the caller to end the transaction. – Justin Cave May 7 '12 at 16:22
  • @JustinCave Whilst that's true, don't forget about autonomous transactions. – Philᵀᴹ May 7 '12 at 20:55
  • @Phil - True, but that's a very different animal. An autonomous transaction cannot see the uncommitted changes made by the caller and cannot be rolled back by the caller so it is extremely unlikely that anything other than a logging procedure ought to be declared to use an autonomous transaction. – Justin Cave May 7 '12 at 21:03

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.