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


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
Function | Purpose | Output Type |
SPLIT | Split delimited string | APEX_T_VARCHAR2 |
SPLIT_CLOBS | Split large CLOB into lines | APEX_T_VARCHAR2 |
SPLIT_NUMBERS | Parse numeric string list | APEX_T_NUMBER |
STRING_TO_TABLE | String → array for APEX use | VC_ARR2 |
TABLE_TO_CLOB | Combine rows into CLOB | CLOB |
TABLE_TO_STRING | Combine rows into string | VARCHAR2 |
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.
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