Re: PlL/SQL and Array Question

From: Peter H. Larsen <plarsen_at_dc.dynares.com>
Date: Wed, 10 Jun 1998 16:41:15 -0400
Message-ID: <357EEF6A.D9FE400E@dc.dynares.com>

Using typedef, yep - you can ON THE SERVER (NOT in the Oracle tools) pass whole arrays to a function. Requirement: Define your procedure in a package, define type type as an internal or external depending on your requirements, and then use that type in your parameter declaretion. Restrictions: You cannot call this function from PL/SQL version 1.1 ei. all the client tools from Oracle.

To LOOP thru an array is difficault because as a matter of fact, the "array" is considered an inifite table by the PL/SQL engine. You can have items in place nr. 5, 10 and 10000 and all others are empty and will yield NO DATA FOUND if requested. If you know your max-limit of the array (most programs operate with that) then simple loop through the array:

DECLARE
   TYPE IDX IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;    AR IDX;
   I NUMBER;
  MAXITEM NUMBER := 10000;
  FOUND NUMBER := 0;
BEGIN
   FOR I IN 1.. MAXITEM
   LOOP

      begin
         if AR(I) = 0 THEN
            -- Found an item
            found := found + 1;
         end if;
         exception
           when no_data_found then null; -- No element defined
      end;

   end loop;

A better way is thinking "objective", and encapsulating your array in a package, and in that package count the number of elements as they are assigned. Unfortunately you cannot make multible instances of this "object" in Oracle7 ... but Oracle8 can!

Hopes this helps a bit ...

• Peter H. Larsen Oracle Consultant

Sean Dolan wrote:

> I am trying to pass an array into a stored function on an Oracle 7.3.4 server. First, can I pass an array to a function?
>
> Second, is there any command/syntax for looping through all the items in the array? For example,... FOR EACH ITEM IN ARRAY blah,blah, blah... or is there a count function to find the number of items in the array so I can do a LOOP that way?
>
> Thanks,
> Sean Dolan
Received on Wed Jun 10 1998 - 15:41:15 CDT