Re: Index-by tables as stored procedure parameters?

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Wed, 27 Sep 2000 00:21:51 -0500
Message-ID: <39D183EE.43C75879@ntsource.com>

The following is a package specification and body that perhaps illustrates what you are trying to do.

Besides adding a body to the procedure I made these changes to your code: (1) "index by binary_integer" was added to the type statements since you wanted an "index-by" table, and
(2) "answer_id_array" was changed to "answer_array" to match the name of the type.

code starts ------

create or replace package ttt is

    TYPE question_id_array
    IS
    TABLE OF NUMBER index by binary_integer;

    TYPE answer_array
    IS
    TABLE OF VARCHAR2(4000) index by binary_integer;

    PROCEDURE StoreSurveyAnswer(

      survey_name_in       IN survey.survey_name%TYPE
    , user_uuid_in         IN resources.uuid%TYPE
    , question_id_array_in IN question_id_array
    , answer_array_in      IN answer_array

    );
end;
/
show errors

create or replace package body ttt is

    PROCEDURE StoreSurveyAnswer(

      survey_name_in       IN survey.survey_name%TYPE
    , user_uuid_in         IN resources.uuid%TYPE
    , question_id_array_in IN question_id_array
    , answer_array_in      IN answer_array

    )
   is

      v_num number;
   begin

      if question_id_array_in.count > 0 then
         for i in question_id_array_in.first .. question_id_array_in.last
loop
            v_num := answer_array_in(i);
         end loop;
      end if;

   end;
end;
/
show errors

code ends -----

Frank Hubeny

David Fetter wrote:

> Kind people,
>
> I'm writing some code for a general survey engine, and have the
> following stub:
>
> TYPE question_id_array
> IS
> TABLE OF NUMBER;
>
> TYPE answer_array
> IS
> TABLE OF VARCHAR2(4000);
>
> PROCEDURE StoreSurveyAnswer(
> survey_name_in IN survey.survey_name%TYPE
> , user_uuid_in IN resources.uuid%TYPE
> , question_id_array_in IN question_id_array
> , answer_array_in IN answer_id_array
> );
>
> How do I iterate over all the questions and get the corresponding
> answers? Or should the answer_array be indexed by the question_id
> numbers? Either way, how do I go through all of 'em?
>
> Huge TIA for any tips, pointers or FM's to RT.
>
> Cheers,
> D
> --
> David Fetter 888 O'Farrell Street Apt E1205
> david_at_fetter.org San Francisco, CA 94109-9046
> http://fetter.org/~shackle phone: +1 415 567 2690 fax: +1 415 567 2340
>
> I planted some bird seed. A bird came up. Now I don't know what to
> feed it.
> Steven Wright
  Received on Wed Sep 27 2000 - 00:21:51 CDT