I’m trying to create and fill a nested table with a SELECT… INTO, to be returned as an out parameter from a PL/SQL PROCEDURE. I’ve declared the record and table at both the package level and the schema level as follows:
In the package:

   type rec_DT_REWARD_POOL_ROW is record (
      "DT_RP_ID" NUMBER(24,0), 
                  "DT_RP_PROG_ID" NUMBER(10,0), 
      "DT_RP_TP_ID" VARCHAR2(18), 
                  "DT_RP_TYPE_CODE" NUMBER(10,0), 
                  "DT_RP_VALUE" FLOAT(126));
type tab_DT_REWARD_POOL is table of rec_DT_REWARD_POOL_ROW;

I’ve also declared it at the schema level

CREATE OR REPLACE TYPE REC_DT_REWARD_POOL_ROW AS OBJECT 
( "DT_RP_ID" NUMBER(24,0), 
                  "DT_RP_PROG_ID" NUMBER(10,0), 
      "DT_RP_TP_ID" VARCHAR2(18), 
                  "DT_RP_TYPE_CODE" NUMBER(10,0), 
                  "DT_RP_VALUE" FLOAT(126));
CREATE OR REPLACE TYPE tab_DT_REWARD_POOL 
AS TABLE OF REC_DT_REWARD_POOL_ROW;

My Procedure (standalone and package) is

PROCEDURE REWARDS_DB_PROD.RETRIEVE_REWRADS(
    TOKEN REWARDS_DB_PROD.DT_REWARD_POOL.DT_RP_TP_ID%TYPE,
        P_Available_Reward OUT tab_DT_REWARD_POOL,
        P_MIC_REWARD       OUT tab_DT_REWARD_POOL) AS

However, when I try to use the following select into:

     SELECT DT_RP_ID ,
DT_RP_PROG_ID ,
DT_RP_TP_ID ,
DT_RP_TYPE_CODE ,
DT_RP_VALUE 
INTO p_mic_reward
FROM REWARD_POOL
WHERE ……;

I’m not returning the whole table.
The package declared one gets a PLS-00642 error: Local collection types not allowed in SQL.
The schema declared one tells me ORA-00947: Not enough values.

Since the record and Object types had their field lists lifted directly from the CREATE TABLE for REWARD_POOL, I don’t understand what’s going on.

create or replace package pkg as 
  type rec is record (
    a1 int, a2 date
  );
  type rec_arr is table of rec
    index by pls_integer;
    
  procedure p ( obj out rec_arr );
end;
/

create or replace package body pkg as 

  procedure p ( obj out rec_arr ) as
  begin
    select level, date'2020-01-01' + level
    bulk   collect 
    into   obj
    from   dual
    connect by level <= 5;
  end;

end;
/

declare
  arr pkg.rec_arr;
begin
  pkg.p ( arr );
  
  for i in 1 .. arr.count loop
    dbms_output.put_line ( 
      'a1 = ' || arr(i).a1 ||
      ' a2 = ' || arr(i).a2
    );
  end loop;
end;
/

a1 = 1 a2 = 02-JAN-2020 00:00:00
a1 = 2 a2 = 03-JAN-2020 00:00:00
a1 = 3 a2 = 04-JAN-2020 00:00:00
a1 = 4 a2 = 05-JAN-2020 00:00:00
a1 = 5 a2 = 06-JAN-2020 00:00:00