CODEWITHSUNDEEP
oracle-databaseplsql
Nik
Nik

Reputation:

How to populate varray multiple times

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.

Upvotes: 0

Views: 2704

Answers (2)

DCookie
DCookie

Reputation: 43533

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...

Upvotes: 1

akf
akf

Reputation: 39485

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.

Upvotes: 0

Related Questions