Re: Use of an array NOT number cause error

From: Chris L. <diversos_at_uol.com.ar>
Date: Fri, 06 Jul 2007 08:48:19 -0700
Message-ID: <1183736899.147528.221610@57g2000hsv.googlegroups.com>

On Jul 6, 9:29 am, Mariano <mariano.calan..._at_gmail.com> wrote:
> Then I've this function:
>
> ======================================
>
> FUNCTION "SEMIAUTO"(px NUMBER)
>
> RETURN automatiche AS
>
> results automatiche := automatiche();
>
> CURSOR c_class IS
> SELECT id_mal, SUM(percent) AS p
> FROM malattie_sintomi
> WHERE id_sin IN (px)
> and id_mal in (select id_mal from malattie_sintomi group by
> id_mal having count(*) >= 1)
> GROUP BY id_mal;
>
> BEGIN
> FOR v_Rec IN c_class LOOP
> results.EXTEND;
> results(results.LAST) := automatica(v_Rec.id_mal,
> v_Rec.p);
> END LOOP;
> RETURN results;
> END;
>
> ======================================
>
> As you could see, this function accept a NUMBER in input, this number
> will be a parameter of my cursor c_class.
> But number it's not usefull for my job, I need to pass an unknow
> number of NUMBER (sorry for words joke :D) a sort of array.
>
> I've write:
>
> type sinArray is table of number;
>
> and now header of my function is something like
>
> FUNCTION "SEMIAUTO"(px SINARRAY)
>
> But in this way i have a conflict and a message advise me that is
> needed NUMBER not SINARRAY.
>
> What I can do?

Here's how to use an "associative array" in a function call

(Taken from

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm#BEIEBJJI

)

DECLARE
TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;

  n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  sn PLS_INTEGER := 10;  -- number of multiples to sum
  m  PLS_INTEGER := 3;   -- multiple

FUNCTION get_sum_multiples(multiple IN PLS_INTEGER, num IN PLS_INTEGER)
  RETURN sum_multiples IS
  s sum_multiples;
  BEGIN

      FOR i IN 1..num LOOP
        s(i) := multiple * ((i * (i + 1)) / 2) ; -- sum of multiples
      END LOOP;

    RETURN s;
  END get_sum_multiples;
BEGIN
-- call function to retrieve the element identified by subscript (key)   DBMS_OUTPUT.PUT_LINE('Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||

TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n)));
END;
/