Re: Oracle 8.1.5 on NT - PL/SQL Arrays as Parameters

First turn to the Oracle documentation on your 8.1.5 CD for NT. It's pretty good and has examples. Next, if you've got the cash, invest in a PL/SQL programming book from Urman or Feuerstein. They're fantastic and have paid for themselves many times over. Now that I'm done lecturing, here's your answer:

The array (PL/SQL "index by" table) of records type must be visible to the packaged object declaring a parameter based upon it. This type can be declared either in its own body, the global area of the package body, its own package spec, or within some other package spec. Then you just declare a parameter to be of that type (how to declare it with a default of NULL is another lesson).

I'm doing this at home, so no guarantee the following will run on the first shot.

create or replace package mytypes
as
type myrec is record (myid number(10), mytext varchar2(30)); type myarray is table of myrec index by binary integer; end mytypes;
/
create or replace package test
as
procedure mytest (iar_data IN mytypes.myarray); end test;
/
create or replace package body test
as
procedure mytest (iar_data IN mytypes.myarray) is
begin

    for i in 1..iar_data.COUNT loop

• do something here with each iar_data(i).myid and iar_data(i).mytext end loop; end mytest; end test; /

best of luck,
bill c.

"Mark Tompkins" <mark.tompkins_at_digital-dispatch.com> wrote in message news:38BDA75A.A01D0C15_at_digital-dispatch.com...
> Hi,
>
> Anyone know how to pass a variable array of records as a parameter in a
> PL/SQL stored procedure?
>
> The array dimension > 1 (wide).
>
> The array length would vary (n records).
>
> thx
>
> thx
>
> Mark
>
>
>
Received on Wed Mar 01 2000 - 00:00:00 CST