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 (
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),
CREATE OR REPLACE TYPE tab_DT_REWARD_POOL
AS TABLE OF REC_DT_REWARD_POOL_ROW;
My Procedure (standalone and package) is
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 ,
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.
The following works for me:
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 );
create or replace package body pkg as
procedure p ( obj out rec_arr ) as
select level, date'2020-01-01' + level
connect by level <= 5;
pkg.p ( arr );
for i in 1 .. arr.count loop
'a1 = ' || arr(i).a1 ||
' a2 = ' || arr(i).a2
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
So I'm not sure what the issue is. Please provide a complete example - all the code, create tables, inserts, etc.