Oracle procedure error: Expected UDT got CHAR

With Oracle 19c, I am trying to write a stored procedure. I have cut a lot of other code out, but in it's simplest form, I need to run some dynamic SQL, and then return the value in a record. The error I am getting on run (it compiles fine) is:

ORA-00932: inconsistent datatypes: expected UDT got CHAR

My code is:

create or replace TYPE rep_mapping_object as OBJECT
  ( REGISTRY_ID varchar2(20 BYTE )
);

CREATE OR REPLACE TYPE rep_mapping_record IS TABLE OF rep_mapping_object;

create or replace PROCEDURE my_test( vROW OUT rep_mapping_record )
IS 
mySQL varchar2(2000); 
BEGIN

mySQL := 'select registry_id from src_territory_assignment fetch first row only';

execute immediate mySQL into vROW;
END;

When this is solved, my rep_mapping_object will have a lot more columns, as will the SQL statement. The SQL statement will only return either 0 or 1 row.

What am I doing wrong?

Solution

Here's how:

Sample types and table:

SQL> create or replace type rep_mapping_object as object
  2    ( registry_id varchar2(20 byte )
  3  );
  4  /

Type created.

SQL> create or replace type rep_mapping_record is table of rep_mapping_object;
  2  /

Type created.

SQL> create table src_territory_assignment as
  2    select 'USA' registry_id from dual union all
  3    select 'FRA'             from dual;

Table created.

Procedure; see lines #5 and 6:

SQL> create or replace procedure my_test( vrow out rep_mapping_record )
  2  is
  3    mysql varchar2(2000);
  4  begin
  5    mysql := 'select rep_mapping_object(registry_id) from src_territory_assignment fetch first row only';
  6    execute immediate mysql bulk collect into vrow;
  7  end;
  8  /

Procedure created.

Testing:

SQL> set serveroutput on
SQL> declare
  2    l_result rep_mapping_record;
  3  begin
  4    my_test (l_result);
  5    dbms_output.put_line(l_result(1).registry_id);
  6  end;
  7  /
USA

PL/SQL procedure successfully completed.

SQL>