JMJ CLOUD
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us

Our Blog

 Using Oracle Storage Cloud Service for APEX BLOBS

2/1/2017

 

Introduction

Picture
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

Picture
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).

Picture
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
M
2/2/2017 02:18:30 am

Thanks for sharing,
Is Oracle Cloud Storage faster than Amazon ?
Does it support custom domain like Amazon ?
Does it have ready code to upload files directly from a mobile App like Amazon?

Jonathan Dixon
2/3/2017 07:18:47 am

Great Questions all:
Is Oracle Cloud Storage faster than Amazon ?
> I have not noticed any difference but have not done any specific performance testing.
Does it support custom domain like Amazon ?
> Not that I have seen but you should be able to use GoDaddy or sum such service to forward requests from your domain/sub-domain.
Does it have ready code to upload files directly from a mobile App like Amazon?
> Not that I have seen.

Steve
2/2/2017 03:40:34 pm

Very nice article with some good tips on utilizing cheaper cloud storage.

LC
2/3/2017 03:07:48 am

Absolutely awesome blog post one of the most informative I have read in a while.

Will help me greatly with an upcoming project.

Frederik
2/27/2017 08:21:52 am

Hi,

Superb blog post!

Can you please give some extra info about the step:

Get the top level (PEM) certificate from the following URL:

https://storageconsole.us2.oraclecloud.com/

Where exactly do I get the PEM certificate?

Thanks a lot.

Jon
2/27/2017 08:02:19 pm

Hi,

I usually do the following:
- Goto the URL in Firefox
- Click the Green Lock Icon to left of URL
- Click the Right Arrow >
- Click More Information
- Click View certificate
- Click Details
- Select the top level cert in the tree 'Symantec Class 3...'
- Click Export


Comments are closed.

    RSS Feed

    Popular Posts

    - APEX Dog Food
    - Cloud ERP & APEX Mashup
    - Modernizing EBS with APEX
    - Easy APEX_WEB_SERVICE
    - Running APEX in RDS
    - ORDS What & Why?

    Categories

    All
    APEX
    AWS
    Fusion Cloud ERP
    INTEGRATION
    MS GRAPH
    OCI
    ORDS
    PaaS
    RAD
    REST
    SOAP

    Archives

    October 2021
    February 2021
    January 2021
    October 2020
    September 2020
    June 2020
    May 2020
    April 2020
    February 2020
    January 2020
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016

Company

About
Contact
Blog
  • Home
  • Projects
  • Blog
  • About Us
  • Contact Us