🔤 Working with APEX_STRING: Useful Methods and Practical Examples

Introduction

I bet that you missed me!

When working with Oracle APEX and PL/SQL, we often deal with strings in a more advanced way — whether it's parsing delimited values, escaping characters, or handling nulls. Oracle APEX provides a great utility package for this: APEX_STRING.

In this article, I’ll guide you through the most commonly used functions in the APEX_STRING package, with practical examples to help you understand when and how to use them.

---

What is APEX_STRING?

APEX_STRING is a built-in package provided by Oracle APEX that offers useful functions for working with strings, particularly for web-based applications. It helps handle delimited strings, parsing values, and more.

Let’s take a look at the most useful procedures and functions available in this package.

---

🧪 Commonly Used Methods

1. APEX_STRING.PUSH

This method allows you to dynamically add elements into an APEX_T_VARCHAR2 collection/array, which is useful when building string arrays programmatically.

DECLARE
  l_values apex_t_varchar2;
BEGIN
  apex_string.push(l_values, 'First');
  apex_string.push(l_values, 'Second');
  apex_string.push(l_values, 'Third');

  FOR i IN 1 .. l_values.COUNT LOOP
    DBMS_OUTPUT.put_line(l_values(i));
  END LOOP;
END;

Output:

First
Second
Third

---

2. APEX_STRING.SPLIT

DECLARE
  l_values apex_t_varchar2;
BEGIN
  l_values := apex_string.split('apple,banana,orange', ',');
  FOR i IN 1 .. l_values.COUNT LOOP
    DBMS_OUTPUT.put_line(l_values(i));
  END LOOP;
END;
/*
output:
apple
banana
orange
*/

---

3. APEX_STRING.SPLIT_NUMBERS

A variant of SPLIT, but returns a table of numbers instead of strings.

DECLARE
  l_numbers apex_t_number;
BEGIN
  l_numbers := apex_string.split_numbers('10,20,30', ',');

  FOR i IN 1 .. l_numbers.COUNT LOOP
    DBMS_OUTPUT.put_line('Value: ' || l_numbers(i));
  END LOOP;
END;

Output:

Value: 10
Value: 20
Value: 30

---

4. APEX_STRING.JOIN

DECLARE
  l_result VARCHAR2(4000);
BEGIN
  l_result := apex_string.join(apex_t_varchar2('a','b','c'), '-');
  DBMS_OUTPUT.put_line(l_result); -- Output: a-b-c
END;

---

5. APEX_STRING.STRING_TO_TABLE

Convert a string of names into a PL/SQL collection/array:

DECLARE
  l_string VARCHAR2(4000) := 'Ana,Bruno,Carla,Diego,Elisa';
  l_nomes APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_nomes := apex_string.string_to_table(l_string, ',');

  FOR i IN 1 .. l_nomes.COUNT LOOP
    DBMS_OUTPUT.put_line('Name ' || i || ': ' || TRIM(l_nomes(i)));
  END LOOP;
END;
/*output:
Name 1: Ana
Name 2: Bruno
Name 3: Carla
Name 4: Diego
Name 5: Elisa
*/

---

6. APEX_STRING.TABLE_TO_STRING

Convert employee names into a single comma-separated string:

DECLARE
  l_nomes APEX_APPLICATION_GLOBAL.VC_ARR2;
  l_string VARCHAR2(4000);
BEGIN
  SELECT first_name
  BULK COLLECT INTO l_nomes
  FROM employees
  WHERE ROWNUM <= 5;

  l_string := apex_string.table_to_string(l_nomes, ', ');
  DBMS_OUTPUT.put_line('Employees: ' || l_string);

/*
output:
 Employees: Ellen, Sundar, Mozhe, Shelli, Amit
*/
END;

🔄 Bonus: Combine SPLIT + JOIN

Example: Uppercase all elements in a delimited string:

DECLARE
  l_values apex_t_varchar2;
  l_result VARCHAR2(4000);
BEGIN
  l_values := apex_string.split('apex,plsql,oracle', ',');
  FOR i IN 1 .. l_values.COUNT LOOP
    l_values(i) := UPPER(l_values(i));
  END LOOP;

  l_result := apex_string.join(l_values, ',');
  DBMS_OUTPUT.put_line(l_result); -- Output: APEX,PLSQL,ORACLE
END;

---

Conclusion

The APEX_STRING package is a powerful utility for string manipulation in Oracle APEX and PL/SQL. It simplifies common tasks that otherwise would require more verbose or complex logic.

Now with methods like PUSH and SPLIT_NUMBERS, you can handle dynamic value collections/arrays more efficiently — especially when generating queries or dealing with JSON or CSV inputs.

If you're working on dynamic SQL, parsing values from forms, or building UI components in APEX, mastering APEX_STRING will definitely boost your productivity.

Let me know in the comments if you’d like a deep dive on how to use these utilities inside dynamic SQL or APEX reports!