Please refer the script I attached with this question -

There are records which are supposed to be stored in table type variable as a result of cursor loop on table - test_mgr_hierar5.
The table type is expected to hold values - 177603,157564,157286,186196 for column person_id.

Next step is - Need to delete records from table - test_mgr_hierar90 where person_id's are not the above ones. so virtually its like - delete from test_mgr_hierar50 where person_id NOT IN ( 177603,157564,157286,186196) -- these values are collected in table type variable.

The issue which I am facing is not the deletion , rather somehow I am not able to collect those values in a table type variable by looping/extend clause. So my issue is only pertaining to the below clause -

for v IN csr1
LOOP depts_max := depts_max + 1;
exch_rt.EXTEND;
--exch_rt(depts_max) := v.person_id; --- This is the one which is erroring (PLS-00382: expression is of wrong type
END LOOP;


I have shared all the steps/scripts and session is attached. I tried multiple ways to change that statement but that doesn't seem to be working (details in attached session) or the assignment statement but unable to find out after different trial and errors. So need your advise only the correct way of assigning the values into table type variables.

SQL> create or replace type exch_row as object (
  2      person_id NUMBER);
  3
  4  /

Type created.

SQL>
SQL> create or replace type exch_tbl as table of exch_row;
  2  /

Type created.

SQL>
SQL>
SQL> DECLARE
  2   l_row     exch_row;
  3   exch_rt   exch_tbl;
  4   depts_max NUMBER;
  5
  6  CURSOR csr1
  7  IS
  8   select 100 person_id from dual;
  9
 10  BEGIN
 11    depts_max := 0;
 12    exch_rt :=  exch_tbl();
 13
 14   for v IN csr1
 15   LOOP
 16      depts_max := depts_max + 1;
 17      exch_rt.EXTEND;
 18      exch_rt(depts_max) := exch_row(v.person_id);      <============= object
 19   END LOOP;
 20  END;
 21  /

PL/SQL procedure successfully completed.