transaction inside a transaction

[alainc]

hello,
i have a problem
i have a big transaction failling because of a constraint failure
as my transaction delete a lot of rows i can't tell where is the problem
i have the possibillity to trace everything on a trace table. But as the transaction failed my trace table is also rollback!!!

so is there a possibillity to insert a rows on a table with a 'local commit' of that trace and not on the rest of the big transaction

thanks

Alain

[ales]

Hello,
autonomous transaction will help you:

procedure do_report(r in varchar2)
is
  pragma autonomous_transaction;
begin
  insert into t_report values(r);
  commit;
end;

Procedure above inserts a string into report-table and commits. This commit doesn't have anything to do with a transaction outside.
HTH,
Ales

[julian]

And remember the following interesting fact. If you have the autonoumous transaction in a trigger, then you still need the commit!

[Raminder]

You can disable constraints before executing the transactions and then enable them using something like

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;

You need an exceptions table for showing bad records.
You can create an exception table by running the script UTLEXCPT.SQL, which creates a table named EXCEPTIONS.

You can find out the offending rows by

SELECT * FROM exceptions;

and then delete/modify them.