Skip to Main Content
  • Questions
  • Updating and returning a nested table via OUT parameter

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stephen.

Asked: August 18, 2020 - 2:21 pm UTC

Answered by: Chris Saxon - Last updated: August 19, 2020 - 8:24 am UTC

Category: PL/SQL - Version: 19c / Autonomous Transaction Processing database

Viewed 100+ times

You Asked

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.


and we said...

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


So I'm not sure what the issue is. Please provide a complete example - all the code, create tables, inserts, etc.

and you rated our response

  (3 ratings)

Reviews

August 18, 2020 - 3:42 pm UTC

Reviewer: A reader from PLANO TX USA

I would never get to running it, because my code is generating those errors in the CREATE PACKAGE BODY (OR CREATE PROCEDURE) compile.

I did notice that you declared your package table with the INDEX BY PLS_INTEGER clause, which I didn't use.

type rec_arr is table of rec
    index by pls_integer;


So I changed the package spec to do that when declaring the table.

    type tab_DT_REWARD_POOL is table of rec_DT_REWARD_POOL_ROW INDEX BY PLS_INTEGER;


This time I got a different error message: PLS-00597: expression 'P_AVAILABLE_REWARD' in the INTO list is of wrong type.

Chris Saxon

Followup  

August 18, 2020 - 5:03 pm UTC

It's OK if it doesn't compile, show us your code!

Everything we need to copy-paste it and see exactly the same behaviour you do.

Ideally remove everything unnecessary to your actual problem.

This makes it significantly easier for us to give you effective help. Help us help you!

Response isn't actually a required field

August 18, 2020 - 3:44 pm UTC

Reviewer: Stephen Nelson from Plano TX USA.

I would have rated your answer higher, but I forgot to click the field, and the page let me submit.

August 18, 2020 - 10:22 pm UTC

Reviewer: Stephen Nelson from Plano TX USA.

Guys, I found the solution with the help of another Oracle Consultant . Here's a link to it in liveSQL:

https://livesql.oracle.com/apex/livesql/s/kjcuh7ldaufw8y05edbiivdkz

The key was changing the select so that instead of selecting using the column list from the actual table, we effectively CAST it to the record type declared at the schema level (making the bolded changes), and then defined the table being passed back out as another schema type:

create or replace procedure test_reward_type 
is 
    p_mic_reward_tab tab_dt_reward_pool; 
begin 
        SELECT 
        <b>rec_dt_reward_pool_row(dt_rp_id, dt_rp_prog_id, dt_rp_tp_id, dt_rp_type_code, dt_rp_value) 
        BULK COLLECT </b>
        INTO p_mic_reward_tab 
        FROM 
            reward_pool;  
end; 


Thanks for your responses, since that helped us get our minds wrapped around this.

World's best debugger: another set of eyeballs.
Chris Saxon

Followup  

August 19, 2020 - 8:24 am UTC

Glad you got it sorted

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.