Skip to Main Content

Operation on a Table type variable storing multiple values one time rather than looping

4245323May 5 2020 — edited May 30 2020

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.

Comments
Post Details
Added on May 5 2020
19 comments
774 views