🧩Substitution Strings in Oracle APEX You (Probably) Aren’t Using — But Should Be

Introduction

We all use substitution strings in Oracle APEX like &APP_USER. or &APP_PAGE_ID. — they're everywhere. But did you know there are some less talked-about substitution strings that can provide powerful debugging, performance insight, or even UI flexibility?

In this article, I’ll walk you through some hidden gems like #TIMING#, #ROWS_FETCHED#, #CUSTOMIZE#, and others — explaining what they do and where you can use them to your advantage.

---

🔍 What Are Substitution Strings?

Substitution strings in APEX are special tokens — like &ITEM. or #TOKEN# — that APEX replaces automatically at runtime. They can be used in regions, templates, reports, text messages, and more.

The ones starting with # are usually available in region templates, report templates, and themes, not inside PL/SQL or SQL code (except in places like Classic Reports or Static Content).

Let’s look at some of the lesser-known but useful ones 👇

---

🧠 Useful Substitution Strings You Should Know

---

1. #TIMING#

What it does:
Replaced with the time (in seconds) it took APEX to render the region.

Where to use:
Place it in the footer of a Classic Report or Region template to get performance feedback.

Use case:
You're debugging a page with multiple regions and want to know which one is the bottleneck.

Example:

Rendered in: #TIMING# seconds

---

2. #ROWS_FETCHED#

What it does:
Returns the number of rows fetched by a report region.

Where to use:
Classic Reports or IR templates.

Use case:
You want to show how many records were returned from the SQL query (without writing PL/SQL logic for it).

Example:

Total rows: #ROWS_FETCHED#

---

3. #TOTAL_ROWS#

What it does:
Returns the total number of rows that match the query — even if pagination is enabled.

Where to use:
Classic Reports.

Use case:
Useful when pagination is turned on and you want to show:

“Showing 10 of 253 total records”

Example:

Showing #ROWS_FETCHED# of #TOTAL_ROWS# rows

⚠️ Not all templates support this substitution by default. You may need to manually add it in your report region footer or template.

---

4. #FIRST_ROW_FETCHED#

What it does:
Returns the time (in seconds) it took APEX to fetch the first row from the query.

Use case:
Use it alongside #TIMING# to compare backend (fetching) vs frontend (rendering) performance.

Example:

First row in: #FIRST_ROW_FETCHED# s — Total render: #TIMING# s

---

5. #CUSTOMIZE#

What it does:
Displays the “Customize” link in Interactive Reports (IRs) or Interactive Grids (IGs).

Where to use:
In the region header or templates of IR/IG regions.

Use case:
If you build a custom header template or need to relocate the IR/IG customize link to another part of the UI.

Example:

<div class="custom-toolbar">
  #CUSTOMIZE#
</div>

---

⚙️ Bonus: Other Cool Substitution Strings

#OWNER#

Shows the schema owner of the region’s query. Rarely used but available in Classic Reports.

#APEX$ROW_NUM#

Returns the current row number in a report loop (especially for Classic Reports using HTML expressions).

#REGION_STATIC_ID#

Resolved to the region’s static ID — great for applying scoped JS or CSS.

---

📦 Where Can You Use These?

• Classic Report Templates

• Interactive Report Templates

• Region Footer/Header

• HTML Expressions

• Static Content Regions

• Theme Components (like Cards or Templates)

---

🧪 Practical Example

Let’s say you have a Classic Report listing employees. You want to show:

• How long the region took to render

• How many rows were fetched

• A note if performance exceeds a threshold

In the footer of the region, you could write:

Fetched #ROWS_FETCHED# employees in #TIMING# seconds.

And if you want to highlight slow render times, go further with a dynamic expression region or client-side logic using #TIMING# with JS.

---

🧩 Conclusion

Substitution strings are more than just &APP_USER. or &APP_ID. — they’re part of what makes Oracle APEX flexible and fast to develop with.

The next time you're building a custom UI, report, or diagnostic tool, consider using:

• #TIMING# for performance insights

• #ROWS_FETCHED# and #TOTAL_ROWS# for user feedback

• #CUSTOMIZE# to bring back features that may be hidden in custom layouts

---

🔁 Know another hidden substitution string? Drop it in the comments!