Ode to APEX_T_VARCHAR2 #JoelKallmanDay

The type apex_t_varchar2 itself is not that special. If we open up its declaration we can see that it has a different internal name ”wwv_flow_t_varchar2” and is basically just a varchar2 array:

1create or replace type wwv_flow_t_varchar2 as table of varchar2(32767);
2

With normal arrays, you can already do great stuff but apex_t_varchar2 gets its superpowers from the apex_string package which adds plenty of functions on top of these arrays.

In the following sections, I want to point out how to work with the type and how it can make your code elegant and efficient.

Imagine a REST API where the user can fetch customer data. Your API receives a customer code and returns the data for it. Your API can currently only handle one customer at a time:

1select *
2  from customers
3 where cust_code = pi_cust_code
4

You can only call the API with one customer code at a time, but the user wants to fetch multiple ones most of the time. We can improve this by allowing to pass multiple customer codes as a comma-separated string (e.g. CUST1,CUST2,CUST3):

1select *
2  from customers
3 where cust_code member of(
4    apex_string.split(pi_csv_cust_codes, ',')
5  );
6

Now the user saves a lot of requests and the database executes fewer queries.

If you want to filter the values based on a column with a number type you can use the function apex_string.split_numbers which returns apex_t_varchar2’s sibling apex_t_number:

1select *
2  from customers
3 where cust_id member of(
4    apex_string.split_numbers(pi_csv_cust_ids, ',')
5  );
6

The most “APEX thing” to do with apex_t_varchar2 is handling APEX items that store multiple values. This includes checkboxes, shuttles, multi-value select lists, etc. By default, they store the selected values in a single string colon-separated (e. g. CHESS:FOOTBALL:RACING).

If we want to store their value in a normalized fashion (one row for each value), we must split these values up before merging them. This can be done super easily:

1-- ...
2as
3  l_fav_sports apex_t_varchar2;
4begin
5  l_fav_sports := apex_string.split( pi_fav_sports, ':' );
6
7  -- insert new entries
8  insert into people_favorite_sports
9    (person_id, sport)
10  select pi_person_id, column_value
11    from table(l_fav_sports)
12   where column_value not in ( select sport from people_favorite_sports where person_id = pi_person_id )
13  ;
14
15  -- remove old values that are not selected anymore
16  delete from people_favorite_sports
17   where person_id = pi_person_id
18     and sport not in ( select column_value from table(l_fav_sports) )
19  ;
20-- ...
21

We first split the colon-separated string into our apex_t_varchar2 variable. With the resulting variable, we can construct both an insert and delete statement that adds newly selected and removes unselected entries from the table.

Note that we can query an apex_t_varchar2 array like a normal table. We need to pass it to the table function and get the value in a column_value col.

Imagine a PL/SQL data validation process where we validate multiple rows of user input. A user enters all the data, invokes a validation function, and receives a list of errors and warnings.

Because the rows are independent of each other it makes sense to not exit at the first error but instead validate all rows and in the end list all errors and even some warnings.

To conveniently store these feedback messages we can use an array. We create one for the error messages and one for the warnings. Each time we come across a problem with the user input we add a message to the corresponding array with apex_string.push.

1-- ...
2begin
3  l_errors := apex_t_varchar2();
4  l_warnings := apex_t_varchar2();
5
6  for rec in (
7    select *
8      from user_inputs
9     order by line_no asc
10  )
11  loop
12    -- ...
13    if user_underage(rec.birthdate) then
14      apex_string.push(l_errors, 'Line' || rec.line_no || ': User is too young!');
15    end if;
16
17    if not country_phone_no_match(rec.country, rec.phone_number) then
18      apex_string.push(l_warnings, 'Line' || rec.line_no || ': Country does not match with phone no country code!');
19    end if;
20    -- ...
21  end loop;
22
23  -- create messages
24  if l_warnings.count > 0 then
25    po_warnings_list_html := '<ul><li>' || apex_string.join(l_warnings, '</li><li>') || '</li></ul>';
26  end if;
27
28  if l_errors.count > 0 then
29    po_status_error := true;
30    po_error_list_html := '<ul><li>' || apex_string.join(l_errors, '</li><li>') || '</li></ul>';
31  else
32    po_status_error := false;
33  end if;
34-- ...
35

I like to write code that still works under changing conditions. I had to implement an e-mail feature where the end users could define and edit the template. To make this possible I added a Markdown editor to a page and stored the template in a CLOB column. Because the e-mails are sent for a specific data record I allowed them to put placeholders in the template. These are named after their column name in a specific view and are replaced with the actual value before sending the e-mail.

1Dear ##FIRST_NAME## ##LAST_NAME##,
2
3the data record "##RECORD_NAME##" has changed.
4...
5

I could have just done every replacement manually for each column but I wanted it to be more generic. I anticipated that this view and by that the available placeholders will grow in the future. So I wanted to loop over all the columns of a single record of that view and dynamically replace the placeholders.

Unfortunately, this is not as easy as it sounds. As far as I know, there is no way to loop over a rowtype (please contact me if I am wrong). I would love a native feature where you could just get an associative array/map like structure from a rowtype. So instead I got a bit creative with apex_t_varchar2:

1as
2  l_col_arr apex_t_varchar2;
3  l_val_arr apex_t_varchar2;
4begin
5  select column_name
6    bulk collect into l_col_arr
7    from user_tab_cols
8   where table_name = 'MAIL_VALUES_V';
9
10  l_dyn_sql :=
11    'select apex_t_varchar2(' || apex_string.join(pio_col_array, ', ') || ')
12       from MAIL_VALUES_V where record_id = :1';
13
14  execute immediate l_dyn_sql into l_val_arr using pi_record_id;
15
16  for i in l_val_arr.first .. l_val_arr.last
17  loop
18    pio_mail_text := replace(pio_mail_text, '##' || l_col_arr(i) || '##', l_val_arr(i));
19  end loop;
20

We fill two arrays, one for the column names and one for the column values where importantly the positions match. The downside to this approach is that we convert every value of that record into a string and that we have to use dynamic SQL. This conversion is not a problem in this case as I want to dump all values in a mail text. I try to avoid dynamic SQL because you can get nasty SQL injection vulnerabilities if you don’t sanitize your inputs but in this case, we have no choice.

Noticed how we in the first array store all values of a single column (vertically) and secondly all values of a single row (horizontally)? We achieve this with bulk collect und using the types constructor apex_t_varchar2(col1,col2,col3).

There is also a different approach to loop over a rowtype with dbms_sql where you don’t lose your types (thanks Moritz for pointing this out to me). Here is one example from Anton Scheffer’s “as_xlsx” package. But as you can see the code required for this is quite complex. So you have to decide for yourself what you want to do if you have a similar case.

This post was published in the context of #JoelKallmanDay. It is the Oracle Community day where many people in the community share their knowledge about Oracle PL/SQL and APEX. You can find more about it in Tim Hall’s announcement post.