This content has been marked as final. Show 4 replies
count NUMBER := 0;
total NUMBER := 0;
CURSOR del_record_cur IS
FOR rec IN del_record_cur LOOP
DELETE FROM .
WHERE rowid = rec.rowid;
total := total + 1;
count := count + 1;
IF (count >= 1000) THEN
count := 0;
DBMS_OUTPUT.PUT_LINE('Deleted ' total ' records from ..');
Asif - thanks v much for your speedy reply.
I was wondering whether you would mind just briefly running through that script for me...
particulary the IN cursor LOOP part. My understanding is that the curser will pick up the select row e.g.
WHERE provider_id = '100'
as part of the script body, i am unsure where to reference the other tables this record may have been included as an PK constraint?
Assuming that the other 19 tables that have provider_id all reference provider_tbl as an FK, then it is relatively simple.1 person found this helpful
As the owenr of these table you would use the user_constraints table to determine the tables that have an FK relationship with provider_tbl.
Assuming that you know the provider_id of the provider you want to delete, then you could do something like:
SELECT table_name FROM user_constraints WHERE r_constraint_name = (SELECT constraint_name FROM user_constraints WHERE table_name = 'PROVIDER_TBL' and constraint_type = 'P')
DECLARE l_provider NUMBER := 1234; BEGIN FOR rec IN (SELECT table_name FROM user_constraints WHERE r_constraint_name = (SELECT constraint_name FROM user_constraints WHERE table_name = 'PROVIDER_TBL' and constraint_type = 'P')) LOOP EXECUTE IMMEDIATE 'DELETE FROM '||rec.table_name||' WHERE PROVIDER_id = :b1' USING l_prpovider; END LOOP; COMMIT; END;
Many thanks for this. i am working through, and will post the result.