I have written the below code and it is working fine as expected but not per my requirement
DECLARE
-- declare a nested table type
TYPE test_mgr_hierar_tab_typ
IS TABLE OF per_all_people_f.person_id%TYPE;
-- declare and initialize a nested table variable
test_mgr_hierar_tab_var test_mgr_hierar_tab_typ := test_mgr_hierar_tab_typ();
CURSOR csr1
IS
select * from test_mgr_hierar5;
BEGIN
for v IN csr1
LOOP
test_mgr_hierar_tab_var.EXTEND;
test_mgr_hierar_tab_var(test_mgr_hierar_tab_var.LAST) := v.person_id;
END LOOP;
FOR l_index IN test_mgr_hierar_tab_var.FIRST..test_mgr_hierar_tab_var.LAST
LOOP
dbms_output.put_line(test_mgr_hierar_tab_var(l_index));
delete from test_mgr_hierar2 where person_id <> test_mgr_hierar_tab_var(l_index);
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
i.e. deleting from table - test_mgr_hierar2 where person_id <> (The person id's in test_mgr_hierar5 is fetched one by one in loop)
so ultimately everything is getting deleted from table - test_mgr_hierar2 and is blanking out.
What I was needing was like below -
deleting from table - test_mgr_hierar2 where person_id <> (all person id values together and not once each in loop)
so I needed something like this deleting from table - test_mgr_hierar2 where person_id <> (1,2,4,9) and if table - test_mgr_hierar2 has another person id say 11, then when I query
select * from test_mgr_hierar2, it should give only one record as four records got deleted.
In my case table is getting blanked out because values are being fetched in loop separately and actually nothing remains by the time loop finishes.
Could some one assist on any option for deletion after the loop or anyways so that deletion happens at once or anything which I may be doing differently working with Table types?
Appreciate your help on this.