Hi,
I need to delete records from some tables and its child tables. Currently cascade constraints is not allowed (don't know why?) and there is separate script which delete the records manually, like records are identified to be delete into a temp table and then with EXITS clause the records are deleted from child to parent.
I was wondering whether a recursive procedure will work which will delete records from child tables onwards to parent table. Is such a recursive procedure possible or i am wasting my time :(
If it's not possible then i'll have to stick with the script . . .
--procedure will have parent table as parameter and temp table with column on which delete is based
--cursor 1 will identify childs
cursor C_CHILD (P_USER varchar2, P_TABLE varchar2) is
SELECT table_name, constraint_name
FROM all_constraints
WHERE constraint_type ='R'
AND r_owner = P_USER
AND r_constraint_name IN
(SELECT constraint_name
FROM all_constraints
where CONSTRAINT_TYPE in ('P','U')
AND table_name = P_TABLE
);
--cursor 2 will identify columns to be joined (don't have the query yet)
BEGIN
FOR C_CHILD in c1
LOOP
EXIT WHEN C_CHILD%NOTFOUND
--call procedure again (recursive)
--delete based on columns on cursor 2
END LOOP;
END