oracle - How to rollback between two savepoints like scenario -

i working on oracle 11g.i new oracle need write storedproc. in proc deleting tables , inserting form staging tables.while deleting many tables has integrity constraints. if delete child tables after insertion in parent table need rollback child tables.

eg:

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

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

if child tables static can generate scripts enable , disable them using query this:

select 'alter table ' || fk.owner ||'.'|| fk.table_name   ||' disable constraint '|| fk.constraint_name ||';' all_constraints fk join all_constraints pk on pk.owner = fk.r_owner , pk.constraint_name = fk.r_constraint_name fk.constraint_type = 'r' , fk.status = 'enabled' , pk.constraint_type = 'p' , pk.table_name = '<your parent table>; 

that gives list of alter table commands can run before delete/insert, , can same thing create script re-enable them all.

if want on fly, more given want stored procedure, can same thing in cursor , execute dynamic sql:

begin   r in (     select 'alter table ' || fk.owner ||'.'|| fk.table_name       ||' disable constraint '|| fk.constraint_name stmt     all_constraints fk     join all_constraints pk     on pk.owner = fk.r_owner     , pk.constraint_name = fk.r_constraint_name     fk.constraint_type = 'r'     , fk.status = 'enabled'     , pk.constraint_type = 'p'     , pk.table_name = 't42'     ) loop     execute immediate r.stmt;   end loop; end; / 

sql fiddle demo of both options.