Introduction

The Requirement
(Mobilize your Oracle Database with ORDS). Amongst other things, the App allowed shop floor workers to take pictures of images throughout the manufacturing process and post them to Amazon S3 storage. In addition to the iOS App, we built a large APEX application (integrated with Oracle EBS) for front office staff to manage jobs in the plant. The requirement that lead me to this blog post is to provide the ability for the desktop APEX users to post documents and files to S3.
The Challenge
The Solution
1 – Calling the API Gateway Service
In APEX we use APEX_WEB_SERVICE to call a simple REST service end point on API Gateway. A small JSON document is constructed with information about the file, bucket and URL expiration. You can include Tags for the S3 file in your headers when posting the file. This will ensure those tags are included in the Pre-signed URL and attached to the S3 object when you come to POST it.
2 – The API Gateway Web Service
The API Gateway end point is secured using a key which is maintained in API Gateway. There are other alternatives (such as AWS Cognito). Because we are storing the key securely in our Oracle EBS database, using a key is secure enough. As well as securing the service, API Gateway provides additional functionality such as validating the JSON payload. This avoids un-necessary calls to the Lambda when invalid payloads are passed. Other useful features include built in monitoring and throttling to name a few.
The end point for our API Gateway function is a Lambda function. AWS has a tight integration between these two technologies so hooking them up is pretty straight forward.
3 – The Lambda Function
Serverless computing allows you to build and run applications and services without thinking about servers. With serverless computing, your application still runs on servers, but all the server management is done by AWS. At the core of serverless computing is AWS Lambda, which lets you run your code without provisioning or managing servers.
In certain scenarios, serverless technologies such as Lambda offer significant advantages over traditional approaches. It is always available, you only pay for it when you invoke the function and it scales automatically. You can develop Lambda functions in Node, Java, Python, C# and Go. No news on when PL/SQL will be supported :)
We built our function in Node and it has just 30 lines of code. The function imports the AWS S3 tools library, parses the JSON payload passed in by API Gateway and calls the S3 API to get the pre-signed URL. Again, the tight integration between Lambda and S3 comes in handy, the call to the S3 API is extremely simple.
4 – The Response
When the Lambda function is done, it passes the pre-signed URL back to API Gateway within a JSON document. API Gateway then passes the JSON back to our request in PL/SQL.
5 – Posting the File
Now that we have a pre-signed URL we can post our file directly to S3 without the need for keys and hashing. We again use APEX_WEB_SERVICE to post our BLOB to S3.
PL/SQL Code
Getting the Pre-Signed URL (includes adding tags)
Considerations
- Indexing Files:
- Even though S3 does allow for tagging, it is fundamentally object storage and you are responsible for indexing the file in your database. In view of this, whenever you post a file to S3 you should also be adding a record to a table in your local database, so you can find it again later.
- Having said that, you should also tag your files in the event your index fails for some reason.
- Even though S3 does allow for tagging, it is fundamentally object storage and you are responsible for indexing the file in your database. In view of this, whenever you post a file to S3 you should also be adding a record to a table in your local database, so you can find it again later.
- Security:
- Because we have a pre-signed URL, anyone with that URL can update the associated object in S3. In view of this, be sure to limit the expiration time of the URL to just the amount of time it takes to POST the file. (think minutes not hours).
- JavaScript:
- You can also use this approach to post files directly from JavaScript as opposed to using a ‘File Browse’ item and processing the file in PL/SQL. You can use a PL/SQL dynamic action to get the pre-signed URL then post the file directly from JavaScript on your APEX page which avoids a round trip to the database.
- Wallets:
- Amazon has different SSL certificates for API Gateway and S3 which means you will need to load both certificates into your database wallet.
- Lambda Warmup:
- You pay for each execution of your Lambda function. When your code is inert you don’t pay anything for it. In view of this, AWS only spins up a container for your function when it is executed. This can lead to a lag of a couple of seconds the first time you call the function after a period of inactivity. This is not a big deal for this use case but is worth understanding.
Conclusion
Author
Jon Dixon, Co-Founder of JMJ Cloud