CREATE OR REPLACE procedure pre_tra
as
cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name';

c2 cconstraints%rowtype;
mytab varchar2(200);
mytab1 varchar2(200);
mytab3 varchar2(200);
mytab4 varchar2(200);
retrycounter number;
counter number;
cstatus varchar2(20);
enableerrorfound boolean:=true;
--MAX_TRY number:=10000;
begin
begin
open cconstraints;
loop

fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab := c2.table_name;
mytab1 := c2.constraint_name;
mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1;
execute immediate mytab3;

end loop;
close cconstraints;



open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
execute immediate 'delete ' || mytab;


end loop;
close cconstraints;
end;


open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
mytab4 :='DATABASENAME';
execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')';
DBMS_OUTPUT.PUT_LINE(mytab);
commit;
DBMS_OUTPUT.PUT_LINE(mytab);
end loop;
close cconstraints;


begin
DBMS_OUTPUT.PUT_LINE('Starting enable script.....');

RETRYCOUNTER := 3;

WHILE RETRYCOUNTER > 0
loop
open cconstraints;

loop
fetch cconstraints into c2;
exit when cconstraints%notfound;

DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab);
mytab := c2.table_name;
mytab1 := c2.constraint_name;
--cstatus:= c2.status;
--if cstatus = 'DISABLED' then
mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1;
DBMS_OUTPUT.PUT_LINE('Q' || mytab3);
DBMS_OUTPUT.PUT_LINE(retrycounter);
--enableerrorfound:=false;
BEGIN
execute immediate mytab3;
EXCEPTION
WHEN OTHERS THEN
enableerrorfound:=true;
END;
--end if;



end loop;
RETRYCOUNTER := RETRYCOUNTER - 1;
DBMS_OUTPUT.PUT_LINE(retrycounter);
close cconstraints;
end loop;

DBMS_OUTPUT.PUT_LINE(retrycounter);

IF (enableerrorfound) THEN
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE');
else
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE');
end if;
end;

end pre_tra;
/