apex_t_varchar2 Reference Sheet

Introduction

apex_t_varchar2 is a powerful datatype provided by Oracle APEX. However, sometimes it is not obvious what commands to use to manipulate the array. This is a centralized page to provide a list of some of the most useful commands to use when manipulating apex_t_varchar2.

Initializing an apex_t_varchar2

There are two ways to initialize an apex_t_varchar2 array, the constructor and by using APEX_STRING.split.

apex_t_varchar2 constructor

This is the basic constructor for the datatype.

declare
    l_arr apex_t_varchar2;
begin
    l_arr := apex_t_varchar2('1','2','3');
end;

APEX_STRING.split

To create an apex_t_varchar2 from a varchar2 or clob, use APEX_STRING.split.

declare
    l_arr apex_t_varchar2;
begin
    l_arr := apex_string.split('1:2:3', ':');  -- apex_t_varchar2('1','2','3')

    l_arr := apex_string.split('123', null);  -- apex_t_varchar2('1','2','3')
end;

Adding members to the array

To append a member, or an array of members (as a apex_t_varchar2) to an apex_t_varchar2, use APEX_STRING.push.

This utility function adds a member, or an array of members (as a apex_t_varchar2) to the apex_t_varchar2.

declare
    l_arr   apex_t_varchar2 := apex_t_varchar2('1');
    l_arr_2 apex_t_varchar2 := apex_t_varchar2('3','4');
begin
    APEX_STRING.push(l_arr, '2'); -- apex_t_varchar2('1','2')

    APEX_STRING.push(l_arr, l_arr_2); -- apex_t_varchar2('1','2','3','4')
end;

Updating members in the array

To update an array value simply reassign the value using index access.

declare
    l_arr apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'orange');
begin
    l_arr(2) := 'mango'; -- 'apple', 'mango', 'orange'
end;

Removing members from the array

To delete elements from a non-plist apex_t_varchar2, there is no provided API. In order to delete elements, use a query and bulk collect into a new apex_t_varchar2 variable. Here are 3 potential ways to do so.

declare
   l_arr        apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'orange');
   l_arr_delete apex_t_varchar2;
begin
   dbms_output.put_line(apex_string.join(l_arr, ', ')); -- 'apple', 'banana', 'orange'

   -- delete by value (will delete every entry equal to banana)
   select column_value
     bulk collect into l_arr_delete 
     from table(l_arr)
    where column_value != 'banana';

   -- delete by index (will only delete 1 element)
   select column_value
     bulk collect into l_arr_delete 
     from (select rownum idx, column_value from table(l_arr))
    where idx != 2;

    -- delete by value (will only delete 1 element)
   select column_value
     bulk collect into l_arr_delete 
     from (select rownum idx, column_value from table(l_arr))
    where idx != apex_string.index_of(l_arr, 'banana'); 

   dbms_output.put_line(apex_string.join(l_arr_delete, ', ')); -- 'apple', 'orange'
end;

Finding element position in the array

To get the index number of a particular entry, use APEX_STRING.index_of. This function will return the index of the first instance in the array and null if it does not exist.

declare
    l_arr apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'orange', 'apple');
    l_idx number;
begin
    l_idx := APEX_STRING.index_of(l_arr, 'banana'); -- 2
end;

Testing if an array contains a value

APEX_STRING.index_of can be used to test if an apex_t_varchar2 contains an element. This is possible since this function will return null if an element does not exist.

declare
    l_arr apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'orange');
    l_idx number;
begin
    l_idx := APEX_STRING.index_of(l_arr, 'pear'); -- null

    if l_idx is null then
        dbms_output.put_line('pear is not contained in the array');
    end if;
end;

For loop over apex_t_varchar2

You can loop over an apex_t_varchar2 by using the first and last properties of the array.

declare
    l_arr apex_t_varchar2 := apex_t_varchar2('apple', 'banana', 'orange');
begin
    for i in l_arr.first..l_arr.last loop
        dbms_output.put_line(l_arr(i));
        --apple
        --banana
        --orange
    end loop;
end;

Using apex_t_varchar2 as source in SQL query

An apex_t_varchar2 array can be used in a SQL query if surrounded by table(). The values of the array will then be referenced by column_value in the query.

select column_value
  from table(apex_t_varchar2('apple', 'banana', 'orange'))

-- COLUMN_VALUE
----------------
--    apple
--    banana
--    orange

Concatenate the array back into a varchar2

It is possible to join the elements of an apex_t_varchar2 back into a varchar2 with a separator using APEX_STRING.join. This does the opposite of APEX_STRING.split.

declare
    l_arr apex_t_varchar2 := apex_t_varchar2('1','2','3');
    l_str varchar2(40);
begin
    l_str := apex_string.join(l_arr, ':'); -- '1:2:3'
end;

Sort an apex_t_varchar2

To sort an apex_t_varchar2 you simply have to sort it in a SQL query and bulk collect it into another apex_t_varchar2 variable

declare
   l_arr        apex_t_varchar2 := apex_t_varchar2('3', '1', '2');
   l_arr_sorted apex_t_varchar2;
begin
   dbms_output.put_line(apex_string.join(l_arr, ', ')); -- '3', '1', '2'

   select column_value
     bulk collect into l_arr_sorted 
     from table(l_arr)
   order by column_value asc;

   dbms_output.put_line(apex_string.join(l_arr_sorted, ', ')); -- '1', '2', '3'
end;

Randomize array

In order to randomize the elements in an apex_t_varchar2 array, use APEX_STRING.shuffle.

declare
   l_arr        apex_t_varchar2 := apex_t_varchar2('1', '2', '3');
begin
   dbms_output.put_line(apex_string.join(l_arr, ', ')); -- '1', '2', '3'
   apex_string.shuffle(l_arr);
   dbms_output.put_line(apex_string.join(l_arr, ', ')); -- randomized order 
end;