Advisable to put a Commit in an Oracle EBS Concurrent Program?

Question

i just want to ask if its Advisable to put a Commit inside PL/SQL Procedure being called by an Oracle EBS Concurrent Program? I've always believed its bad practice to put commits inside the program for the following reasons:

1. If the Program encounters an Exception after the Commit, we cannot Roll it back anymore.
2. Let the calling application do the implicit commit (in this case, EBS)
3. As in the case of Oracle Workflows, we should never put Commits inside the WF package as it will disrupt the "rollback" feature of the Workflow.

However, i keep on seeing Oracle EBS Developers put commits inside a single Concurrent Program. Any thoughts on this?

Thanks!

Answer 1

Don't worry, you are doing right. Database developers are not aware of many thing until they get burn in production. Avoiding COMMIT inside a code is about enabling this code to be used with other code units, without breaking the whole system logic. Rollback is not only about exceptions but also about the freedom of a user to cancel an action within interactive program, without corrupting the database.

Here is an example of what happens when one developer (the developer of unit_2) takes the freedom to use COMMIT.

create table t (i int);

---

create or replace procedure unit_1 as begin insert into t(i) values (1); end;
/
create or replace procedure unit_2 as begin insert into t(i) values (2); commit; end;
/
create or replace procedure unit_3 as begin insert into t(i) values (3); end;
/
create or replace procedure unit_4 as begin insert into t(i) values (4); end;
/

---

create or replace procedure all_units as begin delete t;unit_1;unit_2;unit_3;unit_4; end;
/

---

exec all_units;

select * from t;

---

         I
----------
         1
         2
         3
         4

---

set transaction t; exec all_units; rollback;


select * from t;

---

         I
----------
         1
         2