​I have been using APEX since version 4. I have used it to develop extensions to E-Business Suite, Oracle ERP Cloud and also for Custom Applications. During this whole time, I have come to dread the question that clients almost always ask, “How do I print from APEX”? In this post I will tell you why I think APEX Office Print (AOP) gives APEX developers a chance to respond to that question with their heads held high.

APEX Office Print offers a solution for generating highly formatted documents and reports in Word, Excel, PowerPoint, HTML and Plain Text formats. It comes in an on-premise and cloud version. You send AOP a template and a JSON payload and it returns a formatted document. On the backend, AOP is architected as a web service which means it is completely technology agnostic (one of its key differentiators). It means you can use AOP from any technology that can call a web service (not just APEX). It is called APEX Office Print for a reason though so let’s talk about that. AOP comes with a Plugin for APEX which provides tight integration with a number of APEX components (e.g. Interactive Report, Interactive Grid). Using this integration, downloading an Interactive Report to Excel can be as simple as adding a button to an APEX page and creating a template with a single tag. Read on to see some more in-depth examples that explore many other capabilities of this interesting product.

The obvious product to compare AOP with is Oracle’s BI Publisher. For me, the primary differentiator for AOP is its availability in the Cloud. This makes it accessible from just about anywhere. In fact, I am working at a client now where we are using AOP in the Cloud from APEX on-premise and from APEX running on Exadata Express. There is even talk at the same client of calling AOP from a native iOS App.
 
Other AOP differentiators include:

• Tight integration with APEX.
• JSON data payloads (as opposed to the bulkier XML that BI Publisher uses).
• Powerful and easy to follow templating syntax.
• Comprehensive demo APEX application. This was a great help when I learned AOP.
  
• Reasonably priced (especially when compared to BI Publisher).

I won’t be diving too deep, but I do want to demonstrate some ways we are using AOP for our clients. Hopefully, these examples will illustrate why you should consider AOP as part of your APEX printing strategy.

Inspection Report
One of our clients’ needs to produce documents (in pdf and Word) detailing the inspection of product before it is shipped to the customer. The document shows order details, material condition information and up to a hundred readings taken from a digital quality gauge. The document is printed at the plant and then emailed to the customer. With so much information coming from many different sources, we decided to use the following approach to generate the document:

1. In APEX, the user selects the job to print, checks the output format (pdf or Word) and clicks a custom ‘Print’ button.
2. Behind the ‘Print’ button, we call a custom PL/SQL procedure does the following:
   • Calls a function to generate the JSON payload for the report. This function calls a number of functions and procedures to gather the data necessary for the report.
   • Fetches the appropriate AOP template from a database table for the user’s logged in language.
   • Calls the AOP procedure ‘aop_plsql_pkg’ with the JSON and the template. The ‘aop_plsql_pkg’ gathers the template and JSON and calls the AOP web service which returns a BLOB with the formatted document.
   • Downloads the output to the users’ computer (optionally emails the document to the user).

 
Note: In the above example we have not used the AOP APEX Plugin at all. This illustrates you can use the ‘aop_plsql_pkg’ package to call AOP directly from PL/SQL.

Generic Interactive Report Excel Downloads
The example illustrates AOPs tight integration with APEX via their Dynamic Action Plugin. One of our clients has an APEX application with over 150 pages. Amongst these pages are about 50 interactive reports and interactive grids. If we wanted to develop an ‘.xlsx’ download for all of these reports using BI Publisher, we would have had to:

1. Develop 50 different BIP templates and upload them to the ‘Report Layouts’ section in ‘Shared Components’ in our APEX App.
2. Create a copy of the 50 underlying SQL statements for each of the reports to the ‘Report Queries’ section in ‘Shared Components’ in our APEX App.
3. Add code to each of the 50 pages to call these BI Publisher reports.

The duplication of the report SQL is the exact opposite of ‘DRY’ and represents as significant increase in maintenance and upgrade costs.
 
AOP to the Rescue
Using AOP, we can create a single Excel template with the company logo, a nicely formatted title block and a single token ‘{&interactive}’. This template can be used for all 50 of our interactive report Excel downloads.

​By adding the code below to our APEX pages, we can use this template on any interactive report in our application, significantly cutting down on the number of templates we need to maintain.

​Code from the Init PL/SQL Block:

The above code does the following:​

1. Sets the download filename using a global variable.
2. Sets a global variable with APEX Items so they can be referenced in the AOP template.
3. Sets the formatting for the table headings in the output and even sets alternating row colors.

Sample Output:

Employee Document Receipt Confirmation
My final example shows how you can use AOP in the Cloud to generate highly formatted documents from on-premise or Cloud based applications. In this instance, the customer requested that their employees be able to confirm receipt of key documents (e.g. NDA, Employee Handbook) in the Cloud and also generate a pdf copy of their acceptance for the employee to download. In addition to this, they also wanted to generate a copy of the pdf to attach to the employees HR record in EBS on-premise.
 
APEX running on Exadata Express was used to show the employee the document and to call AOP to generate a pdf of the document acceptance for the employee to download.
One accepted, the on-premise system pulled in the acceptance to the local database, and then used the same AOP template to generate another pdf copy of the document acceptance and attached it to the employees official HR record in EBS.

Architecture

Here are some other potential use cases for AOP:

• Using AOP from a button in APEX to generate a report and email it instead of downloading it.
• Calling the AOP web service from a native iOS App in order to generate a pdf to display to the user on an iPad.
  
• Calling the AOP web service from a customer portal (built in a non-Oracle technology) to generate a pdf copy of an invoice for a customer.

There are a couple of things I would like to see from AOP in the future:

• Ability to store templates in the Cloud e.g. AWS S3 and have AOP go directly to the Cloud to get the template.
  • You can store templates in S3 today and reference them via a URL. The problem is AOP makes a call from your local database to get the template before the AOP web service is called.
  • Ideally, we would pass a template URL and a JSON document to AOP. AOP would then fetch the template directly from S3, without the extra round trip.
• Free development mode credits.
  • Developing a template-based report is an iterative process. You often go through many iterations of changing the template, running the report, changing the template and running the report again. A complex report could take more than 100 iterations. This can quickly eat into the monthly AOP plan quota.
  • I would like to see a development mode that did not count against your quota. I would even be OK if the result was watermarked in development mode.

We have only been using AOP for three months, but we have already used it to build several mission critical documents and reports. It has been reliable, performant and fills most of the printing gaps left by Oracle in the base APEX product. If it continues to develop at the rate it is today, I believe it will become the go to solution for reporting from APEX. At time of writing a free tier was introduce giving you 100 free credits per month. I encourage you to go try it out!

APEX is obviously a database centric product. It is one of the things we love about it because it makes handling data easy. Even so, there are times when we may want to limit the number of times we perform round trips to the server. Having an overly ‘chatty’ App can cause headaches to your server administrator and a poor experience for your users.

In this post I will talk about Web Storage, the apex.storage JavaScript API and how they can help us persist information locally so that we can reference it without performing that extra round trip to the server.

Before I get started, let me be clear. I am not talking about off-line Apps or even Progressive Web Apps (PWA’s). While I would love to be able to achieve pure off-line with APEX, I don’t see it happening in the near future. Vincent Morneau has done a lot of work in this area and I would recommend reading what he has done here. I am talking about improving regular on-line APEX Apps by reducing un-necessary chatter with the server.

With web storage, web applications can store data locally within the user's browser. Before HTML5, application data had to be stored in cookies, which are included in every server request. Web storage is more secure, and large amounts of data can be stored locally (at least 5MB), without affecting website performance.

In a nutshell apex.storage is an APEX provided JavaScript API which allows you to store and retrieve name value pairs in your browser’s web storage. The APEX team have also thought of things like having collections of name value pairs for different APEX Apps (or even pages and regions). You can even differentiate between values stored just for the current session (session storage) and values stored across sessions (local storage).

I could go on, but the 18.1 documentation (About local and session storage) provides a good background to the subject. I have noticed there is no mention of apex.storage in the new style JavaScript documentation. I am assuming this is an omission in the documentation as opposed to any sign the API is not supported.

​The most important warning is that you should not store any sensitive information in web storage, and I mean any. Not only is it easily accessible to the user of the PC but even the most basic CSS attack can access web storage on your user’s device.

​Web storage is subject to your user clearing their cache. This means it cannot be relied on permanently. If a user clears their browser cache, all of the data stored will be lost. In view of this, you shouldn’t treat APEX storage as a database just somewhere you can store your stuff for a while.

Finally, as with anything JavaScript related there are also browser support considerations which you can see here. It does go as far back as IE 8 though, so you should be OK using it.

​Saving User Preferences
In many applications, you need to save convenience data to help users complete repetitive tasks more efficiently. This typically comes in the form of default values. For example, when a user has to enter the same value day after day you can give them the option to save it for next time (and only change it when necessary) by storing the value in local storage. You avoid the need to do a round trip to the server to save the preference value and when you are getting the preference value.
 
Temporary Storage of User Selections
Let’s say your user is selecting from a number of choices in a list. Instead of sending each selection to the server via an Ajax call, you can store their selections in web storage. When they are done selecting, you can send the entire list to the server in one shot.

​In a recent project, users wanted to be able to review and email a report (an APEX Interactive Report) to several colleagues on a daily basis. In order to prevent the user from having to type in the distribution list every day, we saved the last used distribution list locally to web storage. When the user logs in the next day to send the email again, we get the distribution list from web storage and set that distribution list as the default value.
 
The goal is that when a user opens the modal to send an email, the distribution list will be populated with the one they used last:

​In the above call, we are applying a prefix of “MYAPP” and asking APEX to also include the App Id as part of the prefix. Within those prefixes, we could have hundreds of name value pairs for a given APEX App.
 
This is how the stored value looks in Chrome Developer Tools. You can see the key has been prefixed by the APEX API with the prefix I asked for ‘MYAPP’ and the App Id.

​Let’s check the value was set:
console.log(apex.item("P160_EMAIL_ADDRESS").getValue());
jon.dixon@jmjcloud.com, fred@abc.com

​Whilst not exhaustive, I hope this post has sparked some ideas and that you start thinking of web storage as another useful tool in your APEX toolbox.

​Over the past 6 months, APEX has been the foundation for several innovative solutions provided by JMJ Cloud. Our most recent project leads us to the world of Amazon Web Services (AWS) with API Gateway, Lambda (serverless/function as a service) and S3 object storage. In this post, I will show you how we built a cost-effective solution to simplify posting files to S3 from APEX using API Gateway and Lambda.

A few months ago, JMJ Cloud built a native mobile iOS App for a major manufacturing client
(Mobilize your Oracle Database with ORDS). Amongst other things, the App allowed shop floor workers to take pictures of images throughout the manufacturing process and post them to Amazon S3 storage. In addition to the iOS App, we built a large APEX application (integrated with Oracle EBS) for front office staff to manage jobs in the plant. The requirement that lead me to this blog post is to provide the ability for the desktop APEX users to post documents and files to S3.

AWS does not have an SDK for S3 and PL/SQL (no surprises there). This makes managing objects in S3 from APEX and or PL/SQL a major challenge. The number of steps AWS make you go through to call their S3 web services are overwhelming and if you are on R11 of the database (with no dbms_crypto to create a SHA-256 hash) it is almost impossible to achieve. Some folks have tried to solve this problem through open source solutions such as Alexandria (link). The problem is that as Amazon evolves its S3 offering, the open source solutions aren’t always able to keep up. We needed a way to simplify the interface with S3 and use this from multiple clients including APEX.

The solution to simplify the process of interfacing with S3 involves two other AWS products, API Gateway and Lambda. In the diagram below, I am illustrating how we use these technologies to get a pre-signed S3 URL. With a pre-signed URL, you can perform an action on a specific S3 object with just the URL (no other keys or hashing required). We can also use a similar approach to directly post files to S3 but both API Gateway and Lambda have payload size limits. API gateway has a limit of 10mb and Lambda a limit of 6mb. By the time you account for base64 encoding the files, this gives you a max file size of about 4mb.

Let’s step through the diagram to see what is happening:

1 – Calling the API Gateway Service
In APEX we use APEX_WEB_SERVICE to call a simple REST service end point on API Gateway. A small JSON document is constructed with information about the file, bucket and URL expiration. You can include Tags for the S3 file in your headers when posting the file. This will ensure those tags are included in the Pre-signed URL and attached to the S3 object when you come to POST it.

2 – The API Gateway Web Service
The API Gateway end point is secured using a key which is maintained in API Gateway. There are other alternatives (such as  AWS Cognito). Because we are storing the key securely in our Oracle EBS database, using a key is secure enough. As well as securing the service, API Gateway provides additional functionality such as validating the JSON payload. This avoids un-necessary calls to the Lambda when invalid payloads are passed. Other useful features include built in monitoring and throttling to name a few.

The end point for our API Gateway function is a Lambda function. AWS has a tight integration between these two technologies so hooking them up is pretty straight forward.

3 – The Lambda Function
Serverless computing allows you to build and run applications and services without thinking about servers. With serverless computing, your application still runs on servers, but all the server management is done by AWS. At the core of serverless computing is AWS Lambda, which lets you run your code without provisioning or managing servers.

In certain scenarios, serverless technologies such as Lambda offer significant advantages over traditional approaches. It is always available, you only pay for it when you invoke the function and it scales automatically. You can develop Lambda functions in Node, Java, Python, C# and Go. No news on when PL/SQL will be supported :)

We built our function in Node and it has just 30 lines of code. The function imports the AWS S3 tools library, parses the JSON payload passed in by API Gateway and calls the S3 API to get the pre-signed URL. Again, the tight integration between Lambda and S3 comes in handy, the call to the S3 API is extremely simple.

4 – The Response
When the Lambda function is done, it passes the pre-signed URL back to API Gateway within a JSON document. API Gateway then passes the JSON back to our request in PL/SQL.

5 – Posting the File
Now that we have a pre-signed URL we can post our file directly to S3 without the need for keys and hashing. We again use APEX_WEB_SERVICE to post our BLOB to S3. 

The PL/SQL code to call the API Gateway Service to get the pre-signed URL and then post a file to that URL is about 40 lines of code. Add the 30 lines of code for the Lambda function and we are still under 100 lines of code. Not too shabby!
 
Getting the Pre-Signed URL (includes adding tags)

​The case for serverless technologies is building as their capabilities continue to grow. In this post, I have described a relatively simple use case. I hope it has given you some ideas of how this technology can help you to solve similar problems.