My steps to upgrade to Oracle APEX 20.2, ORDS 20.3 and AOP 20.3

In the last month, the three major components of an Oracle APEX environment got new versions:

- on October 21, 2020, Oracle Application Express (APEX) 20.2 

- on October 29, 2020, Oracle REST Data Services (ORDS) 20.3 

- on November 15, 2020, APEX Office Print (AOP) 20.3 

In general, the releases are as follows (based on findings from 2018 onwards):

- APEX has two releases a year, around March/April and one around September/October.

- ORDS brings out one release per quarter, but from time to time they might skip a quarter.

- AOP has three major releases per year and some smaller releases in between.

Do we upgrade our production environment with every new release? 

- APEX: yes, we upgrade typically once the patchset bundle is available, so every 6 months

- ORDS: no, we upgrade ORDS once a year, unless we need a specific feature

- AOP: yes, at least the major releases are followed for the on-premises AOP release. AOP Cloud always has the latest release by default.

When I look at our customers, many don't upgrade that fast. The bigger the corporation the slower the uptake, going from every 4 years to yearly upgrades.

This weekend we upgraded an environment to bring everything to the latest release. Here's a breakdown of my tasks and some tips.

1. Download all the latest software

- Go to the website Oracle Application Express (APEX) 20.2, click the download link, once you log in the download starts.  If you want to be on the latest patch set, then also click the link to download Patch Set Bundle for Oracle APEX 20.2  (32006852). You will need to have a valid support contract with Oracle in order to download the patchset bundle.

- Go to the website Oracle REST Data Services (ORDS) 20.3, click the download link, once you log in the download starts. 

- Go to the website APEX Office Print (AOP) 20.3, login (or signup if you don't have an account yet), go to the downloads section, and download the on-premise version of Linux or Windows.

2. Prepare installs

- Copy all the zip files from step 1 to the server(s). In our case, all of the above software is running on the same server, but many people have separate servers for the DB (where APEX is), ORDS, and AOP.

- Prepare the directories and unzip

- I typically export all Workspaces and APEX apps before doing the upgrade, in the case of APEX 20.1, and store them somewhere. This way I always have a copy of the app in that APEX version. Here's the command to do so:

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -expWorkspace > workspaces.txt

/usr/bin/java oracle.apex.APEXExport -db localhost:1521/APEX_PDB -user xxx -password xxx -instance > applications.txt

3. Install Oracle APEX

Just as with the upgrade to Oracle APEX 20.1 I blogged about previously, I followed the same steps to maximize uptime during the APEX upgrade.

During the first two phases, the Oracle APEX apps were still running:

SQL> @apexins1.sql sysaux sysaux temp /i/

SQL> @apexins2.sql sysaux sysaux temp /i/

The first two phases took about 7 minutes.

After phase 2, I stopped ORDS as specified in the doc, but which is not necessary anymore as ORDS is smart and knows APEX is upgrading and automatically suspends activity by itself. 

(As I wanted to upgrade ORDS too I stopped it - read more in ORDS install about that)

SQL> @apexins3.sql sysaux sysaux temp /i/

It completed in about 3.30 minutes.

At the same time, phase 3 was running, I copied the images folder.

Note: you can also use the CDN by running @reset_image_prefix.sql after phase 3 and specifying: https://static.oracle.com/cdn/apex/20.2.0.00.20/

With the above steps, the APEX 20.2 install completed. Now, I applied the patchset bundle, which took about 1 minute.

SQL> @catpatch

Finally, I ran the following command to allow APEX apps to access web services:

SQL> BEGIN

    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

        host => '*',

        ace => xs$ace_type(privilege_list => xs$name_list('connect'),

                           principal_name => 'APEX_200200',

                           principal_type => xs_acl.ptype_db));

END;

/ 

That was pretty much it to upgrade our Oracle APEX 20.1 to 20.2 release.

4. Install ORDS

Every pluggable database and APEX version has its own ORDS. Whenever I upgrade ORDS, I unzip it in a new directory. I copy the config of the previous ORDS version (the whole ords directory) in the new directory where I unzipped ORDS. In my case, I unzipped ORDS 20.3 in /u01/apex202/ords.

Next to the config, I also copy the start and stop scripts and create a logs directory.

Next, I run the ORDS command to set the config directory in the new ords.war file.

java -jar ords.war configdir /u01/apex202/ords

To upgrade ORDS I run: 

java -jar ords.war 

This will connect to the database, identify the meta-data, upgrade it, and run ORDS.

Once it's complete, I stop ORDS and run it with the start_ords.sh script, so proper logging is done. 

Note, in this environment we run ORDS in standalone mode.

Now, with the install of ORDS, I made a mistake... I thought to be smart, and upgrade ORDS at the same time when APEX was doing the final install step (see in APEX, when I stopped ORDS).

This wasn't so smart to do! It looks like ORDS is looking at the APEX version to generate some repository views. It messed up things, as ORDS saw still APEX 20.1 as the flip of APEX versions was still going on. Anyway, I got an error. So I decided to wait until the APEX upgrade was finished, then I ran the ORDS command again and it completed fine.  All things were validated ok, start ORDS and we were up-and-running in APEX 20.2 and ORDS 20.3.  Tested the apps, all ok... but for one domain, after a few minutes, I saw an error in the ORDS logs:

2020-11-21T11:19:38.182Z INFO        <CaNhATfOdgVPIOr6aSAtzw> GET www.xxxx.com /ords/f?p=XXXX:LOGIN:0 403

ProcedureForbiddenException [statusCode=403, logLevel=INFO, reasons=[Access to the procedure named: f is denied. ]]

I still can't explain why it happened. I solved it by running ORDS validate again and restarting ORDS.

java -jar ords.war validate

My guess is, that the connection pool was messed up by running ORDS while APEX was not finished yet installing. So my recommendation is to always install APEX first and only once that is complete upgrade ORDS (if you want to do it at the same moment), or the other way around, install ORDS first and once complete upgrade APEX.

5. Install AOP

Upgrading AOP is the most simple of the three components, as it doesn't have a repository. Unzip the download in a new directory, copy the config (aop_config.json) of the running AOP to the new folder and activate AOP:

./APEXOfficePrintLinux64 -a

Stop the old version of AOP, start the new version of AOP, and done.

Happy upgrading!

Oracle APEX 20.2: APEX Office Print (AOP) vs native PDF, Excel and HTML

One of the most loved features of APEX Office Print (AOP) is the ability to export an Interactive Report or Grid exactly as you see it on the screen to Excel and PDF. AOP takes into account highlighting, filters, and more as defined by the end-user. This feature has been available since AOP 1.5 (2015) on APEX 5.0 and any higher version of APEX.

With the release of Oracle APEX 20.2, APEX provides this capability out-of-the-box. Because of this, we have received a few questions like: "Is this AOP functionality?", "How does the native APEX functionality compare with AOP?", "What's the roadmap of AOP?" and I thought the best way to answer would be to write about the differences between APEX Office Print and the native printing functionality in APEX 20.2.

Let's go back to the start... AOP's mission has always been to make the printing and exporting of data in Oracle APEX in the format you want  as easy as possible. We really believe that business users should be able to create a template in a tool they know (Word, Excel, Powerpoint) and let AOP merge it with data from the database. From day 1, APEX Office Print has been a template-based reporting engine. In your template, you use tags, which AOP replaces with data. When no custom template is selected AOP will use a default AOP Template. AOP is the most integrated printing solution for Oracle APEX out there and provides some unique features like the ability to print and export different regions by providing a single tag. For example, to print an Interactive Report as you see it on the screen you put {&interactive} in your template, to print a chart you use {$chart}, an image you use {%image}, etc.

The built-in exporting solution of Oracle APEX started from a different angle. The APEX development team wanted a PL/SQL only solution to allow people to export data/regions in different formats. Oracle APEX 20.2 is a big advancement to what was already available previously. For example, before you could download a report to CSV, but now APEX allows you to download to native Excel. Also, for Interactive Reports and Grids, you can choose to get the export as you have it on the screen with breaks, etc. In addition to the built-in download of a region, they now expose APIs (APEX_REGION and APEX_DATA_EXPORT packages) to export programmatically. 

Let's look at the questions people ask: 

Is this AOP functionality?

No, it's not, in the sense that the creation of the file happens completely in PL/SQL in native APEX, whereas AOP reads the APEX meta-data, creates a JSON, and sends it to the AOP Server which creates the file and serves it back to the database. Of course, reading the meta-data is the same and the output for a single report is very similar when you don't define a custom template in AOP. 

Although AOP is super fast in creating files (< 0.5 seconds!), the native APEX functionality will be the fastest solution to export data as it stays within the database. This is why from AOP 20.3 onwards we also now provide the option to select APEX Report, which will use the native APEX functionality behind the scenes.

How does the native APEX functionality compare with AOP? 

The native functionality in Oracle APEX 20.2 focuses on exporting a single region or some flat data (single select statement). This is vastly different from AOP, which focuses on printing and exporting as a whole. AOP is a full templating and printing engine with hundreds of features to create pixel-perfect PDFs and export the data out of your database in the format you want. If you need to create custom letters, bills of lading, documents with images and charts, perform some PDF manipulations like merging, splitting, signing or want to print directly to a printer, for example, AOP is what you need to use. 

When we don't look at general exporting and printing but focus on exporting of APEX regions and data, the native APEX functionality does a great job on basic functionalities. If you want more flexibility and advanced features, AOP is what you want to use. Here are a few examples where AOP shines:

• put your logo on top of the export (PDF/Excel/HTML)
• export multiple reports with some custom titles and text to PDF
• export different Interactive Report, Interactive Grid, Classic Reports, and some custom data to different sheets in Excel
• keep the styling (e.g. defined in HTML expressions) when exporting to PDF and Excel 
• have complete freedom as to how the export looks and define your own template
• support for many different languages
• export the charts of Interactive Reports and Grids
• reference APEX Items in your export
• use a percent graph in your reports
• export a specific saved private or public report
• conditionally hide a specific column when you export
• export Master-Detail(-Detail) Interactive Grids
• export your reports to Word, Open Document Format, or Markdown
• export hierarchical data and different blocks of data  

I think its great people can create some custom exports and prints with built-in tools and use AOP when more customization, flexibility, and features are needed. In fact, the new API functionality that is exposed in APEX 20.2 will make AOP even better, but more about that in my answer to the next question.

What's the roadmap of AOP?

We shipped the first version of AOP in March 2015 and since that day we have constantly invested in the product. We typically have 3 major releases per year and some smaller releases between them.

In fact, every year we have invested more in the product than the year before. 2020 is not any different. On the AOP 20.3 release, we have worked for 6 months with 8 people! It was one of the biggest and most challenging releases we have ever done, but it has made it our best release ever! A huge shout-out to the entire team who pulled it together: Sunil, Recep, Gibresh, Niyam, Kelvin, Inias, Jackie, and ... myself 😀

Two years ago it was announced that AOP will be more integrated into Oracle APEX. In APEX 20.1 and above, you can define APEX Office Print as your Print Server on the Instance Level, which replaced the functionality that was deprecated in ORDS. In APEX 20.2 the foundation of further integration of AOP is made available by providing the awesome APEX_REGION and APEX_DATA_EXPORT packages. 

If you've ever wondered why there's a 'PJSON' format in the APEX_DATA_EXPORT package... it's because we worked with the APEX Development team on this. In the last years, we've spent a lot of time understanding the meta-data of APEX and for every new component and release, we've had to put a lot of effort into supporting and testing it. The new packages provide us with a built-in way to get the region meta-data, so we are set up to support any future component.

The Oracle Database and APEX are in our hearts and we do everything we can to provide you with the best printing and exporting solution out there. Just like the APEX Development team, we are dedicated to making you successful. We also understand you might be using additional technologies to Oracle APEX and PL/SQL, and although, today, AOP can already be used from any technology by doing a REST call, we will begin providing SDKs for other technologies too.

I hope the above addresses some of your questions and I'm happy to answer any other you might ask in the comments section below or by emailing our AOP Support team.

Happy printing and exporting! 😀

Create, Read, Fill and Flatten PDF Forms with APEX Office Print 20.3

In the last three years, we've put a lot of effort into the PDF capabilities of APEX Office Print (AOP).

With AOP 20.3 we are releasing an additional sample app focussed just on PDF generation and manipulation. Here's an overview of the main page, highlighting our many features:

In this blog post, I want to cover PDF Forms. PDF Forms have been around for a long time. My guess is that everybody has had to fill in at least one PDF Form already. An example we as a company have to fill in a lot is the W-8BEN form, another example is Form 941 Federal Tax Return. 

Before I go into how you can fill such a PDF Form automatically, let's first look at how you can create a PDF Form yourself with AOP! It looks like COVID increased the requirement to create such forms directly from the database, at least we received many more requests for such a feature lately.

First, you create a template in Word (docx), and anywhere you want a form item (text box, radio group, checkbox) you specify the tag {?form name}.

Next, for every form item you need to specify how it should look in the data you send with the template.

You can specify the item details in AOP by defining the JSON, a SQL, or PL/SQL statement for example. Here's how the definition looks in a SQL Statement and in JSON:

You define your template and data source in the AOP APEX Plug-in (or PL/SQL API).

That's it, AOP will do the rest and create your beautiful PDF Form automagically! :)

Now let's see what AOP can do with PDF Forms we received? E.g. the W-8BEN Form.

Our dream was to read the PDF Form, identify the fields, and fill it automatically and get a filled-in PDF back... and we nailed it! 😀

Use the AOP plug-in to tell which PDF you want to read, and set the global variable g_identify_form_fields to true. AOP generates a new PDF that identifies every field and shows what the name is.

If you want all the fields in a JSON, use the AOP PL/SQL API and specify FORM_FIELDS  as output type:

You get back a nice JSON with all the different form fields, the type, and the value, in case it's filled in.

To fill the items, you call AOP and specify in "aop_pdf_form_data" the data to fill in the PDF Form.

Finally, in case you filled in the PDF Form and now want to make sure other people can't change the fields anymore, you would flatten the PDF. Again, use the AOP PL/SQL API or APEX Plug-in, set the global variable g_output_lock_form to true and you are done!

In the AOP PDF Sample App you will see that the PDF on the left you can still edit, while on the right, you can't:

It's not going to be any easier than this!

In case you need to generate PDF Forms, or you need to fill in forms or you want to process and read those filled-in PDF Forms, AOP can really help. It was a tough nut to crack, but I'm super proud the team was able to pull it together.

Be on the lookout for APEX Office Print 20.3, it's coming to you very soon!