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

I'd like to create an in-memory array variable that can be used in my PL/SQL code. I can't find any collections in Oracle PL/SQL that uses pure memory, they all seem to be associated with tables. I'm looking to do something like this in my PL/SQL (C# syntax):

string[] arrayvalues = new string[3] {"Matt", "Joanne", "Robert"};

Edit: Oracle: 9i

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 it
begin
   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!

Another solution is to use an Oracle Collection as a Hashmap:

declare 
-- create a type for your "Array" - it can be of any kind, record might be useful
  type hash_map is table of varchar2(1000) index by varchar2(30);
  my_hmap hash_map ;
-- i will be your iterator: it must be of the index's type
  i varchar2(30);
begin
  my_hmap('a') := 'apple';
  my_hmap('b') := 'box';
  my_hmap('c') := 'crow';
-- then how you use it:

  dbms_output.put_line (my_hmap('c')) ;
  
-- or to loop on every element - it's a "collection"
  i := my_hmap.FIRST;
  
  while (i is not null)  loop     
    dbms_output.put_line(my_hmap(i));      
    i := my_hmap.NEXT(i);
  end loop;

end;

Sample programs as follows and provided on link also https://oracle-concepts-learning.blogspot.com/

plsql table or associated array.

		DECLARE 
			TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
			salary_list salary; 
			name VARCHAR2(20); 
		BEGIN 
		   -- adding elements to the table 
		   salary_list('Rajnish') := 62000; salary_list('Minakshi') := 75000; 
		   salary_list('Martin') := 100000; salary_list('James') := 78000; 
		   -- printing the table name := salary_list.FIRST; WHILE name IS NOT null 
			LOOP 
			   dbms_output.put_line ('Salary of ' || name || ' is ' || 
			   TO_CHAR(salary_list(name))); 
			   name := salary_list.NEXT(name); 
			END LOOP; 
		END; 
		/

Using varray is about the quickest way to duplicate the C# code that I have found without using a table.

Declare your public array type to be use in script

	type t_array is varray(10) of 			varchar2(60);

This is the function you need to call - simply finds the values in the string passed in using a comma delimiter

function ConvertToArray(p_list IN VARCHAR2)
   RETURN t_array
 AS
	myEmailArray t_array := t_array(); --init empty array
	
	l_string       				varchar2(1000) := p_list || ','; - (list coming into function adding final comma)
	l_comma_idx  				integer;
	l_index        				integer := 1;
	l_arr_idx        			integer := 1;
	l_email 					varchar2(60);
	
 BEGIN
	
	LOOP
		l_comma_idx := INSTR(l_string, ',', l_index);
		EXIT WHEN l_comma_idx = 0;
		
		l_email:= SUBSTR(l_string, l_index, l_comma_idx - l_index);
		dbms_output.put_line(l_arr_idx || ' - ' || l_email);
		
		myEmailArray.extend;
		myEmailArray(l_arr_idx) := l_email; 
		
		l_index := l_comma_idx + 1;
		l_arr_idx := l_arr_idx + 1;
	END LOOP;
   
   for i in 1..myEmailArray.count loop
       dbms_output.put_line(myEmailArray(i));
   end loop;
   
   dbms_output.put_line('return count ' || myEmailArray.count);
   RETURN myEmailArray;

--exception
	--when others then
	--do something

end ConvertToArray;

Finally Declare a local variable, call the function and loop through what is returned

l_array 		 t_array; 

l_Array := ConvertToArray('[email protected],[email protected],[email protected]');


    for idx in 1 .. l_array.count
    loop
		l_EmailTo := Trim(replace(l_arrayXX(idx),'"',''));
		if nvl(l_EmailTo,'@') = '@' then
			dbms_output.put_line('Empty: l_EmailTo:' || to_char(idx) || l_EmailTo);
		else
			dbms_output.put_line
			( 'Email ' || to_char(idx) ||
				' of array contains: ' ||
				l_EmailTo
			);
		end if;
    end loop;