Using Oracle Storage Cloud Service for APEX BLOBS

Introduction

Let's face it, Binary Large Object (or BLOB) columns in the Oracle database are convenient. You can easily create, fetch and delete files of all sizes using PL/SQL and APEX. This ease of use does come with a few downsides, however, namely:
​

• They increase the size of your database considerably. This makes backing up and restoring your database a more time consuming process.
• It is costlier; the fast disk your database is running on is more expensive than Cloud Object Storage.
• APEX must deal with fetching these files across the same connection as the rest of your APEX page thus increasing the load on the database and ORDS.

The answer is to use abundantly available (and cheap) cloud based object storage to manage files used in your APEX Applications. Until recently, I have primarily used Amazon's S3 for storing files. On my latest project, however, I have been working with Oracle's Storage Cloud Service (OSCS). This post describes a general approach for storing files in the cloud and a specific sample APEX application that utilizes OSCS to store files for an APEX application.

---

General Approach for Cloud Based Storage in APEX

This section describes a general approach for implementing a cloud based storage solution for APEX. This approach could be used for any cloud storage service including AWS S3, Google Drive, Drop Box and of course Oracle's Storage Cloud Service.

• Create a 'File Details' table to maintain a local store of file reference information:
  • File ID (Generated by a Sequence)
  • File Related Object Name (e.g. Customer)
  • File Related Object ID (e.g. the ID of the Customer Record)
  • Visible File Name
  • File Mime Type
  • File Size
  • Storage File Name
  • Storage REST End Point URL
  • Status (EXISTS, DELETED)
• Create a new Container/Bucket in your Cloud Storage service to store files used for your APEX Application
• When the user uploads a file in APEX:
  • Store file temporarily in APEX_APPLICATION_TEMP_FILES
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call a REST API to Upload those Files to Cloud Storage
  • Save a record for each file stored in the 'File Details' table mentioned above
  • Make sure the file name in the Storage Service 'Storage File Name' is unique by prefixing the filename with a unique sequence. Users will see 'Visible File Name'.
  • Update the related entity table (e.g. Customer Table) with the File ID
• When the user wants to view available files:
  • Query file information from the local File Details table
  • Avoid doing this from the Cloud Storage Service unless you must, as it will be much slower than pulling it straight from your local table
• When user wants to download a file:
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the REST Service to retrieve file from Cloud Storage into a local BLOB or collection
  • Download the file for the user
• When user wants to delete a file:
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the REST Service to delete a file
  • Mark file as deleted in the local file details table
• Other considerations:
  • Consider limiting the file size at time of upload. Don't take this too far, however, as this can get annoying for your end users who know storage is cheap
  • Consider compressing the file before uploading it to reduce file sizes
  • Consider storing non-proprietary, frequently used logos and image files in Cloud Storage and have a publicly available URL. You can reference these files directly via the URL and there will be no round trip to the database. This will act like a Content Delivery Network of sorts. Of course, another option is to store these kinds of files on the Application Server that ORDS is running on.

Getting Prepared

Before you can start calling the OSCS REST services, you will need to perform some preparatory steps:

Storage Cloud

• Make sure you have an Oracle Cloud account setup with the 'Storage_ReadWriteGroup' role
• Make sure you can login to the Storage Console and upload files from the console
• Create a new container to store your files
• Gather the following information about your OSCS account:
  • REST Service END Point e.g. https://a123456.storage.oraclecloud.com/v1/Storage-a123456
  • Identity Domain Name e.g. a123456
  • Region e.g. US2
  • Oracle Documentation Reference Link

Upload the Cloud Storage SSL Certificate to your Database

• Get the top level (PEM) certificate from the following URL:
  • https://storageconsole.us2.oraclecloud.com/
• Import the certificate into your database wallet
• e.g.
  • orapki wallet create -wallet /home/oracle/mywallet -pwd <<Wallet Password>>
  • orapki wallet add -wallet /home/oracle/mywallet -trusted_cert -cert DownloadedCert.crt -pwd <<Wallet Password>>

Getting the Authentication Token

As with most Cloud Services, Authentication is handled via a token. Every time you call a web service you will need to pass an active token to prove you have access to the resource. To get a token, you need to call the authentication service. Here is some sample code to do this, using the APEX Web Service API. You will notice some global variables, make sure you set these appropriately with your specific values. The token is active for 30 minutes so I suggest creating a PL/SQL function you can call to get the token before calls to other web services.

Suggestion: Use Postman (or a similar tool) to call the service first. Try it out in PL/SQL once you have it working there. Oracle Authentication REST API Documentation Link​

​Sample APEX Application

I built a sample APEX application that allows a user to upload file(s) to and view files in a given container on OSCS. In this section I will provide sample PL/SQL code to allow you to put files in a container and list the files in a container. For the sake of simplicity, this App strays from the approach described in the Approach section of this post (i.e. I am not creating a local File Details table).

End Result

​Adding a File

Here is the PL/SQL for Adding a file. I assuming that you have already used a 'File Browse' item in APEX and uploaded a file or files to the APEX_APPLICATION_TEMP_FILES table. The code below will loop through these files and PUT them into Cloud Storage. Oracle Documentation REST API Link

​Getting a List of Files

The code below will call a REST service to fetch a list of files for a container. Oracle Documentation REST API Link
In our case we are going to ask for the list pf files in JSON, which will look like the following:

Conclusion

While not comprehensive, (I did not cover deleting files, adding file metadata, generating temporary links to files) I hope I have at least got you thinking about alternate ways to store BLOB data. Storage is cheap and you will be doing your DBA and your infrastructure a favor by keeping BLOBS out of your database wherever possible.

Other Reference Information

• A-Team Article on Calling the Storage Cloud REST APIs
• Oracle Storage Cloud Service (OSCS) API Reference Manual