Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> populating a nested collection in an object type

populating a nested collection in an object type

From: Keith Devlin <keith.devlin_at_sambusys.com>
Date: 24 Oct 2002 05:55:20 -0700
Message-ID: <491d9027.0210240455.6cb6f192@posting.google.com>


Hi

I'm trying to pass through a user defined object type as a PL/SQL parameter. The object type has a sub collection though and I am unsure how to populate the parameter.

I have created the following objects:

create or replace type mt535bal as object( isin varchar2(12) , amount number );

create or replace type mt535bals as table of mt535bal;

create or replace type mt535hold as object( safekeeping varchar2(14) , mt535bals mt535bal );

create or replace type mt535holds as table of mt535hold;

so far so good!

My PL/SQL procedure is thus:

      1 create or replace procedure main_prog as
      2
      3 v_holding   mt535bals := mt535bals();
      4 v_mt535holds    mt535holds := mt535holds() ;
      5 i           number;
      6 x           number;
      7 begin
      8 for x in 1..2 loop
      9     v_mt535holds.extend;
     10     for i in 1..3 loop
     11       v_holding.extend;
     12       v_holding(i) := mt535bal ('GBP', 100+i);
     13       
     14     end loop;
     15     v_mt535holds(x) := mt535hold(x, v_holding);
     16 
     17 end loop;
     18 sub_prog(v_mt535holds);
     19 end;
     19 /
     20 show errors;   

The problem is line 15 - the reference to v_holding. But I'not sure how to populate v_mt535holds with the sub collection in the variable v_holding!

The error is
14/22 PLS-00306: wrong number or types of arguments in call to

         'MT535HOLD' Thanks Received on Thu Oct 24 2002 - 07:55:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US