How to rollback between two savepoints like scenario

Question

I am working on oracle 11g.I am completely new to oracle but now i need to write a storedproc. In my proc i am deleting tables and inserting form staging tables.while deleting many tables has integrity constraints. if i delete all child tables also after my insertion in parent table i need to rollback all child tables.

eg:

delete ch1,ch2,ch3;
delete parent;
insert into parent;
rollback ch1,ch2,ch3;

please give me some solution like disable/enable integrity constrains or how to make my transaction worthful to my scenario.

You're really only refreshing the parent table? What happens if the refreshed data is missing a record that used to exist and had child records? You can [search for how to disable constraints](http://stackoverflow.com/search?q=%5Boracle%5D+disable+constraints). You might also be able to make the constraints deferrable, if you're deleting/inserting as one transaction. — Alex Poole, Aug 15 '13 at 18:48
Thank you Alex.Yes,exactly i am refreshing the parent table but always refreshed data would not miss such scenario.it will help if any code snippet. — user2686982, Aug 15 '13 at 18:56
The [first answer on the first question](http://stackoverflow.com/a/131595/266304) from that search has code to disable the triggers. You can probably be more restrictive and filter to only modify your child tables and only foreign keys (`'R'`). Although it might be easier to just list the tables and constraints rather than using dynamic SQL, depending on how many there are, how often they might change, and how often this has to be done. — Alex Poole, Aug 15 '13 at 19:09
I have almost 110 tables are child for one parent primary key.So i need to disable all child foreign keys,no need to parent primary key.I got my answer Alex thank you so much. — user2686982, Aug 15 '13 at 19:20
possible duplicate of [Disable all table constraints in Oracle](http://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle) — Alex Poole, Aug 15 '13 at 19:23
Duplicate constraints? one child has only one FK to refer parent Pk.can you please more elaborate. — user2686982, Aug 15 '13 at 19:29

score 0 · Answer 1 · answered Aug 16 '13 at 08:35

If the child tables are static then you can generate a scripts to enable and disable them all using a query like this:

select 'alter table ' || fk.owner ||'.'|| fk.table_name
  ||' disable constraint '|| fk.constraint_name ||';'
from all_constraints fk
join all_constraints pk
on pk.owner = fk.r_owner
and pk.constraint_name = fk.r_constraint_name
where fk.constraint_type = 'R'
and fk.status = 'ENABLED'
and pk.constraint_type = 'P'
and pk.table_name = '<your parent table>;

That gives a list of alter table commands that can be run before your delete/insert, and you can do the same thing to create a script to re-enable them all.

If you want to do it on the fly, which is more likely given that you want to do this from a stored procedure, you can do the same thing in a cursor and execute it as dynamic SQL:

begin
  for r in (
    select 'alter table ' || fk.owner ||'.'|| fk.table_name
      ||' disable constraint '|| fk.constraint_name as stmt
    from all_constraints fk
    join all_constraints pk
    on pk.owner = fk.r_owner
    and pk.constraint_name = fk.r_constraint_name
    where fk.constraint_type = 'R'
    and fk.status = 'ENABLED'
    and pk.constraint_type = 'P'
    and pk.table_name = 'T42'
    ) loop
    execute immediate r.stmt;
  end loop;
end;
/

SQL Fiddle demo of both options.

How to rollback between two savepoints like scenario

1 Answers1