SQL & PL/SQL

Hi,

We have an oracle database which contains a number of tables.

From these tables I need to be able to populate a number of holding tables in a MSSQL database via a nightly scheduled job.

Therefore I will insert into these tables via a procedure from my oracle tables.

I need to ensure that if any of the inserts fail for a particular record I am uploading out of the five insert statements then the whole process is rolled back.

I therefore having something like the following

FOR cursor1_rec IN cursor1_cur LOOP -- This gives me the records I need to insert into the holding table for

BEGIN
INSERT into ms_holding_table_1
(field1, field2)
select 1,2 from oracle_table_1
where id = cursor1_rec.id;

INSERT into ms_holding_table_2
(field1, field2, field3, field4)
select 1,2,3,4 from oracle_table_2
where id = cursor1_rec.id;

INSERT into ms_holding_table_3
(field1)
select 1 from oracle_table_3
where id = cursor1_rec.id;

INSERT into ms_holding_table_4
(field1, field2)
select 1,2 from oracle_table_4
where id = cursor1_rec.id;
END;

END LOOP;

I need to ensure that if the insert for that particular cursor record fails on the 2nd insert statement, the first insert also rollbacks. Likewise if it fails on the 4th insert, I need to ensure that all 4 inserts are rolled back.

If you could provide me with any information as to how to achieve this.

Would issuing a savepoint before the first insert statement and then in my exceptions rolling back to the savepoint before the end of the loop suffice?

Thanks in advance,
ca84