Re: How to call Oracle Stored Poc with an array as a param

From: bigrez <bigrez_at_mail.hughes.net>
Date: Tue, 27 Mar 2001 16:04:08 -0800
Message-ID: <CT9w6.1379$NAC.20250814@news.randori.com>

---

/* You'll need a package to define the array type before the procedure

    has been created. */

create or replace package my_package as

   /* Define the array type */
   type arrayType is table of varchar2(20) index by binary_integer;

   /* A couple of procedures to show usage. */    procedure callingProc;
   procedure calledProc(parmCount integer, parmArray arrayType); end my_package;

create or replace package body my_package as

/* The first procedure which fills and passes data */ procedure callingProc is
begin

   localArray arrayType;

   localArray(1) := 'first';
   localArray(2) := 'second';
   localArray(3) := 'third';
   localArray(4) := 'fourth';
   localArray(5) := 'fifth';

   calledProc(5,localArray);
end callingProc;

/* This procedure receives the array */
procedure calledProc(parmCount integer, parmArray arrayType) is begin

   /* Loop through the array. In this case, I've passed in the number of
    * elements but you could do it some other way if needed.
    */
   for index in 1..parmCount loop
         dbms_output.put_line('First array element says ' ||

parmArray(index));

   end loop;

end;
end my_package;

---

I'm doing this from scratch, so there's probably a syntax error or two in there. ;)

HTH, mike

Anthony Smales wrote in message <99ngek$45g$1_at_taliesin.netcom.net.uk>...
>How can I call an Oracle Stored Procedure, if one of the parameters is an
>array?? I need the exact syntax.
>
>Thanks
>
>
Received on Tue Mar 27 2001 - 18:04:08 CST