Introduction

- 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.
General Approach for Cloud Based Storage in APEX
- 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
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:
- 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
Sample APEX Application
Adding a File
Getting a List of Files
In our case we are going to ask for the list pf files in JSON, which will look like the following:
Conclusion
Other Reference Information