How to Create and Use APEX Loop Directives

 Introduction

In Oracle APEX, Loop Directives are a powerful feature within the TEMPLATE_TEXT of region templates, particularly for Classic Reports and Card Reports. These directives allow developers to iterate over a collection of data rows and generate repeated HTML elements dynamically. With the ability to define reusable structures using syntax like {loop} and {endloop}, Loop Directives simplify the rendering of structured, row-based content in a clean and maintainable format. This technique is especially useful when displaying complex card layouts, lists, or custom components where uniform styling and logic need to be repeated across rows.

In Oracle APEX, Loop Directives are used within HTML templates—especially in Classic Reports and Card Reports—to dynamically repeat a block of content for each row returned by a SQL query. This gives developers a way to control the layout of repeating elements like cards, rows, and formatted blocks using clean HTML and APEX’s templating syntax.

To create and use a Loop Directive in Oracle APEX, follow these steps:

Step 1: Navigate to the Report Attributes

Go to the APEX App Builder
Open the page that contains your Classic or Card Report
Click on the report region
Under the Layout section, locate Template Type and ensure it is set to HTML Expression (Legacy) or Custom Template depending on your version

Step 2: Create or Edit the Template

Click on Shared Components
Under User Interface, select Templates
Filter by Region Templates or Report Templates, depending on where you want to apply the loop
Choose or create a template
Edit the Template Text field

Use the following basic Loop Directive structure:

{loop}
  <div class="card">
    <h2>{ENAME}</h2>
    <p>Department: {DEPTNO}</p>
  </div>
{endloop}

Here, {loop} and {endloop} define the block that will be repeated for each row of your query. {ENAME} and {DEPTNO} are substitution strings matching the columns from your SQL query.

Step 3: Reference the Template in the Report

Return to your report region
Set the Report Template to the one that uses the loop directive
Make sure your SQL Query returns the columns needed for the template:

SELECT ENAME, DEPTNO
FROM EMP

Step 4: Test and Preview

Run the page to see the repeated structure generated by the loop
Each row from the query will produce one HTML block as defined in the template

Optional Enhancements

You can use these additional template variables:

• {APEX$ITEM} – Reference a report column as an item

• {APEX$I} – Reference the current row index

• {APEX$ID} – Create unique HTML IDs per row

• {APEX$META} – Access row metadata

These make it possible to add dynamic JavaScript hooks, conditional logic, or styling based on the position of each row.

Use Cases for Loop Directives

• Custom card layouts

• Rendering media grids

• Structured profile or product displays

• Simplified HTML structure instead of default tabular format

Loop Directives give you full control over how repeated data looks, without needing to write JavaScript or manually repeat HTML for each row.

By combining SQL queries, custom HTML, and APEX Loop Directives, you can build powerful, user-friendly, and dynamic report presentations in your Oracle APEX applications.

Example

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
By incorporating APEX Loop Directives in your report templates, you unlock greater flexibility in customizing how data is displayed without the need for complex PL/SQL or JavaScript. This method improves development efficiency by enabling consistent, template-driven rendering of repeated data patterns. Whether you're building card-based layouts, custom lists, or enhanced UI components, Loop Directives offer a structured, elegant way to enhance your Oracle APEX applications.

How to Create and Use APEX CASE Directive with TEMPLATE_TEXT Using {case}, {when}, {otherwise}, and {endcase}

 In Oracle APEX, the use of Template Directives such as {case}, {when}, {otherwise}, and {endcase} within TEMPLATE_TEXT allows developers to create dynamic, conditional content inside custom templates like Card Reports or Classic Reports. These CASE directives operate similarly to a traditional CASE expression in SQL or PL/SQL, enabling conditional rendering of output depending on the value of a specific item or column. This approach helps developers display custom labels, styles, or icons based on data values without writing additional PL/SQL logic or creating multiple templates. It simplifies complex UI behavior by embedding logic directly into the template, keeping the application clean and maintainable.

In Oracle APEX, the {case}, {when}, {otherwise}, and {endcase} directives are powerful tools used within TEMPLATE_TEXT to apply conditional logic directly in your report templates. These directives enable you to control how specific portions of a report are rendered based on the value of a particular column or item. This is particularly useful in Card Reports, Classic Reports, and custom region templates where dynamic styling, icons, or text output is required.

To use the CASE directive, begin by editing the TEMPLATE_TEXT of a report template, such as a Card Region or Classic Report. You will wrap conditional logic using the {case} directive followed by one or more {when} conditions. You can also include an optional {otherwise} block to define the default behavior. Every conditional block must end with {endcase}.

Example use case: displaying a status icon based on a column named STATUS.

<span class="status-icon">
  {case STATUS}
    {when 'OPEN'}
      <i class="fa fa-circle text-success"></i>
    {when 'CLOSED'}
      <i class="fa fa-circle text-danger"></i>
    {when 'PENDING'}
      <i class="fa fa-circle text-warning"></i>
    {otherwise}
      <i class="fa fa-circle text-secondary"></i>
  {endcase}
</span>

In this example, based on the value of the STATUS column, a different Font Awesome icon color is displayed. The condition matches the value exactly as provided in the {when} directive. If no condition matches, the {otherwise} block will render the fallback content.

Steps to Implement in Oracle APEX:

1. Open App Builder and select your application.

2. Navigate to Shared Components > Templates, or directly edit a region or card report.

3. Edit the Region or Report Template, scroll to TEMPLATE_TEXT.

4. Insert your conditional logic using {case}, {when}, {otherwise}, and {endcase}.

5. Save your template and refresh the report to see it in action.

You can also nest other directives inside these blocks such as {apex$column_name} for data substitution or use HTML to customize output.

The APEX Template Engine interprets these directives at runtime, rendering the HTML output accordingly. This eliminates the need to use PL/SQL or JavaScript for display logic in many scenarios, making your reports cleaner, more performant, and easier to maintain.

Examples

In Oracle APEX, you can use CASE directives in templates to dynamically generate content based on specific conditions. The syntax {case}, {when}, {otherwise}, and {endcase} are used in APEX templates to create conditional logic, allowing you to show or hide content based on certain values or conditions.

In this tutorial, we’ll demonstrate how to use these directives to display different content dynamically in your APEX application.

Step 1: Understanding the Syntax

Here’s the basic structure of a CASE directive using TEMPLATE_TEXT with {case}, {when}, {otherwise}, and {endcase}:

{case NAME/}

    {when string1/}

        TEMPLATE_TEXT1

    {when string2/}

        TEMPLATE_TEXT2

    {otherwise/}

        TEMPLATE_TEXT

{endcase/}

• {case NAME/}: This begins the CASE block and is followed by a name or expression. The block will evaluate conditions based on the value of NAME.

• {when string1/}: This specifies a condition (string1). If NAME matches string1, the content (TEMPLATE_TEXT1) will be displayed.

• {when string2/}: This specifies another condition (string2). If NAME matches string2, the content (TEMPLATE_TEXT2) will be displayed.

• {otherwise/}: If no conditions match, this content will be displayed as the default.

• {endcase/}: This closes the CASE block.

Step 2: Example 1 - Showing Different Content Based on User Role

Suppose you want to display different messages to users based on their roles (e.g., Admin, User, Guest). You can create a CASE directive that evaluates the &APP_USER. (the current user) and shows different text depending on the role.

Steps:

1. Create a Static Content Region.

2. In the HTML Expression field, use the following template:

{case &APP_USER./}

    {when 'ADMIN'/}

        <h2>Welcome, Administrator!</h2>

        <p>You have full access to the system.</p>

    {when 'USER'/}

        <h2>Welcome, User!</h2>

        <p>You have limited access to certain features.</p>

    {otherwise/}

        <h2>Welcome, Guest!</h2>

        <p>Please log in to access more features.</p>

{endcase/}

• Explanation: This block checks the value of &APP_USER. (the logged-in user):

• If the user is 'ADMIN', it shows a message welcoming the admin.

• If the user is 'USER', it shows a message for the standard user.

• If the user is neither, it displays a default guest message.

Expected Output:

If &APP_USER. is 'ADMIN':

Welcome, Administrator!

You have full access to the system.

If &APP_USER. is 'USER':

Welcome, User!

You have limited access to certain features.

If &APP_USER. is any other value (e.g., 'GUEST'):

Welcome, Guest!

Please log in to access more features.

Step 3: Example 2 - Showing Content Based on Page Item Value

In this example, let's say you want to show different content depending on the value selected in a page item (P1_STATUS). For instance, the status could be either 'Active', 'Inactive', or 'Pending'.

Steps:

1. Create a Page Item: Create a page item called P1_STATUS with possible values: 'Active', 'Inactive', and 'Pending'.

2. Create a Static Content Region.

3. In the HTML Expression field, use the following template:

{case :P1_STATUS/}

    {when 'Active'/}

        <h2>Status: Active</h2>

        <p>The system is running smoothly.</p>

    {when 'Inactive'/}

        <h2>Status: Inactive</h2>

        <p>The system is currently inactive. Please contact support.</p>

    {when 'Pending'/}

        <h2>Status: Pending</h2>

        <p>The system status is pending. Please wait for further updates.</p>

    {otherwise/}

        <h2>Status: Unknown</h2>

        <p>The status could not be determined.</p>

{endcase/}

• Explanation: This block evaluates the value of the P1_STATUS page item:

• If P1_STATUS is 'Active', it shows a message indicating that the system is running smoothly.

• If P1_STATUS is 'Inactive', it shows a message about the system being inactive.

• If P1_STATUS is 'Pending', it shows a message about the pending status.

• If none of these values are selected, it shows a default message.

Expected Output:

If P1_STATUS is 'Active':

Status: Active

The system is running smoothly.

If P1_STATUS is 'Inactive':

Status: Inactive

The system is currently inactive. Please contact support.

If P1_STATUS is 'Pending':

Status: Pending

The system status is pending. Please wait for further updates.

If P1_STATUS is any other value:

Status: Unknown

The status could not be determined.

Step 4: Example 3 - Customizing Output Based on a Session Variable

In this example, let’s use a session variable to customize content dynamically. You can create a condition that checks whether the session variable SESSION_STATUS is 'LOGGED_IN' or 'LOGGED_OUT' to display different messages.

Steps:

1. Set a session variable: Set the SESSION_STATUS session variable to 'LOGGED_IN' or 'LOGGED_OUT' based on the user’s login state.

2. Create a Static Content Region.

3. In the HTML Expression field, use the following template:

{case :SESSION_STATUS/}

    {when 'LOGGED_IN'/}

        <h2>Welcome Back!</h2>

        <p>You're logged in and ready to go.</p>

    {when 'LOGGED_OUT'/}

        <h2>Logged Out</h2>

        <p>You have been logged out. Please log in again to continue.</p>

    {otherwise/}

        <h2>Status Unknown</h2>

        <p>Your session status could not be determined. Please try again later.</p>

{endcase/}

• Explanation: This block evaluates the session variable SESSION_STATUS:

• If SESSION_STATUS is 'LOGGED_IN', it shows a welcome message.

• If SESSION_STATUS is 'LOGGED_OUT', it shows a message indicating the user is logged out.

• If neither condition is met, it shows a default "status unknown" message.

Expected Output:

• If SESSION_STATUS is 'LOGGED_IN':

• Welcome Back!

• You're logged in and ready to go.

• If SESSION_STATUS is 'LOGGED_OUT':

• Logged Out

• You have been logged out. Please log in again to continue.

• If SESSION_STATUS has any other value:

• Status Unknown

• Your session status could not be determined. Please try again later.

Step 5: Using Dynamic Content with CASE Directive in Regions

You can dynamically change the content of entire regions based on conditions. The CASE directive can be used to show or hide entire regions or provide different content within regions depending on the condition being evaluated.

For instance, you can conditionally show a region only for certain user types or based on a condition.

Example Code:

{case :USER_ROLE/}

    {when 'ADMIN'/}

        <h2>Admin Dashboard</h2>

        <p>You have full control over the application settings and users.</p>

    {when 'MANAGER'/}

        <h2>Manager Dashboard</h2>

        <p>You can manage your team's performance and reports.</p>

    {otherwise/}

        <h2>User Dashboard</h2>

        <p>Access basic application features.</p>

{endcase/}

Expected Output:

• If USER_ROLE is 'ADMIN':

• Admin Dashboard

• You have full control over the application settings and users.

• If USER_ROLE is 'MANAGER':

• Manager Dashboard

• You can manage your team's performance and reports.

• If USER_ROLE is anything else:

• User Dashboard

• Access basic application features.

By applying these techniques, you can provide a personalized experience to your users by displaying the right content based on various conditions, ensuring a more dynamic and interactive user interface.

Key Takeaways:

• {case NAME/} begins the conditional block and evaluates the value of NAME.

• {when value/} is used to specify a condition and display content when the condition is met.

• {otherwise/} defines the default content when no conditions match.

{endcase/} closes the CASE block.

By incorporating the {case} directive in your TEMPLATE_TEXT, you can control the display based on a column's value—for example, showing different icons based on a status field, or applying different color classes. This technique not only enhances the end-user experience but also significantly reduces the need for external logic or JavaScript manipulation. Once you become familiar with how the {case} directive functions, it becomes a powerful part of your Oracle APEX template toolkit, enabling rich data-driven displays in a compact, readable format.