How to populate varray multiple times

Question

I have a script that uses one VARRAY multiple times. But, I can't seem to figure out how to reset the VARRAY after looping through it once. I wrote the following basic script to help me troubleshoot:

DECLARE
   TYPE multi_show_id_type IS VARRAY (60) OF VARCHAR2 (10);

   multi_show_id multi_show_id_type := multi_show_id_type ();
   counter       NUMBER := 1;
   i             NUMBER := 1;
BEGIN
   DBMS_OUTPUT.put_line ('BEGIN');

   WHILE i < 10
   LOOP
   DBMS_OUTPUT.put_line (i);
   --counter:=0;
   --multi_show_id :=multi_show_id_type();
   --multi_show_id.delete;

   WHILE counter < 25
   LOOP
   multi_show_id.EXTEND ();
   multi_show_id (counter) := counter * counter;
   DBMS_OUTPUT.put_line ('VArray: [' || counter || '] [' || multi_show_id (counter) || ']');

   counter := counter + 1;
   END LOOP;
   i := i + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('END');
END;
/

This script works when it is only looping through the array once. But if you uncomment the counter:=0 line, which forces it to loop through the array population loop 10 times, I get an ORA-06532 error. You can see some of the stuff I've tried in the other commented lines. Any help would be appreciated.

Answer 1

there seems to be two problems here. first, the VARRAY index starts at 1. second, you will stop once your VARRAY is full at 60 items, as defined in your declaration.

use the following:

TYPE multi_show_id_type IS VARRAY (250) OF VARCHAR2 (10);

and

counter:=1;

uncomment the multi_show_id :=multi_show_id_type(); line if you want to start at 1 for each loop. if you want to ensure that no more than 4 values, your inner while loop should make that restriction.

Answer 2

Actually, @akf is correct; your code as written won't work because a VARRAY starts at item 1, not zero.

Change your code thusly and it works:

   ...
   LOOP
          DBMS_OUTPUT.put_line (i);
          counter:=1;
          --multi_show_id :=multi_show_id_type();
          multi_show_id.delete;

          WHILE counter < 26
          LOOP
            ...

EDIT: if you want to run thru the loop 25 times you do need to change the WHILE loop upper bound to 26...