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!

More Advanced File Uploads in Oracle APEX

This post is part of the Getting Started with Plug-ins Pro APEX plug-ins series.

In many Oracle Application Express projects, there's a requirement that people can upload files. Typically the end-users want the ability to drag-and-drop images, PDFs, or other file types in their application. 

For example, if you use APEX Office Print, you might have added in your application the ability for end-users to upload their own templates which typically are docx, xlsx, pptx, html and text files. 

Those files are then stored in the database in a BLOB column or they might be stored on a file server or cloud storage.

In order to add this capability in your app, APEX provides a File Browse item.

There are a few settings for this item type; where do you want to store those files (in a BLOB column or in a TEMP table), if you want to allow multiple file uploads at once, and which types of file you want to accept.

But be careful with the accepted File Types feature. You can define image/* if you want your browser to only allow selecting of images. BUT specifying the file type doesn't prevent users from dragging-and-dropping other file types! So you can't rely on this feature to keep other file types out of your database. 

As this is a native HTML File Browse item, you can customize it with CSS (and HTML and JavaScript). For example, you can make it look like what APEX provides in the APEX Builder itself:

In most of the projects I'm involved in, the end-users want more than the standard HTML File Browse that Oracle APEX provides out-of-the-box. 

The most requested features are:

• Show which files were selected when adding multiple files
• A visual indicator of the progress of uploading
• Save files directly to the cloud (Oracle Cloud Object Storage, Amazon S3) instead of the database
• Limit the file types that will be accepted
• Only allow files with a size less than x MB
• Specific to upload of images: resize and compress images and add watermarks automatically
• Rename files

There are some excellent open-source JavaScript libraries that bring these features, one of the most known is Dropzone.js. Five years ago, Daniel Hochleitner even created an APEX Plug-in on top of it (with the last update 2 years ago). As this plug-in was really well written, we decided to use it as the starting point for our File & Image Uploader plug-in. You can see our plug-in as a supported version of Dropzone for Oracle APEX, with some extra features.

To use this plug-in, you first have to download and install it. I've covered that in my blog posts Plug-ins Pro: Getting started: Sign-up and install your first Oracle APEX Plug-in. So if you didn't import the File & Image Uploader Pro plug-in yet, read that post first.

I'll cover two use cases where we used our File & Image Uploader plug-in.

The first use case is for our RentMaster Platform software which assists in the renting of student homes. We have a form with the information of the contract, and a region which allows people to upload multiple files (up to 3, with a max file size of 5MB) that are linked to this contract. This information is stored in the CONTRACTS table and another CONTRACT_FILES table which has an FK to the CONTRACTS table. Here's a simplified version of the use case in action:

In order to create the above, we add a region with our plug-in on the left and a normal classic report on the right. We also have a Dynamic Action that fires when the files are uploaded so that the report on the right is refreshed.

The attributes of our plug-in region look like this. As storage type we use Custom Table:

The only thing we have to do is define the table in which we want to store the files and the relation (FK) with the parent (ID).

The plug-in exposes many dynamic action events, so you know exactly in which state the upload is in. We specified when Dropzone File Upload was successful:

Pretty simple and low code :)

In the second use case, our customer does auctions. With every auction, multiple images are included of the goods that are for sale. As there are so many images, all those images are resized and watermarked and put outside the database, on a file server and cloud storage. In this example, I will show how to do the image manipulation and save the files directly in the Object Storage of the Oracle Cloud.

Note: if you are interested in how to set up the Object Storage in the Oracle Cloud, read my series on the Always FREE Oracle Cloud.

In the next animated gif, you see again a simplified version in action. On the left the APEX application with the File & Image Uploader plug-in, on the right, the Oracle Cloud - Object Storage:

The attributes of our plug-in region look like this. As storage type we use this time Web Service:

Furthermore, in the attributes, we defined that we should only accept images with max size 3MB and only 10 files.

This time we also defined transformation parameters, a watermark and we rename the files on the fly.

In the United Codes File & Image Uploader Pro Sample Application, you will find an Attribute Builder, which makes it very easy to define what your transformations and watermark need to be, so you can just copy and paste in the attributes:

So we defined a Web Service... this web service can be anything. In my example, I created a web service in ORDS. In this web service, I make a call to the Object Storage REST API of the Oracle Cloud. The plug-in will pass some parameters to the web service: file, filename, mimetype, sessionid and appid:

When you look at the above, you might think... hmm, Dimitri is not doing any authentication to his Object Storage... that is correct. To simplify my example I setup Pre-Authenticated Requests for my bucket in the Object Storage. This provides me with a secure URL people can use to put files in my bucket.

I believe that most people will do proper authentication, which means you first do an extra call to get the authentication token. You may want to read JMJ Cloud's blog post if you want an example of how to do that from APEX. Adrian Png also did another blog post on how to deal with Object Storage from APEX.

Now back to the example... this is what the result looks like; the large image was scaled down, the quality was set to 80%, a watermark was added and the file was renamed - all on the fly and automatically!

So to recap this example: you add our region plug-in, you specify the actions you want to happen (transform, watermark, rename), define a web service which does the call to your cloud of choice and that's it, now whenever somebody uploads files in your Oracle APEX app, it will go straight to the cloud and all manipulations are done in the background!

There are many more use cases you can come up with. This plug-in is also part of our APEX Media Extension (AME). Using the Plug-ins Pro File & Image Uploader plug-in is great when you want things to happen on the client-side. When you already have images in your database, and you want to manipulate those images, you definitely want to check out AME, as it provides a PL/SQL API to do all that, and more (e.g. read meta-data of photos!). We built AME as a replacement for Oracle Intermedia (see blog post and here), we are even featured in the official Oracle documentation as a replacement.

Ok back, to this plug-in... just like any other Plug-ins Pro plug-in, there's extensive documentation and a great sample app that showcases the different features.

You can try the plug-in yourself for 1 month for free in your own APEX applications, so you can see what it can do for you. I also want to highlight that through the end of September 2020, there's a special running. You can find all the details on the Plug-ins Pro website!

Power to the end-user! Create Interactive JET Charts in Oracle APEX

This post is part of the Getting Started with Plug-ins Pro APEX plug-ins series.

Charts and visualizations become very important when talking about data. Businesses are using these visualizations to make an appealing story. You will find many tips and tricks on the internet when you search for Data Storytelling. One of the most important tips is to use the right chart for your data. 

So as a developer, will you figure out what type of chart to include in your Oracle APEX app? 
Or do you include a complete dashboard with every possible way to look at the data?

The issue is that the perfect chart depends on the story the business wants to tell!

Let's look at an example of that is pertinent to today: COVID data.

The table below shows the number of positive cases in the last 4 months for two different countries and a total.

Most likely, people would like to look at this data where the months go from left to right, so let's pivot the data:

How do we visualize this data?

If I add a Chart region in APEX, providing the data source, adding sorting and a Legend, it looks like this: 

 

We can now customize the chart further, but without knowing what the business wants to talk about, it's hard, no? We can use different colors, move the legend to the bottom, change the bars to be stacked, use a different chart type, etc..

When you look at the history of Oracle APEX, one of the most significant and breathtaking features has been Interactive Reports. As a developer, we specify just the SQL query, but the business user is able to look at the data in different ways.

With United Codes, we wanted to provide the developer and business user the same functionality as the Interactive Report but with charts. So I'm happy to introduce you to one of the most astonishing Plug-ins Pro APEX plug-ins: Interactive JET Charts Pro. 

Let's look at what this plug-in can bring to your end-users:

Did you see your end-users can save the customized charts, just like with Interactive Reports? They can add as many charts as they want, so they can quickly change to different views and even share those charts with colleagues!  

Mindblowing? Well, it was for me when I saw how far we got with this 😀

Before I show you how to add a customize button to your own charts, I want to continue the data story...

All of the charts I created in the animated gif are on the same data, but they all support a different story.

The line chart shows whether lines are going up, going down, or holding steady. You can clearly see that country 2 numbers decline and country 1’s take an upswing.

The clustered column chart directly compares the two columns. This is perfect when I want to focus on the difference between both countries: the orange column vs the green column. I changed the colors of the charts as they better represent the country and the difference is shown more clearly.

The stacked column chart focuses on part-to-whole patterns, how the gold segment and the green segment add up to a total. If I wanted to tell the story about the combined numbers of the countries I would pick the stacked bar chart.

So which one to pick, it all depends on the message you want to get across. The Interactive JET Chart APEX Plug-in is ideal if you need to brainstorm on several possible charts, to finally find the perfect chart to support your message. And the cool thing is that as a developer you don't have to make decisions about which type will be most suitable, you give the power to the end-user!

Ok, now that we have a bit more background as to why we built this plug-in, let me walk you through step-by-step how to get started in your own app.

The first step is to download and install the plug-in. I covered that in my blog post Plug-ins Pro: Getting started: Sign-up and install your first Oracle APEX Plug-in. So if you didn't import the Interactive JET Chart Pro plug-in or one of the other awesome Plug-ins Pro plug-ins yet, read that post first.

One point of attention, in the last step, when you install the plug-in it will prompt the Component Settings and you have to define which Procedure you want to use to save the charts. This plug-in will save the end-user settings in a table. 

I would recommend installing the Sample App that comes with the plug-in. This will install everything you need.

The procedure is called UC_JET_CHART_API.SAVEREPORT. You can look at that package and can customize it to your needs. As you have full control over the procedures to save, fetch, and delete charts, you can hook up authorization schemes or any rules you like.

In your application, go to a page where you have a JET Chart. Add a Button, right-click on it and add a Dynamic Action that fires on Click of the button. As Action, you specify United Codes Interactive JET Charts [Plug-in] and you select in Affected Elements the JET Chart Region.

That's it!

In the animated gif, I only did a few customizations, but there are so many more things in this plug-in! In the next example I hide and show a series, so you can focus on a single data set and I change a series chart type and color.

Another important feature is that you can download the JET chart you customized to a PNG file.

This way it's super easy to include the chart in a presentation or document.

Finally, just like any other Plug-ins Pro plug-in, there's extensive documentation and a great sample app that showcases the different features. I would strongly encourage you to look at the Live Demo in the sample app. The plug-in exposes many events, something I didn't even touch on in this blog post!

You can try the plug-in yourself for 1 month for free in your own APEX applications, so you can see what it can do for you. I also want to highlight that through the end of September 2020, there's a special running. You can find all the details on the Plug-ins Pro website!

I'm a big fan of charts and visualizations and I believe your end-users will love this plug-in! 

In 2008 I did a chart presentation at Kscope and I joked that using charts in your Oracle APEX apps is the shortest way to get a raise (as every manager loves pretty pictures). Our goal is to make you even more successful by providing you extra tools, if this plug-in also gives you a raise, it makes us full of joy. We wish for your success (and raises) 😀

Adding a download (blob) link and context menu to your Interactive Grid in Oracle APEX

This post is part of the Getting Started with Plug-ins Pro APEX plug-ins.

In this blog post, I want to highlight another Interactive Grid plug-in we've built: the United Codes Interactive Grid Download File Plug-in.

When you buy any of the United Codes products, when you go to your Account on the website, you can click on View Invoices and have the ability to download them. Those invoices were created with APEX Office Print (AOP) and stored in a BLOB column of a table.

Behind the scenes, we use a Classic Report with a Download BLOB column.

You have the same feature with an Interactive Report, you can go to any column, and make it a Download BLOB column as in the screenshot below. You tell from which table you want the file to be downloaded and give some extra info like mime type etc.

Jackie redid our entire backend system and used many more Interactive Grids (IG). When she tried to include a Download BLOB link, to her surprise there was no such option in an IG. And that is how our IG Download File Plug-in came to live!

Jackie, Bartosz, and I did some brainstorming on what such a plug-in would look like and it became a bit more than just a Download Link 😉

Although this plug-in is probably one of the smaller plug-ins of Plug-ins Pro, we love it and use it a lot in our own applications. Let me first show it in action!

As you can see, it's not just a bleeding fast download link, but you can customize what happens when you click. You can show a context menu on right-click or on a normal click and have full control over it. You can preview files and so much more!

Let's move on integrating this awesome download link into our Interactive Grid. 

The first step is to download and install the plug-in. I covered that in my blog posts Plug-ins Pro: Getting started: Sign-up and install your first Oracle APEX Plug-in. So if you didn't import the IG Download File Pro plug-in yet, read that post first.

Once it's installed it's pretty simple to use the plug-in. Go to your Interactive Grid and right-click on the Columns and add a new column called "DOWNLOAD" from type Link. Give it a CSS Class of "download", specify the target of the link as "javascript:///" and finally give it a link text. You could just give it plain text "Download" or you could specify for example an icon by using:

<i class="fa fa-download"></i>

The above step created a Download link column in our Interactive Grid. Now we will hook up the UC IG Download File Dynamic Action Plug-in to this column. Create a Dynamic Action, Event: Click, Selection: jQuery Selector with .download. As Action, we specify the United Codes IG Download File [Plug-In] and enter the requested details of the BLOB. That's it!

Here a quick screen-recording of me doing all those steps in a few seconds:

You also find the all steps completely documented in the Usage Guide > Implementation Guide.

Now, let's look a bit closer to the settings you can enter:

You specify the (ID) column in the Interactive Grid and the details of the table where the BLOB is found, like the name of the BLOB column, the mime type, and file name columns.

There are also some cool settings. You can keep a normal Download link, or you can extend it so the default behavior is not a download but a menu showing different options. In our own back-end, for example, we quickly wanted to see the preview of a PDF invoice. You just check the checkbox that you want the Context Menu and that is it. This Context Menu by default is enabled on right-click and has some predefined settings like Preview and Download, but you can customize it however you want and even make it the default action.

Events

This plug-in wouldn't be a Pro plug-in if it wouldn't expose events. Events allow you to interfere with the download process at any time. Here are the events you can use in your When of the Dynamic Action:

A typical use case of an event is showing a spinner while the download is transferring data. You find an example in the sample app that comes with the plug-in. Here's what it looks like:

And more ...

As we want our plug-ins to be used over the entire world, the plug-in supports translations and exposes many more customizations. Here's an example of a custom entry opening a modal window and showing more info of the file.

And of course, just like any other Plug-ins Pro plug-in, there's extensive documentation and a great sample app that showcases the different features.

You can try the plug-in yourself for 1 month for free in your own APEX applications, so you can see what it can do for you. I also want to highlight that through the end of September 2020, there's a special running. You can find all the details on the Plug-ins Pro website!