APEX: Understand the Sample OCI Document Generator App
Using the Sample to Build Your Own APEX App
The release note for APEX version 19.1 announced that Oracle REST Data Services (ORDS)-based Printing was Deprecated and it would be removed in future releases.
In the next release (19.2) it was sent packing Oracle REST Data Services (ORDS)-based Printing is Desupported, leaving external remote print servers as the only options to have formatted reports from APEX. The exception for Apache FOP at that time all other options were paid options.
This brings us to the current release, APEX 24.1 and the support for the pre-built function from OCI Functions.
Blog Posts Announcing the Oracle APEX Sample Document Generator App! by Menno Hoogendijk and Seamless PDF Generation with Oracle APEX and OCI Document Generator by Roopesh Thokala
About this Post
As I work with customer attempting to achieve similar results, using the working example of the sample app makes sense.
In my latest engagement it became clear that saying check out the sample app was not enough. So, I will try to describe how this app works if you wish to use the supplied plugin and similar implementation design.
APP Design & Implementation
The Home Page
The Home Page presents a Card region that queries the APEX_APPLICATION_LIST_ENTRIES, to display the sample’s options for document generation.
This region is not truly important but it as a reference to the templates that relate to a document type that will be generated.
Selecting the Generate PDF action button, the user is re-directed back to the Page which uses a computation to check that the Autonomous Database Resource Principal has been created and the user has been granted the DWROLE.
A nice to have.
This action also sets the page request value that is used in several conditional Pre-Rendering Processes that prepares the data and submits the document Generation request.
Data Preparation
Three of the pre-rendering processes are plugin type: OCI Document Generator - Print Document.
The report source query creates the JSON document that is submitted to the document generator function and combined with the template.
The template uses dotted notation references of the JSON document. The query needs to be designed & developed, and tested via SQL tools.
Example
select json_object(
json_object(
'role' value 'USER',
'content' value json_array (
json_object(
'type' value 'TEXT',
'text' value transcripts.BLOCK
)
) RETURNING CLOB
) RETURNING CLOB
) ,
'apiFormat' value 'GENERIC',
'maxTokens' value 600,
'isStream' value false
) RETURNING CLOB
FROM TEXT_TABLE transcripts
Document Templates
OCI Functions: Pre-Built Document Generator Function: generates documents in an Object Storage bucket based on provided JSON data and an Office template document stored in Object Storage.
The function supports both MS Word and MS Excel templates. The sample provide 3 Word document templates.
Templates use TAGS to map JSON data using paths and supports looping constructs. As an added benefit an {%image} tag is used to inject an image into a document. Images can be provided from an OCI Object Storage Bucket, or from a URL. Images must be provided as an object.
//From Object Storage
{
"my_image": {
"source": "OBJECT_STORAGE",
"objectName": "image.png",
"namespace": "object_storage_namespace",
"bucketName": "my_bucket_name",
"mediaType": "image/png"
}
}
//From a URL
{
"my_image": {
"source": "URL",
"url": "https://www.oracle.com/.../.jpg"
}
}
The JSON structure should be included in the JSON data and simply referenced by the element name.
The documentation does a good job to explain how to use the tags and from the various examples, OCI or APEX, it should be self-explanatory.
In the sample all templates are stored as static application files in the templates directory. This is somewhat hardcoded and need to be uploaded by a developer.
APEX OCI Document Generator Plugin
The bulk of the integration work is done by the plugin and it can be copied, exported/imported from App to App.
The plugin uses two sets of custom attributes scopes, the ones set at the Application level and the ones passed in from the page to the component.
The Application scoped settings are configured in the Shared Components - Component Settings area for the plugin.
Configure this based on the OCI details for the OCI Document Generator Function and OCI Bucket.
The Component scoped attributes are set when the plugin’s type is used on a page and your previous exploration already reviewed these settings for the Template, Report Source Query, and Filename. Though only the Report Source Query was called out for the JSON data.
How does it work?
A review of the plugin code reveals that it
Queries the content of the template from the APEX Static Files
Uploads it to the Bucket on OCI
Executes the SQL of the Report Source Query
Creates the OCI Function request body payload with the data and runtime parameters
Gets the function details and invokes it
When successful it downloads the generated document from Object Storage
Building a New App
Including this capability in a new app will require all of the setup and pre-requisites of the sample app and a copy of the plugin.
The developer will be responsible for providing the report source query and a matching template that will be used by the OCI Function.
Follow the sample’s design and implementation of file placement, naming conventions but I suggest to manage a table or bucket that is accessible to the business users, so templates can be managed and updated without a developer.
Template editors will need clear guidelines and understanding of JSON document structure but would remove this basic task from the developer and bottleneck for such requests.
Any requests for data changes would require a change request, implemented by a SQL Developer.
Conclusion
Things to Note
The Templates are stored in the Application Static Files and are uploaded by the plugin. There is no need to upload the templates to Object Storage, so if you plan to use a different design, the plugin would need to be altered.
The plugin performs a get operation for the function and this call retrieves the endpoint URI, this is a neat use of the GET request and I’ll be considering all the places that could benefit from this or if storing base URLs is still that way to go.
The plugin using Autonomous Database only PL/SQL APIs specific for OCI. For non-Autonomous Deployments the plugin would need to be re-written per the Roopesh’s Blog post.
I’ve not tested this myself but it uses DBMS_CLOUD under the covers to create the require Cloud Credential and provides a link to download and install this package.
The example also uses the APEX Instance set Report Server, where the sample can be connected to different deployed OCI Document Generator Functions, which maybe a requirement for billing by departments.
In Summary
Reusing the sample app is pretty straight forward once you’ve understood the design.
It is an option for Oracle APEX customers running on in the Autonomous database deployment.
The code is relatively easy to follow and with some effort could be adapted for non-Autonomous database deployments as well.