2 Replies Latest reply: Dec 27, 2012 10:38 AM by 981904 RSS

    Associative array of records index by varchar2 - PLS-00382

    981904
      I have an anonymous SQL block that builds a report. At the end of each section I need to summarize the data by "location". The list of locations is finite (approx 15 in a table) and not all sections will have data for each location, however I want the locations to be ordered the same so I thought I would use an associative array to store the summary data. However when I attempt to initialize the associative array (I'm simply putting the locations in the array in the "correct" order) I get PLS-00382 expression is of wrong type.

      Here's the code (the lines where the error occurs are in bold):

      TYPE summary_data IS RECORD (
      summary1 NUMBER,
      summary2 NUMBER,
      summary3 NUMBER
      );

      TYPE summ_arr IS TABLE of summary_data INDEX BY VARCHAR2(5);

      location_summary summ_arr;

      aLoc VARCHAR2(5);

      PROCEDURE init_summ(aSummArr IN OUT summ_arr) IS
      BEGIN
      aSummArr.Delete;

      BEGIN
      FOR aLoc in (select name -- name is VARCHAR2(5)
      from table
      order by key)
      LOOP
      aSummArr(aLoc).summary_1 := 0;
      aSummArr(aLoc).summary_2 := 0;
      aSummArr(aLoc).summary_3 := 0;
      END LOOP;
      EXCEPTION
      -- exception handling
      END;

      END;

      Is this not allowed or am I doing something wrong? I've tried substituting:

      TYPE summ_arr IS TABLE of summary_data INDEX BY VARCHAR2(5);

      with

      TYPE summ_arr IS TABLE of summary_data INDEX BY table.key%TYPE;

      to no avail.

      Thanks in advance for your assistance with this.

      BG.