Apex feature for non Apex users

In one of mine previous posts (SQL based export data to file) I have shown how to export data to flat file by plain sql statement. Here is also simple but different way of use of another cool Oracle "out of the box" feature from Apex.

Since Apex is now part of every Oracle database, these Apex ready made utilities are useful even if you aren't using Apex. Let me show you easy example of how to use SQL based on dynamic (custom) lists.

SQL> declare
  2    l_arr apex_application_global.vc_arr2;
  3    l_str varchar2(4000);
  4  begin
  5    -- Convert delimited string to array
  6    l_arr := apex_util.string_to_table('one,two,three,four,five', ',');
  7    for i in 1..l_arr.count loop
  8      dbms_output.put_line(l_arr(i));
  9    end loop;
 10    
 11    -- Convert array to delimited string with ";"
 12    l_str := apex_util.table_to_string(l_arr,';');
 13    dbms_output.put_line(l_str);
 14  end;
 15  /
one
two
three
four
five

one;two;three;four;five

PL/SQL procedure successfully completed.

SQL> 

With dynamic lists it is very easy to construct functions/procedures whose parameters can easy handle dynamic number of elements.

create or replace function count_dyn_items (p_str in varchar2, p_delim in varchar2) 
return pls_integer
is
  l_arr apex_application_global.vc_arr2;
begin
  l_arr := apex_util.string_to_table(p_str,p_delim);
  return l_arr.count;
end;
/

And the usage of that function is like:

SQL> select count_dyn_items ('a,b,c,d',',') from dual;

COUNT_DYN_ITEMS('A,B,C,D',',')
------------------------------
                             4

SQL> select count_dyn_items ('A1;A2;A3;B;RT;12;22',';') from dual;

COUNT_DYN_ITEMS('A1;A2;A3;B;RT;12;22',';')
------------------------------------------
                                         7

SQL> 

All other is just imagination.

Cheers!