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

Cadena

See: PL/SQL Collections and Records – Topflight

The "table" reference tends to be a hangover from the old PL/SQL tables naming. VARRAYs, Associative Arrays and Declared nested tables are all in-memory array types. – Combustor

read this link orafaq.com/wiki/VARRAY and dba-oracle.com/tips_oracle_varray.htm – Kunin

Also more examples here – Onanism

Answer 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.

Biserrate

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

@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. – Corvin

@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. – Biserrate

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). – Mancini

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

@Depicture see #33621547 – Biserrate

Answer 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.

Combustor

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 – Hortative

j-chomel's version (https://mcmap.net/q/156188/-oracle-pl-sql-how-to-create-a-simple-array-variable) 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') – Northington

Answer 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!

Haha

Answer 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;

Bortman

Answer 5

A simple PL/SQL Table is sufficient.

DECLARE 
  TYPE typ_test_codes         IS TABLE OF VARCHAR2(30);
  v_test_codes                typ_test_codes := typ_test_codes('AAA','BBB','CCC','DEF','ZZZ');
BEGIN
  FOR i IN v_test_codes.FIRST..v_test_codes.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_test_codes(i));
  END LOOP;
END;

Gobelin

Answer 6

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;

Ranson

Answer 7

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; 
        /

Denazify