What is causing the 'invalid datatype' error when using MERGE statement with Oracle PL/SQL table types?

MERGE using Oracle PL/SQL table type

I want to create/update records using MERGE statement. But getting an error "Invalid datatype".

Table setup

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));
                        
                        
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');

Record type

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;
/

Code that is giving error

set SERVEROUTPUT on;
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;
        
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;
/

Error

00902. 00000 -  "invalid datatype"
*Cause:    
*Action:

Please help me resolve this issue.
Appreciate if you could suggest a better way of doing it if any.

Restriction on usage of PL/SQL types is listed in Changes in Oracle Database 12c Release 1 (12.1).

> More PL/SQL-Only Data Types Can Cross PL/SQL-to-SQL Interface
> ---
>
> As of Oracle Database 12c, it is possible to bind values with PL/SQL-only data types to anonymous blocks (which are SQL statements), PL/SQL function calls in SQL queries and CALL statements, and the TABLE operator in SQL queries. However:
> - ...
> - If the PL/SQL-only data type is an associative array, it cannot be used within a non-query DML statement (INSERT, UPDATE, DELETE, MERGE) nor in a subquery

You may use FORALL statement to perform upsert from a collection:

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

fiddle

However, it would be better to keep everything at SQL level and do not move the data around with context swithing if you do not perform any extra PL/SQL processing of the fetched data.

As a frame challenge, PL/SQL and custom types are unnecessary and you can do it all in SQL:

merge into ps_test02 tab2
using (
  select id, name ,active_flag from ps_test01 where active_flag='Y'
) 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);

fiddle

> I should be able to have multiple queries that can populate test_tab, that would help me to create/update records in ps_test02 table.

If you want to use multiple tables then you can use UNION ALL to select from multiple tables in the USING clause.

Create a SQL datatype rather than a PL/SQL one:

CREATE OR REPLACE TYPE test_rec AS OBJECT
   (
      id    number,
      name  varchar2(30),
      active_flag   varchar2(1)
   );

CREATE OR REPLACE TYPE test_tab_type AS TABLE OF test_rec;

Then in your DECLARE section remove the reference to the package:

test_tab test_tab_type;

Now you can use test_tab in SQL statements.