Photo of lots of small wooden file cabinets

Ode to APEX_T_VARCHAR2 #JoelKallmanDay

APEX_T_VARCHAR2 is a database type that comes with an Oracle APEX installation. I use it quite frequently and think that with it you can write better code.


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:

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.

Use Case 1: Query values that are in a comma-separated string of values

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:

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):

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:

Use Case 2: Handling Multi-Value APEX Items

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:

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.

Use Case 3: storing values during long processes

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.

Use Case 4: Loop Over A Rowtype (kind of)

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.

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:

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.


loading comments...