How to add values to a VARRAY using a loop

Question

I have a VARRAY and I want to add elements to this VARRAY by using a loop. This is what I have tried so far.

DECLARE
TYPE code_array_  IS VARRAY(26) OF VARCHAR2(6);
codes_ code_array_;

BEGIN
FOR i IN 1..26 LOOP    
    codes_(i) := dbms_random.string('U',6);
  END LOOP;
END;

Above code gives me an error

"ORA-06531: Reference to uninitialized collection"

Answer 1

As the error message says, you need to initialise the collection variable:

...
BEGIN
  codes_ := code_array_();
  ...

But you also need to size it, either with a single extension each time around the loop:

  FOR i IN 1..26 LOOP    
    codes_.extend;
    ...

Or a one-off extension before you start:

...
BEGIN
  codes_ := code_array_();
  ...
  codes_.extend(26);
  FOR i IN 1..26 LOOP    
    ...

You could also use the post-extend size to control the loop, to save hard-coding 26 again:

DECLARE
  TYPE code_array_ IS VARRAY(26) OF VARCHAR2(6);
  codes_ code_array_;
BEGIN
  codes_ := code_array_();
  codes_.extend(26);
  FOR i IN 1..codes_.count LOOP    
    codes_(i) := dbms_random.string('U',6);
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

Read more about collections.