How to Create and Use APEX Loop Directives

 

Introduction
Oracle APEX enables developers to build dynamic and data-rich user interfaces using template directives in region templates. One of the most powerful and flexible tools available is the loop directive, which allows you to iterate through collections or delimited strings directly within a TEMPLATE_TEXT. This approach is ideal for displaying repeating content inside a single column or report row, such as a list of tags, attachments, or user roles.

How to Create and Use APEX Loop Directives

The {loop} directive in Oracle APEX lets you loop over a delimited list or array-like data from your SQL query and output multiple elements based on that loop. It is often used inside Cards, Classic Reports, and other custom template regions.

Basic Syntax Example

<ul>
  {loop TAGS}
    <li>{TAG}</li>
  {endloop}
</ul>

Here, TAGS is the column from your SQL query that contains delimited data (e.g., 'Oracle,APEX,PLSQL'), and {TAG} represents the value in each iteration.

Step-by-Step: How to Implement Loop Directive in Oracle APEX

1. Prepare the SQL Source
   Format your data to return a delimited string in a column. For example:

   SELECT
     POST_TITLE,
     'Oracle,APEX,PLSQL' AS TAGS
   FROM BLOG_POSTS
   

2. Enable Template Customization

   • Go to your Classic Report or Cards region.

   • Under “Appearance,” enable custom TEMPLATE_TEXT.

3. Insert Loop Directive

   • Use {loop COLUMN_NAME} to begin the loop.

   • Reference the current item using {COLUMN_NAME_ITEM} or define a name using {as variable} syntax:

     {loop TAGS as TAG}
       <span class="badge badge-primary">{TAG}</span>
     {endloop}
     

4. Control the Delimiter (Optional)
   By default, the loop splits using a comma (,). You can define a different delimiter using:

   {loop TAGS delimiter:";" as TAG}
     {TAG}
   {endloop}
   

5. Example Output in a Card Template

   <div class="card">
     <div class="card-body">
       <h5>{POST_TITLE}</h5>
       <div>
         {loop TAGS as TAG}
           <span class="badge badge-info">{TAG}</span>
         {endloop}
       </div>
     </div>
   </div>
   

Best Practices

• Use simple delimiters like commas or semicolons in the database column.

• Avoid complex or nested delimiters unless absolutely necessary.

• Normalize and clean your delimited string in SQL using REPLACE or REGEXP_REPLACE to ensure consistent output.

• Keep {loop} content minimal and fast-rendering for performance.

• Prefer {loop} for output use only — complex logic should remain in SQL or PL/SQL.

Oracle APEX Documentation
You can find the official Oracle APEX documentation on Template Directives here:
Oracle APEX Template Directives Guide

 

In Oracle APEX, loop directives allow you to repeat a section of content multiple times. They are particularly useful when you want to display multiple rows of data dynamically in regions, reports, or templates.

What are Loop Directives?

The loop directive in APEX follows the format {loop}, {endloop}, and is used to iterate through a collection of items, such as a list, an array, or a result set from a query. It is a way to repeat content for each item in the collection.

There are two types of loop syntax in APEX:

1. Loop using a "SEP" separator:

{loop ["SEP"] NAME/}

    TEMPLATE_TEXT

{endloop/}

Loop using a "MODEL_ID" reference:

{loop MODEL_ID/}

    TEMPLATE_TEXT

{endloop/}

In both cases, the content defined inside {loop} is repeated for each item in the collection, and the loop will automatically stop when all the items have been processed.

Step 1: Loop Using "SEP" Separator

This loop directive is often used when you want to iterate through a collection of values and insert separators between items.

Syntax Explanation:

• {loop ["SEP"] NAME/}: The SEP represents a separator that will be placed between each iteration (like a comma, space, or other separator). The NAME is the collection that you want to loop through.

• {endloop/}: Marks the end of the loop.

Example 1: Loop with Separator for a List of Tags

Suppose you want to display a list of tags associated with an article, separated by commas.

1. Define a Collection: Assume that you have a page item or session variable that stores a list of tags, like this: 'APEX, Oracle, SQL, Data, Reports'.

2. Create a Static Content Region.

3. Use the loop directive in the HTML Expression:

{loop [", "] :P1_TAGS/}

    <span class="tag">{ITEM}</span>

{endloop/}

• Explanation: This example loops through the tags stored in the page item :P1_TAGS, and it separates each tag with a comma (,). Each tag is wrapped in a <span> element for styling.

• :P1_TAGS: This represents a page item that contains the list of tags, and ITEM is used to represent each tag in the loop.

Expected Output:

For a list like 'APEX, Oracle, SQL, Data, Reports', the output will be:

<span class="tag">APEX</span>, <span class="tag">Oracle</span>, <span class="tag">SQL</span>, <span class="tag">Data</span>, <span class="tag">Reports</span>

Step 2: Loop Using "MODEL_ID" Reference

The second syntax for looping uses a MODEL_ID, which refers to a specific collection or dataset, often linked to a query result or model.

Syntax Explanation:

• {loop MODEL_ID/}: This will loop over the results of the model or collection identified by MODEL_ID.

• {endloop/}: Marks the end of the loop.

Example 2: Loop through Query Results

Let’s say you have a list of orders in your database and want to display them in a formatted list.

1. Create a SQL Query to fetch order data:

SELECT order_id, order_date, amount

FROM orders

WHERE order_status = 'SHIPPED'

ORDER BY order_date;

2. Create a Static Content Region in APEX.

3. Use the loop directive to display order information in the HTML Expression:

{loop ORDERS/}

    <div class="order">

        <h3>Order #{ORDER_ID}</h3>

        <p>Date: {ORDER_DATE}</p>

        <p>Amount: {AMOUNT}</p>

    </div>

{endloop/}

• Explanation: In this case, the ORDERS model refers to the result set from the query that retrieves orders. The loop will go through each row of the query result and output the ORDER_ID, ORDER_DATE, and AMOUNT for each order.

• {ORDER_ID}, {ORDER_DATE}, {AMOUNT}: These are the data points from the query that will be rendered dynamically within the loop.

Expected Output:

If the query returns the following rows:

ORDER_ID	ORDER_DATE	AMOUNT
1001	2025-03-20	250
1002	2025-03-21	450
1003	2025-03-22	350

The output will be:

<div class="order">

    <h3>Order #1001</h3>

    <p>Date: 2025-03-20</p>

    <p>Amount: 250</p>

</div>

<div class="order">

    <h3>Order #1002</h3>

    <p>Date: 2025-03-21</p>

    <p>Amount: 450</p>

</div>

<div class="order">

    <h3>Order #1003</h3>

    <p>Date: 2025-03-22</p>

    <p>Amount: 350</p>

</div>

Step 3: Combining Both Loop Types for Dynamic Data Rendering

You can combine the use of both loop types (with separator and model) to display dynamic content in a more complex scenario.

Example 3: Looping Through Product Categories and Products

Imagine you have a list of product categories, and each category contains a list of products. You can use the loop directives to display categories and their associated products.

1. Create a SQL Query to fetch product categories and products:

SELECT category_name, product_name

FROM products

ORDER BY category_name, product_name;

2. Create a Static Content Region and use the following HTML Expression:

{loop PRODUCTS/}

    <h3>{CATEGORY_NAME}</h3>

    <ul>

        {loop [", "] :PRODUCT_NAMES/}

            <li>{ITEM}</li>

        {endloop/}

    </ul>

{endloop/}

• Explanation: The outer loop ({loop PRODUCTS/}) will iterate over the categories, and the inner loop ({loop [", "] :PRODUCT_NAMES/}) will loop through the list of products in each category. The :PRODUCT_NAMES can be a collection or model that holds product names for each category.

Expected Output:

For categories like "Electronics" with products like "Laptop", "Smartphone", and "Headphones", and "Clothing" with "Shirt", "Jeans":

<h3>Electronics</h3>

<ul>

    <li>Laptop</li>, <li>Smartphone</li>, <li>Headphones</li>

</ul>

<h3>Clothing</h3>

<ul>

    <li>Shirt</li>, <li>Jeans</li>

</ul>

Step 4: Using Loop with Dynamic Data Models

You can create loops that iterate over dynamic data models in APEX. APEX’s Interactive Reports, Classic Reports, and Collections are great sources of dynamic data that can be used with the loop directive.

For example, if you have a Dynamic Action or SQL Query generating data, you can bind it directly to a loop structure.

Example 4: Looping Through a Dynamic Collection

Suppose you have a collection of items, and you want to display them dynamically:

1. Create a collection in APEX:

• Use the APEX_COLLECTION.CREATE_COLLECTION function to create a collection with items.

2. Loop through the collection using the loop directive:

{loop ITEM_COLLECTION/}

    <p>{ITEM_NAME}</p>

{endloop/}

This loops through each item in the collection and displays its name.

Key Takeaways:

• {loop ["SEP"] NAME/}: Loops through a collection with a separator between each item.

• {loop MODEL_ID/}: Loops through a collection or dataset defined by MODEL_ID.

• Use in Reports: Loop directives can dynamically display data in reports, collections, or interactive regions.

• Data Binding: Bind loop directives to collections, page items, or query results to render dynamic content.

With these techniques, you can create more interactive, dynamic, and personalized pages within your Oracle APEX applications.

Conclusion
The {loop} directive in Oracle APEX empowers developers to iterate through list-based values inside region templates with minimal code. Whether you're displaying user tags, related items, or sets of values, loops help you maintain clean markup and flexible logic without writing JavaScript or custom PL/SQL. Use this feature wisely to create interactive, data-rich reports that enhance user experience while keeping development declarative and efficient.