Oracle PL/SQL - How to create a simple array variable?

You can use VARRAY for a fixed-size array:

declare   type array_t is varray(3) of varchar2(10);   array array_t := array_t('Matt', 'Joanne', 'Robert');begin   for i in 1..array.count loop       dbms_output.put_line(array(i));   end loop;end;

Or TABLE for an unbounded array:

...   type array_t is table of varchar2(10);...

The word "table" here has nothing to do with database tables, confusingly. Both methods create in-memory arrays.

With either of these you need to both initialise and extend the collection before adding elements:

declare   type array_t is varray(3) of varchar2(10);   array array_t := array_t(); -- Initialise itbegin   for i in 1..3 loop      array.extend(); -- Extend it      array(i) := 'x';   end loop;end;

The first index is 1 not 0.

You could just declare a DBMS_SQL.VARCHAR2_TABLE to hold an in-memory variable length array indexed by a BINARY_INTEGER:

DECLARE   name_array dbms_sql.varchar2_table;BEGIN   name_array(1) := 'Tim';   name_array(2) := 'Daisy';   name_array(3) := 'Mike';   name_array(4) := 'Marsha';   --   FOR i IN name_array.FIRST .. name_array.LAST   LOOP      -- Do something   END LOOP;END;

You could use an associative array (used to be called PL/SQL tables) as they are an in-memory array.

DECLARE   TYPE employee_arraytype IS TABLE OF employee%ROWTYPE        INDEX BY PLS_INTEGER;   employee_array employee_arraytype;BEGIN   SELECT *     BULK COLLECT INTO employee_array     FROM employee    WHERE department = 10;   --   FOR i IN employee_array.FIRST .. employee_array.LAST   LOOP      -- Do something   END LOOP;END;

The associative array can hold any make up of record types.

Hope it helps,Ollie.

You can also use an oracle defined collection

DECLARE   arrayvalues sys.odcivarchar2list;BEGIN  arrayvalues := sys.odcivarchar2list('Matt','Joanne','Robert');  FOR x IN ( SELECT m.column_value m_value               FROM table(arrayvalues) m )  LOOP    dbms_output.put_line (x.m_value||' is a good pal');  END LOOP;END;

I would use in-memory array. But with the .COUNT improvement suggested by uziberia:

DECLARE  TYPE t_people IS TABLE OF varchar2(10) INDEX BY PLS_INTEGER;  arrayvalues t_people;BEGIN  SELECT *   BULK COLLECT INTO arrayvalues   FROM (select 'Matt' m_value from dual union all         select 'Joanne'       from dual union all         select 'Robert'       from dual    )  ;  --  FOR i IN 1 .. arrayvalues.COUNT  LOOP    dbms_output.put_line(arrayvalues(i)||' is my friend');  END LOOP;END;

Another solution would be to use a Hashmap like @Jchomel did here.

NB:

With Oracle 12c you can even query arrays directly now!