Uploading and downloading files is handled well out of the box in APEX. The file upload item type allows you to easily give users the facility to upload files. There is also the option to link upload and download items directly to BLOB columns in the database, to provide simple upload and download functionality without even writing any code.
However, you may run into issues if you allow your users to upload and download large files. With APEX being browser-based, uploads and downloads will process for as long as the action takes, rendering your application unusable and possibly causing the user to suspect the action has failed.
To provide an elegant solution to this problem, we needed to look into developing our own upload and download process. We needed to provide a way for the file to be uploaded and downloaded in chunks whilst providing the user with progress feedback. When investigating existing solutions, I came across the following two methods:
The initial solution is utilizing Oracle database and APEX functionality to provide a solution within the environment we are used to developing in. The second solution takes advantage of APEX being browser based and uses node.js libraries and the Oracle driver to do all the hard work for us.
The image below shows an implementation of the initial, web service based chunked upload solution running in APEX 5. In the example I am uploading a large .zip file, but both solutions allow you to upload any type of file.
To develop this example further, we can use APEX to provide the download controls. A simple classic report will let us see the uploaded files, and this can even be integrated with Oracle text searching now that your files are in the database. To do the download we create a similar web service, but this time split the file on the database, download in chunks and then save the file. This will allow a download progress bar to be implemented, giving the user a visual representation of how far through the download they are.
One further modification we can make to this solution is to alter the code in the upload web service so that each chunk is stored separately. This allows you to implement ‘Resume Upload/download’ functionality if your users are uploading extremely large files.
It provides the same functionality as the web service example but can be further extended to include a chunked download. Using the ‘request’ Node module you can chunk the download and then use data.headers[ ‘content-length’ ] to find the length of each chunk as the file is being downloaded. You can report this back to the user as a progress bar in the same way as the download example (or by using one of many pre-made status bar extensions).
So, if you want to extend the upload and download functionality of APEX, there are many tools and ideas already out there to get you started. I would highly recommend downloading and installing the Node.js implementation from Dan Mcghan’s blog, but if you want to build it entirely within APEX, the code posted by Kris Rice is a great place to start.
Author: Craig Sykes
Job Title: Senior Oracle Development Consultant
Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.