# Working with Arrays in APEX and PL/SQL

# 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](https://apex.oracle.com/api) first to see if something would make it even easier. As usual, I was not disappointed.

> This post will describe two [APEX\_STRING](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/APEX_STRING.html#GUID-CAFD987C-7382-4F0F-8CB9-1D3BD05F054A) APIs to help manage arrays in PL/SQL APEX\_STRING.PUSH and APEX\_STRING.PLIST\_%.

# Simple List

[APEX\_STRING.PUSH](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/PUSH-Procedure-Signature-1.html#GUID-E1100503-907E-4F98-B6F6-EED5376BA9A8) 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.

%[https://gist.github.com/jon-dixon/cc54ad39d4e922c44e74da7be04bdc17] 

The result of the above `dbms_output` statement look like this:

```plaintext
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](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/PLIST_DELETE-Procedure.html#GUID-6E693596-60DE-4177-963B-E2C5DA16E921)' 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.

%[https://gist.github.com/jon-dixon/f9b9c1750cbf2a4d118e072104bdd778] 

The result of the above `dbms_output` statements looks like this.

```plaintext
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](https://blog.cloudnueva.com/series/apex-shorts))
    
* #️⃣ \[APEX Posts\]([https://blog.cloudnueva.com/tag/orclapex](https://blog.cloudnueva.com/tag/orclapex))