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"

Are you sure you need a varray, and not a nested table? Is that 26 limit so overwhelmingly useful that it makes up for the loss of the functionality that you get with nested tables? — William Robertson, Sep 22 '16 at 13:54
@William_Robertson VArray maintains element order, whereas nested tables and associative arrays do not. In applications where element order is necessary, varray is your only option. — JeramieH, Feb 15 '18 at 21:04

Alex Poole · Accepted Answer · 2016-09-22T12:14:58.313

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.

Thank you so much for your kind help with this perfect answer to resolve my issue. Thanks again! — Rejwanul Reja, Sep 04 '22 at 11:58

0xdb · Answer 2 · 2016-09-28T10:06:19.560

You must initialize the varray with a constructor and extend it before assigning the values to the particular elements of a collection variable. In a PLSQL context, it is better to use the associative collections.

declare
    type code_array_ is varray(26) of varchar2(6);
    codes_ code_array_ := code_array_();
begin
    <<init_codes>> begin codes_.extend(26);
        for i in codes_.first..codes_.last loop codes_(i) := dbms_random.string('U',6);
    end loop; end init_codes;
end;
/

Or you can use sql to initialize whole collection, e.g. something like this:

declare
    type code_array_ is varray(26) of varchar2(6);
    codes_ code_array_;
begin
    select dbms_random.string('U',6) bulk collect into codes_ from dual connect by level<=26;
end;
/

How to add values to a VARRAY using a loop

2 Answers2

Linked