Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ps_test01 (id Number primary key,
                        name varchar2(30),
                        active_flag VARCHAR2(1));
CREATE TABLE ps_test02 (id Number primary key,
                        name varchar2(30));
begin
insert into ps_test01 (id, name, active_flag) values (1, 'test01', 'Y');
insert into ps_test01 (id, name, active_flag) values (2, 'test02', 'Y');
insert into ps_test01 (id, name, active_flag) values (3, 'test03', 'Y');

insert into ps_test02 (id, name) values (1, 'test001');
commit;
end;/
1 rows affected

create or replace package test_pkg as

TYPE test_rec IS RECORD
   (
      id    number,
      name  varchar2(30),
      active_flag   varchar2(1)
   );
   
   TYPE test_tab_type IS TABLE OF test_rec;

end test_pkg;
/
declare
    l_test varchar(10);
    
    cursor cur_ps_test01 IS
        select id, name ,active_flag from ps_test01 where active_flag='Y';
        
    --TYPE test_tab_type is TABLE OF cur_ps_test01%ROWTYPE index by pls_integer;
    test_tab test_pkg.test_tab_type;
    test_tab2 test_pkg.test_tab_type;
    var number;
begin
    
    open cur_ps_test01;
    fetch cur_ps_test01 bulk collect into test_tab;
    close cur_ps_test01;
    
    dbms_output.put_line('number of rows fetched : ' || test_tab.count); 
    
    select * bulk collect into test_tab2 from table(test_tab);
    dbms_output.put_line('number of rows fetched : ' || test_tab2.count); 

    
    merge into ps_test02 tab2 -- error was reported on this line
        using ( select id,name,active_flag from table(test_tab) ) tab1
        on (tab1.id = tab2.id)
        when matched then
            update set name = tab1.name
        when not matched then
            insert (id, name) values (tab1.id, tab1.name);
    
end;
/


dbms_output:
number of rows fetched : 3
number of rows fetched : 3

ORA-00902: invalid datatype
ORA-06512: at line 23

declare
    l_test varchar(10);
    
    cursor cur_ps_test01 IS
        select id, name ,active_flag from ps_test01 where active_flag='Y';
        
    --TYPE test_tab_type is TABLE OF cur_ps_test01%ROWTYPE index by pls_integer;
    test_tab test_pkg.test_tab_type;
    test_tab2 test_pkg.test_tab_type;
    var number;
begin
    
    open cur_ps_test01;
    fetch cur_ps_test01 bulk collect into test_tab;
    close cur_ps_test01;
    
    dbms_output.put_line('number of rows fetched : ' || test_tab.count); 
    
    select * bulk collect into test_tab2 from table(test_tab);
    dbms_output.put_line('number of rows fetched : ' || test_tab2.count); 

    forall i in test_tab.first..test_tab.last
      merge into ps_test02 tab2 -- error was reported on this line
      using (
        select
          test_tab(i).id as id,
          test_tab(i).name as name,
          test_tab(i).active_flag as active_flag
        from dual
      ) tab1
        on (tab1.id = tab2.id)
      when matched then
        update set name = tab1.name
      when not matched then
        insert (id, name) values (tab1.id, tab1.name);
    
end;
/
1 rows affected

dbms_output:
number of rows fetched : 3
number of rows fetched : 3

select *
from ps_test02


ID
NAME


1
test01

2
test02

3
test03