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

Question

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

Solution 1

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.

Solution 2

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.

Solution 3

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!

Solution 4

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;

View more solutions

Comments

[unziberla]

The iteration condition raises VALUE_ERROR when the collection is empty. I would suggest to rather use FOR i IN 1 .. employee_array.COUNT in this case

[m.edmondson]

"confusingly" just about sums up Oracle

[Honinbo Shusaku]

Do I insert into tables the same way as arrays? i.e. my_array(0) := 'some string';

[Honinbo Shusaku]

@TonyAndrews array.extend(); does EXTEND add a slot to a regular bounded array? In that case, it's already dynamic in size so a table (unbounded array) wouldn't be needed.

[Tony Andrews]

@Abdul, no it doesn't. I never use VARRAYs normally but when testing the above code I checked what happens if you try to extend a varray(3) 4 times - you get a "subscript out of limit" error.

[Jonathan Van Dam]

Wish I coud up vote this answer multiple times @TonyAndrews since you covered the array.extend(). Every where I looked did not show this and it was the most important part to being able to add more than one item (from my understanding of it, still new to arrays in SQL).

[Andreas Covidiot]

j-chomel's version (stackoverflow.com/a/40579334/1915920) based on sys.odcivarchar2list below has the advantage, that you also have a constructor at hand, e.g. for function param default initialization: sys.odcivarchar2list('val1','val2')

[Dharm]

Does it have a limit on the size of array, I mean can I pass on 1000+ values ?

[Tony Andrews]

@Dharm see stackoverflow.com/questions/33621047/…