The Power of APEX_STRING: A Deep Dive into 7 Essential Functions

Mahdi AhmadiMahdi Ahmadi
4 min read

Today’s weather is just perfect! ☀️ A cool breeze, soft sunshine — it's one of those days where everything feels aligned, and I’m fully energized to write a killer blog post!
Let’s dive into one of the most practical, underrated packages in Oracle APEX: APEX_STRING.
Whether you're parsing CSV data, working with long text, or transforming strings into tables (and vice versa), this package is a must-have in your PL/SQL toolbox.


What is APEX_STRING?

APEX_STRING is a built-in PL/SQL package in Oracle APEX designed to simplify operations with strings, CLOBs, lists, and numbers — especially when working with delimited data.
It shines when you need to:

  • Split delimited strings into rows

  • Combine table rows into a single string or CLOB

  • Convert string-based lists into arrays usable in SQL queries

Let’s explore 7 powerful and frequently used functions from this package.


1. SPLIT Function – Signature 1

APEX_STRING.SPLIT(p_str IN VARCHAR2, p_sep IN VARCHAR2 DEFAULT ':') RETURN APEX_T_VARCHAR2;

What it does:

Splits a delimited string into a table of strings (e.g., 'a:b:c' → ['a','b','c'])

Use Cases:

Parsing list inputs, filter parameters, dynamic reports.

Example:

SELECT column_value
FROM TABLE(apex_string.split('oracle:apex:plsql', ':'));

Result:

oracle
apex
plsql

2. SPLIT Function – Signature 2

APEX_STRING.SPLIT(p_str IN VARCHAR2, p_sep IN VARCHAR2, p_limit IN PLS_INTEGER) RETURN APEX_T_VARCHAR2;

What it does:

Same as Signature 1, but limits the number of splits. Remaining content goes into the last item.

Example:

SELECT column_value
FROM TABLE(apex_string.split('a:b:c:d:e', ':', 3));

Result:

a
b
c:d:e

Use Case:
When you only need the first few parts and want to keep the rest intact.


3. SPLIT_CLOBS Function

APEX_STRING.SPLIT_CLOBS(p_clob IN CLOB, p_sep IN VARCHAR2 DEFAULT chr(10)) RETURN APEX_T_VARCHAR2;

What it does:

Splits a CLOB into a list of strings, typically by newline or any separator.

Use Case:

Working with large text (logs, files, responses) and parsing line-by-line.

Example:

DECLARE
  l_clob CLOB := 'line1'||chr(10)||'line2'||chr(10)||'line3';
BEGIN
  FOR rec IN (
    SELECT column_value FROM TABLE(apex_string.split_clobs(l_clob))
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.column_value);
  END LOOP;
END;

4. SPLIT_NUMBERS Function

APEX_STRING.SPLIT_NUMBERS(p_str IN VARCHAR2, p_sep IN VARCHAR2 DEFAULT ':') RETURN APEX_T_NUMBER;

What it does:

Converts a string like '10:20:30' into a table of numbers.

Use Case:

Filtering queries by numeric list from a string input.

Example:

SELECT column_value
FROM TABLE(apex_string.split_numbers('100,200,300', ','));

Result:

100
200
300

5. STRING_TO_TABLE Function

APEX_STRING.STRING_TO_TABLE (
  p_string     IN VARCHAR2,
  p_separator  IN VARCHAR2 := ','
) RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;

What it does:

Converts a delimited string into an APEX_APPLICATION_GLOBAL.VC_ARR2 array.

Use Case:

Used frequently in dynamic PL/SQL processes, especially with APEX components like checkboxes or multiselect items.

Example:

DECLARE
  l_arr apex_application_global.vc_arr2;
BEGIN
  l_arr := apex_string.string_to_table('red,green,blue', ',');
  FOR i IN 1 .. l_arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(l_arr(i));
  END LOOP;
END;

6. TABLE_TO_CLOB Function

APEX_STRING.TABLE_TO_CLOB(
  p_table        IN  APEX_T_VARCHAR2,
  p_sep          IN  VARCHAR2 := chr(10)
) RETURN CLOB;

What it does:

Joins elements of a string table into a single CLOB using a custom separator.

Example:

DECLARE
  l_clob CLOB;
BEGIN
  l_clob := apex_string.table_to_clob(
    apex_string.split('line1|line2|line3', '|'),
    chr(10)
  );
  DBMS_OUTPUT.PUT_LINE(l_clob);
END;

7. TABLE_TO_STRING Function

APEX_STRING.TABLE_TO_STRING (
  p_table       IN APEX_T_VARCHAR2,
  p_sep         IN VARCHAR2 := ','
) RETURN VARCHAR2;

What it does:

Combines a string table into a single delimited string.

Use Case:

Sending lists as a single value or saving them in a VARCHAR2 column.

Example:

DECLARE
  l_list apex_t_varchar2 := apex_string.split('a|b|c', '|');
  l_result VARCHAR2(4000);
BEGIN
  l_result := apex_string.table_to_string(l_list, ', ');
  DBMS_OUTPUT.PUT_LINE(l_result);  -- Output: a, b, c
END;

Real-world APEX Use Case

Let's say you're using a multi-select checkbox item in an APEX form and want to filter records based on selected IDs.

SELECT *
FROM customers
WHERE customer_id IN (
  SELECT column_value
  FROM TABLE(apex_string.split_numbers(:P1_SELECTED_IDS, ','))
);

This makes your APEX page dynamic, responsive, and efficient with minimal code.


Summary Table

FunctionPurposeOutput Type
SPLITSplit delimited stringAPEX_T_VARCHAR2
SPLIT_CLOBSSplit large CLOB into linesAPEX_T_VARCHAR2
SPLIT_NUMBERSParse numeric string listAPEX_T_NUMBER
STRING_TO_TABLEString → array for APEX useVC_ARR2
TABLE_TO_CLOBCombine rows into CLOBCLOB
TABLE_TO_STRINGCombine rows into stringVARCHAR2

Final Thoughts

The APEX_STRING package is more than just a utility — it's a powerhouse for transforming and managing data structures in Oracle APEX.
Use it to boost performance, clean up your logic, and write more elegant code.

0
Subscribe to my newsletter

Read articles from Mahdi Ahmadi directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Mahdi Ahmadi
Mahdi Ahmadi

Founder & CEO at Artabit | Oracle APEX Expert | Building Innovative HR Solutions | UAE & Iran