How to Create and Use APEX Style Templates with Data Substitutions

 Introduction

Creating and using APEX Style Templates with data substitutions is an essential skill for Oracle APEX developers who want to build visually appealing and dynamic applications. APEX Style Templates define how UI components—such as regions, cards, reports, and buttons—are rendered, using HTML and CSS to control layout and appearance. By incorporating data substitutions, developers can inject dynamic content into templates, making the output responsive to the data being presented. This approach not only ensures consistency across pages but also allows greater flexibility in customizing how information is displayed at runtime.

In Oracle APEX, Style Templates are powerful tools that define the structure and presentation of components such as Cards, Classic Reports, or Regions. By using data substitution strings within these templates, developers can inject dynamic values directly into the HTML layout, making the interface both flexible and data-aware. This technique is especially useful when displaying values from queries in a visually styled manner, such as in Card Reports or custom HTML regions.

To create and use APEX Style Templates with data substitutions, follow these detailed steps:

1. Navigate to Shared Components:
From the Application Builder, click into your application, then navigate to Shared Components. Under the “User Interface” section, select Templates.

2. Create a New Template:
Click Create and choose the type of template you want to create (e.g., Report Template for Classic Report or Card Template for Card Report). Select a suitable name like CustomCardTemplate.

3. Define Template Markup:
In the template editor, define the HTML layout for how each row should be rendered. Use substitution strings to represent column data. For example:

<div class="card">
  <h2>#NAME#</h2>
  <p>#DESCRIPTION#</p>
  <span class="status">#STATUS#</span>
</div>

In this example, #NAME#, #DESCRIPTION#, and #STATUS# are substitution strings that match the column aliases from your SQL query.

4. Add Classes and Styling:
To ensure a clean layout, you can define custom CSS classes. You can do this within the Theme Roller or add inline styles in your template or Static Application Files.

Example CSS:

.card {
  border: 1px solid #ccc;
  border-radius: 8px;
  padding: 16px;
  margin-bottom: 12px;
}
.status {
  font-weight: bold;
  color: green;
}

5. Use the Template in a Report or Card Region:
Go back to the page where you want to display your data. Add a Classic Report or Cards region. In the Attributes section of the region:

• For Cards, under Appearance, select your new custom Card Template.

• For Classic Reports, select your custom Report Template under the Layout options.

6. Write SQL with Matching Column Aliases:
The SQL you write must return columns that match the substitution strings defined in your template. For example:

SELECT name AS NAME,
       description AS DESCRIPTION,
       status AS STATUS
FROM projects;

7. Preview the Page:
Run the page to see your report or card layout using the custom HTML structure you defined. If the substitution strings and SQL columns match correctly, the placeholders will be replaced with actual values.

8. Optional – Add Conditional Formatting or Links:
You can embed conditional logic or links using standard HTML and APEX link syntax. Example with a link:

<a href="f?p=&APP_ID.:10:&SESSION.::&DEBUG.::P10_ID:#PROJECT_ID#">
  <h2>#NAME#</h2>
</a>

This uses #PROJECT_ID# to dynamically link to another form page, passing the ID as a parameter.

By mastering the creation and use of APEX Style Templates with data substitutions, you gain full control over how your data is visually presented, allowing you to build elegant and user-friendly interfaces tailored to the needs of your application.

Example

In Oracle APEX, Data Substitutions allow you to insert dynamic data from your application’s database or context into your HTML templates or region templates. This is an extremely useful feature for customizing reports, forms, card regions, and other UI elements by embedding values directly from your data source.

This tutorial will walk you through how to create APEX Style Templates that incorporate Data Substitutions. You will learn how to:

• Use APEX Substitution Variables in templates.

• Customize templates with dynamic data.

• Apply the templates to different APEX regions.

By the end of this tutorial, you'll be able to leverage dynamic data in your templates, providing a more flexible and data-driven user interface.

Step 1: Understanding APEX Substitution Variables

APEX Substitution Variables are placeholders that represent the values of your database columns or session data. When rendering a page, APEX replaces these variables with actual values from your SQL query, session, or application.

Some common substitution variables include:

• &COLUMN_NAME.: Replaces with the value of a column from your query.

• &APP_ID.: Replaces with the current application ID.

• &SESSION.: Represents the current session ID.

• &USER.: Represents the current APEX user.

Example of Substitution Variable Usage:

Let’s assume we have an employee table with columns like employee_id, first_name, last_name, and job_title. The SQL query might look like this:

SELECT employee_id, first_name, last_name, job_title FROM employees

In the template, you could use substitution variables like &FIRST_NAME., &LAST_NAME., and &JOB_TITLE. to insert employee data into a custom card layout.

Step 2: Creating a New APEX Application (if not already created)

1. Log in to your Oracle APEX workspace.

2. From the App Builder, click Create to start a new application or navigate to an existing one.

3. Create a simple page with a Classic Report or Card Report to test data substitutions. For example, a Card Report that lists employees.

Step 3: Applying Predefined APEX Style Templates with Data Substitutions

Oracle APEX provides predefined templates that you can customize to include substitution variables. For this example, we will customize a Card Report template to display dynamic employee information.

Example: Using Substitution Variables in Card Report Template

1. Create a Card Report Region:

• In the App Builder, select your page or create a new page with a Card Report region.

• Set the SQL Query for the Card Report. Example:

• SELECT employee_id, first_name, last_name, job_title, department_name, salary FROM employees

2. Edit the Card Report Template:

• After creating the report, go to the Region properties for the Card Report.

• Under Region Attributes, locate the Template dropdown. Select Card - Simple or any other predefined card template.

• To customize the template with data substitutions, click on the Template name to edit it.

3. Customize the Template with Substitution Variables: In the HTML Template area, you can add your APEX substitution variables. For example:

<div class="custom-card">

    <div class="custom-card-header">

        <h3 class="card-title">&FIRST_NAME. &LAST_NAME.</h3>

    </div>

    <div class="custom-card-body">

        <p><strong>Job Title:</strong> &JOB_TITLE.</p>

        <p><strong>Department:</strong> &DEPARTMENT_NAME.</p>

        <p><strong>Salary:</strong> $&SALARY.</p>

    </div>

    <div class="custom-card-footer">

        <a href="f?p=&APP_ID.:DETAILS:&SESSION.::NO::P1_EMPLOYEE_ID:&EMPLOYEE_ID." class="btn btn-primary">View Details</a>

    </div>

</div>

Explanation:

• &FIRST_NAME., &LAST_NAME., &JOB_TITLE., etc., are APEX substitution variables that will be replaced with the actual values for each row in your query.

• The View Details button includes a link to a detail page, passing the employee_id as a parameter.

4. Save the Changes and Preview the Page:

• Save the template, and run the application.

• The Card Report will now display employee data with dynamically substituted values (e.g., employee name, job title, department, salary).

Step 4: Customizing the Card Report with Additional Data Substitutions

You can use more advanced data substitutions within your templates, including conditional logic and additional columns from your query.

Example 1: Conditional Formatting with Substitutions

You can conditionally style elements using APEX substitution variables and HTML expressions.

Example: Change the card's background color based on salary:

<div class="custom-card" style="background-color: &IF &SALARY. > 70000 THEN 'lightgreen'; ELSE 'lightcoral'; END IF;">

    <div class="custom-card-header">

        <h3 class="card-title">&FIRST_NAME. &LAST_NAME.</h3>

    </div>

    <div class="custom-card-body">

        <p><strong>Job Title:</strong> &JOB_TITLE.</p>

        <p><strong>Department:</strong> &DEPARTMENT_NAME.</p>

        <p><strong>Salary:</strong> $&SALARY.</p>

    </div>

    <div class="custom-card-footer">

        <a href="f?p=&APP_ID.:DETAILS:&SESSION.::NO::P1_EMPLOYEE_ID:&EMPLOYEE_ID." class="btn btn-primary">View Details</a>

    </div>

</div>

Explanation:

• The IF statement changes the background color of the card depending on the employee's salary. If the salary is greater than 70,000, the background is light green; otherwise, it is light coral.

Example 2: Adding an Image (with Substitution Variables)

You can also use substitution variables for images or links. For example, you may want to display an employee's photo.

If your table includes a photo_url column, you can dynamically insert the image in the template:

<div class="custom-card">

    <div class="custom-card-header">

        <img src="&PHOTO_URL." alt="Employee Photo" class="employee-photo">

        <h3 class="card-title">&FIRST_NAME. &LAST_NAME.</h3>

    </div>

    <div class="custom-card-body">

        <p><strong>Job Title:</strong> &JOB_TITLE.</p>

        <p><strong>Department:</strong> &DEPARTMENT_NAME.</p>

        <p><strong>Salary:</strong> $&SALARY.</p>

    </div>

</div>

Explanation:

• &PHOTO_URL. is a substitution variable that dynamically loads the employee's photo from the photo_url column in the query result.

Step 5: Applying Custom CSS for Styling

You can further style your templates by adding custom CSS. For example, you can style the card header, body, or footer differently depending on the type of data or the design you want.

1. Navigate to the Page’s CSS:

• In App Builder, go to the page where you want to apply custom styles.

• Under the Page Attributes section, find the Inline CSS field.

2. Add Custom CSS: Example CSS to enhance the card design:

.custom-card {

    border: 2px solid #ccc;

    border-radius: 10px;

    padding: 20px;

    margin: 15px;

    box-shadow: 0px 4px 6px rgba(0, 0, 0, 0.1);

}

.custom-card-header {

    background-color: #f4f4f4;

    text-align: center;

    padding: 10px;

}

.custom-card-footer {

    text-align: center;

    margin-top: 15px;

}

.employee-photo {

    width: 60px;

    height: 60px;

    border-radius: 50%;

}

.btn-primary {

    background-color: #4CAF50;

    color: white;

    padding: 10px 20px;

    border-radius: 5px;

}

3. Save and Preview:

• Save the CSS changes and run the page again to see the updated design.

Step 6: Testing and Debugging

1. Test the Page:

• Run the application and inspect the rendered card report.

• Verify that the substitution variables are correctly replaced with the data from your SQL query.

• Check for any styling issues, especially with conditional formatting or dynamic content.

2. Debug with Browser Tools:

• Use browser developer tools (F12) to inspect the HTML and see if the substitution variables are properly replaced.

• If any dynamic content is not showing up as expected, check the region’s template and ensure that the substitution variables are correct.

Using APEX Style Templates with Data Substitutions allows you to create dynamic and data-driven applications. With substitution variables, you can

easily insert real-time data from your database into regions, reports, cards, and forms. This tutorial has shown you how to:

• Create and use predefined APEX templates.

• Customize templates with substitution variables.

• Apply dynamic content and conditional formatting.

By following these steps, you can build richer, more dynamic UIs that respond to real-time data, making your applications more interactive and user-friendly.

Conclusion

APEX Style Templates with data substitutions empower developers to design reusable, dynamic, and visually consistent components across their applications. By leveraging substitution strings like #TITLE#, #BODY#, or custom columns, developers can control how data is embedded within the UI layout. Once created, these templates reduce repetitive formatting tasks, simplify maintenance, and enhance user experience. Whether you're building card layouts, report rows, or custom buttons, style templates with substitutions are a cornerstone of effective APEX application design.