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!