Quick document generation tools for Oracle APEX using APEX Office Print

When working with Oracle APEX, report generation is usually an inevitable part of the territory, and with reporting comes the need for streamlined workflows. One advantage of APEX Office Print (AOP) is not just the flexibility it offers in custom document generation but also the powerful tooling that reduces complexity for developers while enhancing the user experience. In this blog, I'll go through a few pieces of functionality AOP provides that could help make your report generation more efficient.

Manual Data Batching

This functionality can significantly enhance performance in scenarios where multiple documents are generated from the same template, for example, during invoice generation.
If you have a data source query that can return multiple sets of data just by adjusting the WHERE clause, for instance, changing:

WHERE d.customer_id = 1

 to

WHERE d.customer_id IN (1, 2, 3)

AOP makes it easy to batch these separate reports into a single ZIP file. You can enable this by including the following PL/SQL initialisation code when generating multiple reports:

aop_api_pkg.g_output_filename := 'batched_files.zip';

If you wanted to do some post-processing on the zip file, you could name the files by a unique identifier so you can process files separately using functionality like the below AOP action settings and procedure code:

CREATE OR REPLACE PROCEDURE batch_processing (
    p_output_blob      IN BLOB,
    p_output_filename  IN VARCHAR2,
    p_output_mime_type IN VARCHAR2
) IS
    l_zip     BLOB;
    l_files   apex_zip.t_files;
    l_file    BLOB;
    l_proj_id eba_demo_files.project_id%TYPE;
BEGIN

  -- get the list of files
    l_files := apex_zip.get_files(p_zipped_blob => p_output_blob);

  -- loop through files
    FOR i IN 1..l_files.count LOOP

    --get file content
        l_file := apex_zip.get_file_content(p_zipped_blob => p_output_blob, p_file_name => l_files(i));
    --get ID from file name
        l_proj_id := substr(l_files(i), 1, instr(l_files(i), '.pdf') - 1);

    --insert file into table
        INSERT INTO eba_demo_files (
            file_blob,
            filename,
            file_mimetype,
            project_id
        ) VALUES (
            l_file,
            l_proj_id || '- testfile',
            'application/pdf',
            l_proj_id
        );

    END LOOP;

END batch_processing;
/

More information on this tool can be found using this AOP doc link.

Combining multiple files into one document

This functionality is beneficial when you need to combine multiple files for a specific user into a single document—for example, to simplify file management or streamline printing. AOP makes this process straightforward: simply provide an SQL query to retrieve the necessary files, and AOP handles the merging. Please note that this feature supports output in PDF format only. While you can use a template if desired, for this file-merging example, no template was required.

This can be done using the Append/Prepend/Compare Files tool provided by AOP using aop_api_pkg.g_append_files_sql, for example, for selecting all files associated with a particular project and merging them into one file can be done using code like the below.

aop_api_pkg.g_prepend_files_sql :=  q'[SELECT
                       filename,
                       file_mimetype mime_type,
                       file_blob
                       FROM
                       eba_demo_files
                       WHERE
                       project_id = 72445677789753836857218156025252300291
                       ORDER BY
                       1 DESC]';

aop_api_pkg.g_output_filename := 'project_files';

Dynamic action settings:

AOP was then able to produce a document by merging the file types below, including a hefty document of over 100 pages. 

More information and examples can be found using this link to the AOP page concerning this feature.

Generate a document quickly from page regions

Say, for instance, you have a dashboard or form page and want to quickly generate a report, this can be done easily using the AOP Regions report data type.

Since many region types support this functionality, in this blog, I'll focus on interactive report regions and a custom static region.

A useful feature is the ability to generate an Excel file that consolidates data from multiple interactive reports onto a single sheet. The screenshot below shows the Excel template sheet I'm using:

The two interactive reports below are assigned a static ID—ir1 and ir2

Shown below is the generated Excel sheet, which includes data from all pages of the interactive reports:

For the static region and its contents, I am using a simple template option like the one shown below.

Set the static ID of the region you want to add that corresponds to the template document's entry.

The first image below is from my APEX application, and the second is a screenshot of the corresponding region in the generated document.

As mentioned, this AOP functionality also supports many other region types, including interactive grids and classic reports. Full details can be found via this link.

Quick Note on Document Generation Performance

While effective use of the tool, combined with query efficiency and well-implemented workflows, plays a key role in report generation, there's also a more technical side to optimising AOP itself. The AOP documentation includes a configuration section that guides how to get the most out of your setup.

For more demanding use cases, such as batching many invoices, you can schedule these processes to run during off-peak hours using the AOP API or in a sessions background; this is easy to achieve in APEX using the Run in Background functionality introduced in version 23.1.

Both APEX and AOP offer valuable logging functionality, which I've used in the past to pinpoint where most of a request's time is spent and identify any quick wins.

Conclusion

As we've seen throughout this blog, APEX Office Print (AOP) offers a powerful and flexible set of features that can greatly enhance report generation within Oracle APEX, simplifying tasks that would otherwise require complex development. For more advanced use cases or to explore additional region types and configuration options, refer to the official AOP documentation linked throughout this blog. With the right approach, AOP can help you take your APEX reporting to the next level.

For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.

Contact us today, and one of our expert developers will be in touch.