Skip to Main Content
  • Questions

  • Commit inserts/updates to a single table but not other tables in the same transaction

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sehrope.

Asked: April 30, 2005 - 3:03 pm UTC

Last updated: May 01, 2005 - 3:44 pm UTC

Version: 9.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom! I've said it before but I'll say it again, love the site!

My questions concerns whether it is possible to commit the inserts/updates to a table while not committing inserts/updates to other tables in the same transactions. The general idea is to have a log table into which a long running process inserts rows. Ex:

log that process started : commit only log
do some work
log that piece 1 finished : commit only log
do more work
log that piece 2 finished : commit only log
do more work...
.
.
.
commit all work done in a single commit


Basically I'd like to be able to see the rows in the log table without having to commit the whole transaction. I've used dbms_application_info to get stats from long running PL/SQL blocks before but I don't think that it will work in this case as I'd like to report back more than just a Job-Name/#Rows-Processed and I'd like it to be stored for future use.

The best solution I've come up with is to store the log info in local variables and insert it into the log at the end of the transaction regardless of success/failure and use DBMS_APPLICATION_INFO to give a limited view of what is happening currently. I don't particularly like this solution as it involves duplicating work (log file and longops) and it still doesn't give a whole picture of the state of the job during execution.

If it is possible to commit only the transactions to the log table then I could have up to the moment info on the state of the job and have it be persistent. Is it possible?

Thanks,
-- Sehrope Sarkuni

and Tom said...

This is a valid use of an autonomous transaction:

create or replace procedure log_and_commit_it( .... )
as
PRAGMA autonomous_transaction;
begin
insert into log (...) values ( ... );
commit;
end;
/

now you can code:


log_and_commit_it( ... );
work
log_and_commit_it( ... );
more_work
...
rollback;

work and more_work will be undo, log_and_commit_it will persist and their commits will not affect work and more_work

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect

Sehrope Sarkuni, May 01, 2005 - 12:28 pm UTC

Great answer Tom. I just read up on autonomous transactions and it does seem like the perfect tool to solve this. This further supports my theory that anytime I get stuck at something on the DB side, there is a new (to me) Oracle feature that will provide exactly what I need.

Tom Kyte
May 01, 2005 - 3:44 pm UTC

I learn something new every day.... about Oracle (and life in general but also about the database...)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library