What can you do with APEX_COLLECTION

The APEX_COLLECTION (Oracle 12c onwards) package can be a good choice when you need to work with
data in a temporary, in-memory table-like structure.

When should I use the APEX_COLLECTION?

  1. Temporary data storage: If you need to temporarily store data that is
    generated by a report or a form, using APEX_COLLECTION can be a good option. Since the
    data is stored in memory rather than in a physical table in the database, you don’t need
    to worry about the overhead of creating and managing a database table.
  2. Working with large datasets: If you need to work with large datasets,
    APEX_COLLECTION can be more efficient than other options, such as creating a temporary
    table in the database. Since the data is stored in memory, it can be accessed and
    manipulated more quickly than data that is stored in a physical table.
  3. Non-persistent data: If you don’t need to persist the data between
    sessions or across different users, APEX_COLLECTION can be a good choice. Since the data
    is stored in memory, it is automatically cleared when the session ends or when the user
    logs out.
  4. Flexible data structure: APEX_COLLECTION provides a flexible data
    structure that can be easily modified at runtime. You can add, update, and delete
    records as needed, and you can define the structure of the collection dynamically based
    on your requirements.

When should I avoid it?

While APEX_COLLECTION can be a useful tool for working with temporary data in Oracle APEX,
there are some scenarios where it may not be the best choice. Here are a few situations
where you may want to consider alternative approaches:

  1. Large or complex datasets: While APEX_COLLECTION can be efficient for
    working with large datasets, it may not be the best choice for very large or complex
    datasets. In such cases, it may be better to use a physical table in the database or a
    dedicated data store, such as a NoSQL database or a document store.
  2. High transaction volume: If you have a high volume of transactions or
    concurrent users, APEX_COLLECTION may not be able to keep up with the demand. In such
    cases, it may be better to use a dedicated database table or another data storage
    mechanism that is optimized for high performance.
  3. Data persistence: If you need to persist the data across sessions or
    across different users, APEX_COLLECTION may not be the best choice. While the data is
    stored in memory and can be accessed quickly, it is not durable and will be lost when
    the session ends or when the user logs out. In such cases, it may be better to use a
    dedicated database table or another data storage mechanism that is designed for
    durability and persistence.
  4. Complex data structures: While APEX_COLLECTION can be flexible and
    dynamic, it may not be the best choice for working with very complex data structures or
    data types. If you need to work with data that has a complex or hierarchical structure,
    it may be better to use a dedicated data store or a specialized data modeling tool that
    is designed to handle such data.

That being said, there are also some limitations to using APEX_COLLECTION, such as the
inability to create indexes or enforce constraints, and the limited support for complex data
types. In some cases, it may be more appropriate to use a physical table or other data
mechanism, depending on your specific requirements and use case.

 Alright, now that you know the pros and cons, here is a basic tutorial of what you need to work
with it.


Create an APEX collection:

You can create an APEX collection using the APEX_COLLECTION.CREATE_COLLECTION procedure. This
creates a new collection with the specified name and collection type.

p_collection_name => 'MY_COLLECTION',
p_collection_type => 'APEX_COLLECTION_TYPE_VARCHAR2'

Add records to the collection:

You can add records to an APEX collection using the APEX_COLLECTION.ADD_MEMBER procedure. This
adds a new record to the specified collection with the specified values.

p_collection_name => 'MY_COLLECTION',
p_c001 => 'John',
p_c002 => 'Doe',
p_c003 => 'johndoe@example.com'

Access records in the collection:

You can access the records in an APEX collection using the APEX_COLLECTION.GET_MEMBERS function. This
function returns a cursor that can be used to iterate over the records in the collection.

l_cursor apex_t_varchar2;
l_rec apex_collection_types.apex_collection_varchar2_nt;
l_cursor := apex_collection.get_members(p_collection_name => 'MY_COLLECTION');
FOR i IN 1..l_rec.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_rec(i).c001 || ' ' || l_rec(i).c002 || ': ' || l_rec(i).c003);

Merge records in the collection:

You can merge records in an APEX collection using the APEX_COLLECTION.MERGE_COLLECTION procedure. This
procedure merges the specified records from the source collection into the target collection.

p_target_collection_name => 'MY_COLLECTION',
p_source_collection_name => 'OTHER_COLLECTION',
p_member_key => '1',
p_update_column => 'C003',
p_update_value => 'newvalue@example.com'

Change records in the collection:

You can change records in an APEX collection using the APEX_COLLECTION.UPDATE_MEMBER procedure. This
updates the specified record in the collection with the specified values.

p_collection_name => 'MY_COLLECTION',
p_seq => 1,
p_c001 => 'Jane',
p_c002 => 'Doe',
p_c003 => 'janedoe@example.com'

Delete records from the collection:

You can delete records from an APEX collection using the APEX_COLLECTION.DELETE_MEMBER procedure. This
deletes the specified record from the collection.

p_collection_name => 'MY_COLLECTION',
p_seq => 1


That’s it! Using the APEX_COLLECTION package, you can create, access, merge, change, and delete records in a
collection in Oracle APEX.

Getting started with APEX_STRING.SPLIT

Dealing with string manipulation in PL/SQL isn’t hard, but it can be more work than it’s worth. Converting lists of values coming from HTML inside a single text item, separating lists based on multilple types of separators or even just the first N elements can require you to make an algorithm for something that kind of trivial.

For that reason I would like to show you the APEX_STRING.SPLIT function that should take care of most of you issues. Let’s take a look at the following example:

l_text VARCHAR2(100) := 'apple,banana,melon,orange';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count

This is a basic example that receives 2 parameters, one which is the varchar2 that we want to split and the other is the separator. Easy

Now lets take a look on this example where the delimiters are different between themselves.

l_text VARCHAR2(100) := 'apple;banana,melon:grapes,lemon';
l_delimiter VARCHAR2(10) := '[;,:]';
l_array apex_t_varchar2;
l_array := apex_string.split(l_text, l_delimiter);
FOR i IN 1 .. l_array.count

As you may have noticed, the separator is provided as a regular expression, which means we get a lot of flexibility when working this way.

For this last example, we’ll be applying a delimiter.

l_text VARCHAR2(100) := 'apple,banana,cherry,date';
l_delimiter VARCHAR2(1) := ',';
l_array apex_t_varchar2;
l_array := apex_string.split(l_text, l_delimiter, 2);
FOR i IN 1 .. l_array.count

This is quite easy as well; it simply stops our breaking of the varchar after Nth iterations. where the Nth item in the array will have all remaining values.


Do you have CLOBs or Numbers?

There are two other functions I think are worth mentioning, and the have nearly the same signature, which could help you with numbers and values larger than varchar2.

  • SPLIT_CLOBS: take is a CLOB as the first parameter, same separation and limit rules. Return an apex_t_clob
  • SPLIT_NUMBERS: Doesn’t have the limit parameter and return an apex_t_number



That’s about it, with this package you can do a lot more and if you are working with APEX I highly recommend you taking a look at it’s documentation at https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/APEX_STRING.html#GUID-CAFD987C-7382-4F0F-8CB9-1D3BD05F054A

What is Oracle APEX Quick SQL?

If you’ve browsed Oracle APEX’s features you must have seen the Quick SQL in the SQL Workshop. But have you ever used it? Do you know the shortcut? If not, have a look at this post to understand better this amazing tool.


What is Quick SQL?

According to the Oracle Documentation

Quickly develop a script for simple tables and views.

Quick SQL provides a quick way to generate the SQL required to create a relational data model from an indented text document.

Quick SQL is designed to reduce the time and effort required to create SQL tables, triggers, and index structures. This tool is not designed to be a replacement for data modeling. Instead, Quick SQL is simply a quick way to develop a script for simple tables and views. Once you have generate the SQL, you can then tweak and expand it.

From this quote, you can see that it is not design to replace traditional SQL Development, it is just a tool to help speed up the development of simple routines that sometimes take longer than they should.

Where can I test it?

If you have Oracle APEX,  you can open it in SQL Workshop -> Utilities -> Quick SQL

If you don’t have it, then you should go get it, it’s free 🙂 . But if you really in a hurry, then try https://apex.oracle.com/quicksql

Basic Syntax

As you’ll see, quick SQL is easy to use, you just add some text, indentation and shorthands for stuff you want. Take a look at the example below

On the left side is what I’ve wrote and on the right side is the actual script being generated. Can you see from this simple example how much writing quick sql did for me? I’ve only wrote the table name, indented to write the columns with some shorthands for things like data types and not null. And when I wrote “date_of_birth” i didn’t have to write anything. And the Primary key came as a bonus.

By pressing Enter or “Generate SQL” it keeps updating the right side, so I knew what was happening all the time.

Let’s add a bit more

Ok, that may have been a bit too easy, let’s add

  • check constraints
  • a child table
  • audit columns (auditing in the trigger)

This simple addition made it difficult to fit the right side content, so I’m adding it bellow

 Where can I learn more?

The good news is that Oracle provides a lot of content to study.

The bad news is that, as far as i know, only Oracle has content about it

Quick SQL Documentation: https://docs.oracle.com/en/database/oracle/application-express/20.2//aeutl/using-quick-sql.html#GUID-21EE36C2-F814-48C0-90EA-7D464E9014FD

Shorthands and Datatype: I don’t have a link for that, I have a path. with Quick SQL opened, click on help and you’ll get all the datatypes, table and column directives that you can use (check image below)

One final tip

Take a look at the sample files, you won’t believe how much you’ll learn from them.

That’s it, good studies and good luck

The power of Dynamic Actions in APEX

In this post I would like to talk about a few configuration items in the Dynamic Actions that could make reduce your need to code basic interactions even further.

Selection Type: jQuery Selector and Javascript Expression

  • Do you have multiple elements that need the same action?
  • Are your page’s elements not accessible by the items, buttons, regions or columns selection type?
  • Does your page region have plain HTML and you want it to use Dynamic Actions?

If you said yes, then I highly suggest you take a look below:

jQuery Selector:

The easiest of the 2. You just need to add the selector that will capture the element(s) you need.

You don’t know how to use a selector? Here are a few tips

  • If your apex item has a static ID with the value element-x , you can write #element-x (yes, hashtag include)
  • If you want create a global action that will do or check something, you can write just body

Selecting elements is a powerful tool and to understand them better, I recommend reading more about it in https://learn.jquery.com/using-jquery-core/selecting-elements/.

JavaScript Expression:

Alright, let’s say that your search criteria is so complex that selectors won’t do it. Or maybe you have to validate something in the elements beforehand. If that is the case you can fallback to the JavaScript Expression which allow you to grab DOM elements, apply logic and return processed items to be selected.

Event Scope

The event scope is an awesome tool that allow us to do dynamic binding, which, to the best of my knowledge remove the need for adding event directly into the HTML code because it continues to work even after Pagination, Refresh adding new items, etc…

I am 99% sure that this comes from the jQuery ‘on’ event with delegate. Anyways, here are some tips

  • By selecting it, the Static Container (jQuery Selector) will become available. Please make sure it is an HTML element that DOES NOT get updates with AJAX. Usually you are safe with regions (give them a static id) and in the last case the <main> or <body> tags. But always try the region first
  • IMPORTANT: In the ‘when’ section, if you are using a jQuery Selector as well, make it not including the container itself.

Client-side vs Server-side Conditions

I’ve seen this with many clients, they are not the same!!!! The only thing they have in common is the name.

Client-side Condition: Is the equivalent of adding an IF clause to you Dynamic Action this allows you to better leverage the low code side of APEX. When adding a condition, you can redirect to the TRUE or FALSE actions.

Server-side Condition: The server-side condition does the same thing to DAs that it does for other components, if true the server will render/send it to the user’s machine, otherwise it won’t. There no true and false and the actions won’t work because the Dynamic Action doesn’t exist to be bound to the element.


As you can see, with APEX you probably don’t need to add external JavaScript or bind events directly on the HTML tag which helps to keep your low code also clean from mundane taks.

Oracle APEX List of Values and Popup Lov

Most people don’t know, but Oracle APEX List of Values (LoV) are very flexible components with that allows us to perform a series of actions using a centralized and reusable data source.

In this post, I’ll be briefly talking about some of the amazing functionalities this tool provides us.

Multi column view

After creating the LoV you can scroll down to Additional Columns and add all columns available in your original select. This columns will become available Popup LoV when the user clicks on it to find an item.

The nice thing about this feature is that you can toggle if the column is visible, searchable and even add a format mask.

And don’t worry, if you use the same LoV for Select Lists items, it will still get its configuration from the Display / Return Value fields.


If you have one or just a few Apps in you workspace, you probably never did a subscription. But this is a great way to have a “Master List App” that all other apps listen to get updates.

The interesting thing to note is that the subscription needs to be refreshed by the Publisher or Subscriber, it doesn’t happen automatically. For this reason it allows you test it before letting the code propagate to other applications.

JavaScript Initialization

For those who know JavaScript, you can modify the initialization object and return it with a new configuration.

Although Oracle APEX has dropped jQuery UI, it still uses the same OPTIONS object that you can learn more about in here https://api.jqueryui.com/dialog/


Additional Outputs

Going back to my first tip when I mentioned multiple columns, in addition to that, you can set the value of multiple page items by using said columns. The interface here is a bit too compact, but from the example you can see the pattern. Column name : page item, another column : another page item.



As you can see, the List of Value and Popup LoV are 2 powerful features that allow us to quickly develop without gaving to worry too much about standard list components (and some not so standard as well). I could probably write much more on the subject, but that will probably stay for another post 😉