How a Tiny Office Argument Led Us to Discover APEX_STRING.PUSH

It all started one sleepy morning, just before the first coffee kicked in. A mild but passionate debate broke out in our dev team.

The topic?
“Why can’t anyone build a clean list of items in this project that we can reuse later?”

One teammate swore by LISTAGG, another suggested using JSON arrays, and then a quiet junior dev suddenly chimed in:
"Haven’t you guys heard of APEX_STRING.PUSH?"

We all turned to him, eyebrows raised.
He said, “There’s this neat little procedure in Oracle APEX that lets you build string arrays effortlessly. Super clean, super readable. Let me show you…”

And just like that, our whole team was introduced to the power of APEX_STRING.PUSH.

Let’s walk through what this thing actually is and how it can make your code better (and your coworkers less grumpy).

---

What is APEX_STRING.PUSH?

APEX_STRING is a utility package in Oracle APEX that helps with string manipulation.

The PUSH procedure adds values to a string array (APEX_T_VARCHAR2), giving you a clean and efficient way to build lists on the fly. Think of it as the “append” operation for string tables, but with more flexibility.

There are seven variations (aka signatures) of PUSH. Each one has its unique purpose depending on what you want to push and how.

---

The 7 Signatures of APEX_STRING.PUSH

---

Signature 1

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_value in varchar2 );

Adds a single plain string to the array.

Example:

declare
  l_list apex_t_varchar2;
begin
  apex_string.push(l_list, 'apple');
  apex_string.push(l_list, 'banana');
  -- l_list = ['apple', 'banana']
end;

---

Signature 2

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_value in varchar2,
    p_strip_ws in boolean );

Same as Signature 1, but allows trimming whitespace from the value.

apex_string.push(l_list, '  orange  ', true); -- becomes 'orange'

---

Signature 3

plsqlCopyEditprocedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_value in number );

Pushes a number, automatically converts it to string.

apex_string.push(l_list, 123); -- becomes '123'

---

Signature 4

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_value in number,
    p_format in varchar2 );

Pushes a number with custom formatting.

apex_string.push(l_list, 1234.56, '9999.99'); -- becomes '1234.56'

---

Signature 5

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_values in apex_t_varchar2 );

Pushes another array into the current array.

declare
  l1 apex_t_varchar2 := apex_t_varchar2('a','b');
  l2 apex_t_varchar2 := apex_t_varchar2('c','d');
begin
  apex_string.push(l1, l2); -- l1 = ['a', 'b', 'c', 'd']
end;

---

Signature 6

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_values in apex_t_number );

Pushes an array of numbers, converting them to strings.

---

Signature 7

procedure PUSH (
    p_table in out nocopy apex_t_varchar2,
    p_value in varchar2,
    p_escape in boolean );

Adds a string with HTML or JavaScript escaping, if needed.

---

Real-World Examples You’ll Actually Use

---

Example 1: Building a SQL IN Clause Dynamically

plsqlCopyEditdeclare
  l_filter apex_t_varchar2;
  l_sql    varchar2(1000);
begin
  apex_string.push(l_filter, 'HR');
  apex_string.push(l_filter, 'IT');
  apex_string.push(l_filter, 'FINANCE');

  l_sql := 'select * from employees where department in (' ||
           apex_string.join(l_filter, ',') || ')';

  dbms_output.put_line(l_sql);
  -- Output: select * from employees where department in ('HR','IT','FINANCE')
end;

---

Example 2: Generating JSON Tags from a Dynamic List

plsqlCopyEditdeclare
  l_tags apex_t_varchar2;
  l_json clob;
begin
  apex_string.push(l_tags, 'oracle');
  apex_string.push(l_tags, 'apex');
  apex_string.push(l_tags, 'plsql');

  l_json := '{"tags":[' || apex_string.join(l_tags, ',', '"') || ']}';
  dbms_output.put_line(l_json);
  -- Output: {"tags":["oracle","apex","plsql"]}
end;

---

Final Thoughts

If you’re not using APEX_STRING.PUSH yet, you’re probably overcomplicating your life (or your code). This procedure is:

• Clean and readable

• Works great with JOIN, SPLIT, etc.

• Flexible across string and numeric values

• Ideal for SQL, JSON, PL/SQL processing

So next time you're building a list, looping over values, or constructing dynamic queries—skip the mess, and just PUSH it. And hey, maybe even impress that colleague in the next coffee break. 😉