Viscosity
logo-black
  • Data
    • Database Services
      • Performance Assessment
      • Proactive Healthcheck
      • Database Migration & Consolidation
      • Performance Tuning
    • Microsoft Services
      • Microsoft Azure Services
      • Microsoft SQL Server Services
      • Microsoft Gold Azure Partner
    • Oracle Services
      • Oracle Database 19c
      • RAC
      • GoldenGate
      • Data Guard
      • Oracle & SQL Database
    • Viscosity Remote Services
  • Apps
    • App Dev Services
    • Oracle APEX
    • Viscosity AMP
    • Viscosity-ai
    • Shane-ai
  • Cloud
    • Cloud Services
    • Hybrid Cloud
    • Viscosity Edge Cloud
    • Virtualization & Cloud Expertise
    • Microsoft Azure Services
  • Infrastructure
    • Infrastructure Services
    • Exadata
      • Exadata Resale & Services
    • Oracle Database Appliance
      • Oracle Database Appliance Resale & Services
      • ODA Health Checks
    • Zero Data Loss Recovery Appliance
    • VMware to KVM Migration
  • Events
    • Upcoming Events
    • Virtual Training
    • 2025 Past Events
    • 2024 Past Events
    • 2023 Past Events
    • 2022 Past Events
  • About
    • About Us
    • News
    • Blogs
    • Publications
    • Contact
Hit enter to search or ESC to close
ENGAGE WITH US

APEX , interactive grid , dynamic report , APEX customization , dynamic grid

Best Practices and Tips for APEX Development: Part III - Dynamic Grids and Reports

By Julio Flores
November 04, 2024

Continuing with our focus on APEX Grids and Reports customizations, this final post of the series dives into advanced techniques for creating dynamic grids and reports. Here, we'll cover how to use escape filters for substitution strings and how to build fully dynamic grids or reports that show and hide columns with unique names.

 

Escape Characters

If you want a column with dynamic HTML generated by a PL/SQL function within your Interactive Grid (IG), assign the value to a hidden column and reference it in the same or another column. For example:

 

function (options) {
  options.defaultGridColumnOptions = {
    cellTemplate:
    `
    &CUSTOM_HTML_COL.
    `
  };
  return options;
}

 

The column could be referenced without problems in a report; uncheck the “Escape special characters” option in the Security part of the column with the custom HTML, and the content will show as HTML. However, since IGs lack this option, the only way to get the HTML raw text is to use the escape filters, in this case, we can use the ‘RAW’ escape filter to get the original value like this: 

 

function (options) {
  options.defaultGridColumnOptions = {
    cellTemplate:
    `
    &CUSTOM_HTML_COL!RAW.
    `
  };
  return options;
}

 

Check out the Oracle APEX documentation for additional escape filters like ‘HTML’ and ‘JS.’ The ‘RAW’ filter is highly effective, but consider the other filters depending on your context. All of these are available to use in Items, IG columns, or report columns. 

 

Creating a Dynamic Grid or Report

There are some limitations on how dynamic an IG or IR can be, but this should take care of your needs in most cases. Both of these have a limit of 200 columns that you can use, either visible or hidden. For this example, we will use those 200 columns to dynamically show a preview of a CSV uploaded with the data, with column names the same as the file and only showing the uploaded columns, not the 200 columns. 

 

First, you will need to create your IG defining the 200 columns; for this to work, the report or grid has to define the maximum number of columns you will need so APEX can generate the metadata. In this case, we will take the uploaded file from the temporary table APEX uses to parse the CSV file like this:

 

SELECT line_number, col001, col002, ... , col199
FROM apex_application_temp_files f,
       table( apex_data_parser.parse(
                  p_content => f.blob_content,
                  p_add_headers_row => 'Y',
                  p_max_rows => 102,
                  p_skip_rows => 2,
                  p_file_name => f.filename ) ) p
WHERE f.name = :P1_FILE;

 

P1_FILE is the item used to upload the file. Then, we must create the hidden items to store the column names. Usually, I like to create a new region to hold all these items and have those organized; in this example, I will create the items like this: P1_COL001_H, P1_COL002_H, P1_COL003_H, …, P1_COL199_H. This could be a tedious task, but you can use the export and import functionality in your favor, create just the first item, this will work as a template, then import just the page, and you can manually create the rest of the items using the first one as a template with a query that creates the other 198, then export the page again, and you should have all your items created. 

  

Now that we have all the items created to store the column names, we need to assign values to them. This will dictate whether the column should be shown or hidden. If the item is empty, the column will be hidden; otherwise, it will be displayed. I will use apex_util.set_session_state to dynamically set the items as needed with the values of the first row of the CSV file uploaded like this: 

 

for r in (
    select 'P1_'||col_name||'_H' as item_name, headers as header
    from (
        select p.*
        from apex_application_temp_files f,
        table( apex_data_parser.parse(
            p_content => f.blob_content,
            p_add_headers_row => 'Y',
            p_max_rows => 1,
            p_file_name => f.filename ) ) p
        where f.name = :P1_FILE
    )
    unpivot (headers for col_name IN (col001, col002, ... , col199))
)
loop
    apex_util.set_session_state(r.item_name, r.header);
end loop;

 

Finally, we will use the items to set the header name and condition the column to whether it has to be hidden or not. In the heading, we will reference the item like this: &P1_COL001_H. In the server-side condition, we will set the type to ‘Item is not null’ and select the column item ‘P1_COL001_H.

 

 

 

Every dynamic column created must repeat this. You can use the same trick with the import and export used for the items; in this case, be careful with the IDs of the IG and the columns. 

  

Now, you have a completely dynamic grid or report to preview any CSV uploaded; this is just an example of what can be done with a fully dynamic report or grid. You have the basic knowledge now, so have fun playing with it and exploring all the possibilities. 

 

Conclusion

You’ve now mastered essential APEX customization techniques for dynamic and user-friendly grids and reports. Combining these with the formatting tips from previous posts lets you create better-looking, highly flexible, and functional applications. I hope this series has expanded your APEX toolkit—happy coding, and may your grids be ever dynamic!

All posts
About Author
Julio Flores

Julio Flores is a seasoned Full Stack Developer with over 12 years of experience and currently serves as a Software Engineer at Viscosity. He specializes in building solutions from the ground up using a wide range of technologies and platforms. An expert in Oracle APEX and PLSQL, Julio has delivered numerous internal and webinar presentations on these topics. Prior to his role at Viscosity, he also shared his expertise in Android development. Julio's commitment to continuous learning and his passion for sharing knowledge have made him a valuable asset in the developer community.

You might also like
Unlock Passwordless Authentication in Oracle APEX with Passkeys
Unlock Passwordless Authentication in Oracle APEX with Passkeys
November 04, 2024
Best Practices and Tips for APEX Development: Part II -Elevate Your Interactive Grid with Customization
Best Practices and Tips for APEX Development: Part II -Elevate Your Interactive Grid with Customization
November 04, 2024
SUBMIT YOUR COMMENT
logo for footer

Viscosity's core expertise includes:

Data Transformation, Emerging Technology, High Availability & Scalability Solutions, Cloud Migrations, Performance Tuning, Data Integrations, Machine Learning, APEX Development, and Custom Application Development.


Solutions

Resources

Partnerships

Careers

Clients

 

Contact
Email: sales@viscosityna.com

Telephone:
(469) 444-1380

Address:
3016 Communications Pkwy Suite 200, Plano, TX 75093

Copyright 2025. All Rights Reserved by Viscosity North America.