oracle-databaseplsqloracle9i

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


Solution

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