Introduction

I was working on a task recently where I needed to maintain an array of record IDs during a PL/SQL loop. I needed the array so I could mark successfully processed records after the loop was complete.

This is pretty easy to do with PL/SQL, but I thought I would check the APEX PL/SQL APIs first to see if something would make it even easier. As usual, I was not disappointed.

This post will describe two APEX_STRING APIs to help manage arrays in PL/SQL APEX_STRING.PUSH and APEX_STRING.PLIST_%.

Simple List

APEX_STRING.PUSH enables you to append values to a PL/SQL array. It takes care of initializing the array, so all you have to do is declare the array and add values. There are six overloaded versions of APEX_STRING.PUSH to handle arrays of VARCHAR2, NUMBER, CLOB, and Arrays (yes, that's an array of arrays).

Example

In the example below, I am processing a list of tasks. If the task_status = 'ARCHIVE', then I need to add the task_id to an array. Once I have completed processing the tasks, I need to delete all tasks I captured in the array.

The result of the above dbms_output statement look like this:

Array Contents: 3:4:5:6:21:62

Notes

• I could have achieved the above with just SQL. In some cases, however, you need to perform other processing while you keep track of a list of items, and storing these items in an array is quite convenient. In this case, maintaining the list in a PL/SQL array is faster than adding records to a global temporary table, for example.

• I am using apex_string.join to transform the lt_archived_task_ids array into a string so that I can output it more easily.

• apex_string.push always appends values to the end of the array.

• Make sure to match the data type of the array matches the data types of the values you want to add to it. Use apex_t_number for NUMBER values, apex_t_varchar2 for VARCHAR2 values, etc.

Key Value Pairs

APEX_STRING also has a set of 'PLIST' functions to maintain an array of key-value pairs in a PL/SQL array. These functions allow adding, updating, and deleting key-value pairs from a PL/SQL array.

Example

In the example below, I am building a list of task owners along with a count of how many tasks are assigned to them.

The result of the above dbms_output statements looks like this.

Before Delete: JSMITH:5:CNDEMO:4:JJONES:1
After Delete : JSMITH:5:JJONES:1

Notes

• I am using apex_string.join to transform the lt_owner_task_count array into a string so that I can output it more easily.

• apex_string.plist_get raises a NO_DATA_FOUND exception if the key is not found in the array. I am using this exception to trigger the addition of a new task owner to the array.

• apex_string.plist_push always appends a value to the end of the array. It does not check the array to see if the key already exists, so duplicate key values can be created if you use apex_string.plist_push.

• If you have duplicate key values in your array and you use apex_string.plist_put to update a key value, only the first occurrence of the key will be updated. Given this, be careful when using apex_string.plist_push.

Conclusion

This post illustrates another example of how valuable the APEX PL/SQL APIs can be. They take common development patterns and make implementing them more straightforward and consistent.

🔗 Read More

• 🩳 [APEX Shorts](https://blog.cloudnueva.com/series/apex-shorts)

• #️⃣ [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)