Is it possible/make sense to have COMMIT statement in SQL functions?
3 Answers
Technically, the answer ist yes. You can do the following:
create or replace function committest return number as
begin
update my_table set col = 'x';
commit;
return 1;
end;
/
declare
number n;
begin
n := committest();
end;
/
However, you can't do the following:
select committest() from dual;
this would be a commit during a query and thus result in a
ORA-14552: Cannot Perform a DDL Commit or Rollback Inside a Query or DML
Comments
Yes, you can do that if you make the function an autonomous transaction. That way it will not be part of the current transaction anymore.
create or replace function doIt
as
pragma autonomous_transaction;
begin
... code ...
commit;
end;
/
answered May 25, 2016 at 12:18
Martin Schapendonk
13.6k3 gold badges22 silver badges26 bronze badges
3 Comments
Justin Cave
You can. But I'd suggest a whole heap of "but you really shouldn't unless you understand deeply the transactional implications of what you are asking for" caveats.
Martin Schapendonk
Agree, but we don't know what OP wants to accomplish. The simplest legitimate use I can think of is logging; it is very inconvenient (to say the least) if a transaction fails and the associated logging is rolled back as well.
Justin Cave
True. If you have a procedure whose only function is to log and you want that logging to persist even if the transaction fails, autonomous transactions are wonderful and appropriate. Basically any other use of autonomous transactions, though, is at best a hack and at worst a bug. The vast majority of people using autonomous transactions are using them incorrectly (see any mutating table thread) so a suggestion to use them without some evidence that this would be a valid use case strikes me as dangerous.
No, it's not possible, see the documentation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
Restrictions on User-Defined Functions
.... In addition, when a function is called from within a query or DML statement, the function cannot: ....
- Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
pragma autonomous_transaction). If you do a commit, remember that it will affect all DML done since the transaction started, hence why it's generally not a good thing to do mid-transaction.