By Adrian Png
November 27, 2019
Oracle Cloud Infrastructure provides a comprehensive suite of REST APIs for accessing and managing cloud resources. For example, administrators can use REST APIs to create and manage autonomous databases and initiate backups.
Oracle Application Express (Oracle APEX) makes working with REST easy. The APEX_WEB_SERVICE
package, available since Oracle APEX 4.0, provides developers with a function for consuming REST APIs. Oracle APEX 5.0 introduced the APEX_JSON
package, allowing easy parsing of JSON responses. Oracle APEX 18.1 introduced the Web Source Module (WSM), which enables developers to access REST services and use the data in APEX components such as reports, interactive reports, and interactive grids.
These features, when used together with Oracle Cloud Infrastructure REST APIs, enable APEX developers to create some very compelling solutions. This article addresses a common web developer question: Where should I store large file attachments?
The most common and straightforward solution is to store each file as a LOB (large object) data type in the database. This is easy and supported by the APEX File Browse page item type. But that approach introduces database management complexities such as storage space management issues and longer data migration, backup, and recovery times.
An alternative in the cloud era is to use object-based storage systems, and most cloud service providers offer this solution. Oracle Cloud Infrastructure offers Oracle Cloud Object Storage Service. With the release of Oracle APEX 19.2, there is now built-in support for Oracle Cloud Infrastructure Web Credentials, which provides a fast and easy way to work with Oracle Cloud Object Storage Service. This article demonstrates how easy it is to authenticate and interact with the Oracle Cloud Object Storage Service REST APIs in APEX.
Access to Oracle Cloud is required to complete the tasks in this article. Request Oracle Cloud Infrastructure’s Free Tier account if you have not already done so. Each account comes with various resources, including two Oracle Autonomous Database instances and access to Oracle Cloud Object Storage Service, which is needed to complete the tasks outlined in this article. Although every Oracle Autonomous Database instance comes with APEX, the APEX-based applications can reside on an on-premise database instance or even on apex.oracle.com. The most important consideration is that the instance is running Oracle APEX 19.2 or later.
OpenSSL software is also required to generate the API keys needed to access the Oracle Cloud Infrastructure REST APIs. For Linux and MacOS users, the application should already be installed. Windows users should install the free Git client that comes with a bash command-line interface and OpenSSL.
In Oracle Cloud Infrastructure Object Storage, a bucket is a container for storing objects. To access a bucket through the Oracle Cloud Infrastructure REST APIs, complete the following identity management tasks, using the Oracle Cloud Infrastructure console and an account that has administrative rights within the tenancy (the root compartment).
Create the PROD compartment:
PROD
Compartment for PROD assets
Create the ProdObjectManagers group:
ProdObjectManagers
Users who can manage objects in compartment PROD
Create the ProdObjectManagementPolicies policy in the root compartment:
ProdObjectManagementPolicies
Policies for users to manage objects in compartment PROD
Keep Policy Current
a. Allow group ProdObjectManagers to
read buckets in compartment PROD
b. Allow group ProdObjectManagers to
manage objects in compartment PROD
Create the prod_object_agent
user, whose API keys will be used for managing storage objects through APEX:
prod_object_agent
User performing object management on behalf of APEX application user
On the user’s details page, set up the user’s API keys to be used with the Oracle Cloud Infrastructure REST APIs. (Complete details for preparing the user can be found here.) The following are the key steps involved:
mkdir -p ~/.oci && \
openssl genrsa -out ~/.oci/poa_oci_api_key.pem 2048
openssl rsa -pubout -in ~/.oci/poa_oci_api_key.pem \
-out ~/.oci/poa_oci_api_key_public.pem
~/.oci/poa_oci_api_key_public.pem
~/.oci/poa_oci_api_key_public.pem
file into the text box, and click Add to complete the process.openssl rsa -pubout -outform DER \
-in ~/.oci/poa_oci_api_key.pem | openssl md5 –c
Finally add the prod_object_agent user to the ProdObjectManagers group.
Create a bucket to contain the files that will be uploaded through the new APEX application.
Log in to the Oracle Cloud Infrastructure console as an administrator.
Click the hamburger icon (≡), select Object Storage, and click Object Storage.
Ensure that the PROD compartment is selected.
Click Create Bucket, and provide the following information in the corresponding fields:
apex_file_storage
, for example.Click Create Bucket to complete the process.
To prepare for the successful creation and discovery of the WSM data profile later, upload a dummy file to the apex_file_storage
bucket now, using the Oracle Cloud Infrastructure console.
The next part of this article involves creating the APEX application for listing, uploading, downloading, and deleting objects from buckets in the Oracle Cloud Infrastructure compartment. The application will include the following pages:
The application will also include the following APEX components:
Begin by creating an application in your APEX workspace:
Log in to the APEX workspace, click the arrow next to the App Builder menu item, and click Create.
Object Storage Management
, and click the Create Application button, near the bottom of the page.
Use APEX web credentials to connect to REST services and securely store the credentials.
Before creating the web credentials in the APEX workspace, obtain the required information from the Oracle Cloud Infrastructure console:
poa_oci_api_key.pem
file, using a text editor.-----BEGIN RSA PRIVATE KEY-----
-----END RSA PRIVATE KEY-----
Next create the web credential:
Click Create.
OCI API Access
OCI_API_ACCESS
Click Create.
In this section, you’ll create two web source modules:
list_buckets
list_objects_in_bucket
As the names imply, the first WSM will list the buckets within a specified compartment, such as PROD
. The second will list the objects found in a named bucket, such as apex_file_storage
.
The following additional information about your tenancy will be required:
Create the list_bucket WSM. Take the following steps:
In the APEX application, go to Shared Components and click Web Sources Modules in the Data Sources section.
Click the Create > button.
Select the From scratch option on the first wizard page, and click Next >.
Provide the required information, and click Next >.
list_buckets
APEX will parse the endpoint and suggest what to enter for Base URL and Service URL Path. Because you will be working within a single tenancy, include the namespace information in Base URL.
Select Authentication Required and the appropriate choice for Credentials. Click Advanced >.
Create a Query String variable parameter. Enter the required information, and click Discover >.
compartmentId
WSM automatically discovers the REST service’s response and creates a data profile. Check that the information is correct, and click Create Web Source to complete the process.
Create the list_objects_in_bucket
WSM. IMPORTANT: Ensure that at least one file has been uploaded to the target bucket with the Oracle Cloud Infrastructure console. If the bucket is empty, the WSM data profile discovery phase will fail!
In the APEX application, go to Shared Components and click Web Sources Modules in the Data Sources section.
Click Create >.
Select the From scratch option on the first wizard page, and click Next >.
Provide the required information, and click Next >.
list_objects_in_bucket
bucket_name
(This parameter was created automatically by APEX.)apex_file_storage
APEX will parse the endpoint and suggest what to enter for Base URL and Service URL Path.
Select Authentication Required and the appropriate choice for Credentials. Click Advanced >.
The first parameter, Parameter Name = bucket_name
, is created automatically by APEX. Leave the defaults. Create another parameter, provide the required information, and click Discover >.
fields
name,size,timeCreated,md5
Check that the information is correct, and click Create Web Source to complete the process.
The Home page (page 1) of the APEX application will contain (1) a Select List page item for selecting the bucket to view and (2) a “classic report” that will list the objects contained in the selected bucket. They are backed by the WSM’s list_buckets
and list_objects_in_bucket
.
Select list with an improved list of values. Prior to Oracle APEX 19.2, a list of values (LOV) supported only two types of datasources: static and dynamic. The latter relies on a SQL query that returns a display and a return value. In Oracle APEX 19.2, the dynamic type is now the default and supports different source types, including WSM (REST). Take advantage of this enhancement to create a simple drop-down list with the list_buckets
WSM as a datasource.
In the APEX application, go to Shared Components and click List of Values in the Other Components section.
Click Create >.
Select the From scratch option on the first wizard page, and click Next >.
Enter a name such as BUCKET_LIST
, select Dynamic, and click Next >.
Under Data Source, click Web Source and select the web source module list_buckets. Click Next.
Select NAME for both Return Column and Display Column, and click Create to complete the process.
Classic report to list bucket contents. To create the classic report, edit page 1 from the Application home page.
Create another region and the following options for a classic report:
Expand the Parameters node in the Classic Report region, and select bucket_name.
Under Value, select the following values:
Create a new modal page (page 2) to enable users to upload files to the selected bucket, triggered by the Upload button on page 1, which also sets the target bucket name.
From the Application home page, click Create Page > to create a blank page. Set Page Mode to Normal. When asked, do not associate the page with a navigation menu entry. When the blank page is created, you will be redirected to Page Designer for page 2.
Create two page items:
P2_BUCKET_NAME
, a Hidden page itemP2_FILE
, a File Browse page item that is configured to use APEX_APPLICATION_TEMP_FILES
as the storage type
Create a region for buttons under Dialog Footer, and add two page buttons:
Right-click the CANCEL button, and click Create Dynamic Action.
uploadFile
page submission process that executes the following PL/SQL code:
declare
l_request_url varchar2(32767);
l_content_length number;
l_response clob;
upload_failed_exception exception;
begin
for file in (
select * from apex_application_temp_files
where name = :P2_FILE
) loop
l_request_url := :G_BASE_URL || 'b/' || :P2_BUCKET_NAME
|| '/o/' || apex_util.url_encode(file.filename);
apex_web_service.g_request_headers(1).name :=
'Content-Type';
apex_web_service.g_request_headers(1).value :=
file.mime_type;
l_response := apex_web_service.make_rest_request(
p_url => l_request_url
, p_http_method => 'PUT'
, p_body_blob => file.blob_content
, p_credential_static_id => :G_OCI_WEB_CREDENTIAL
);
if apex_web_service.g_status_code != 200 then
raise upload_failed_exception;
end if;
end loop;
end;
closeDialog
process to close the modal page that returns the user to page 1.The uploadFile
process code refers to two static substitutions; add them to the application with the corresponding values:
G_BASE_URL
: https://objectstorage
.<REGION>.oraclecloud.com/n/NAMESPACE/
G_OCI_WEB_CREDENTIAL
: OCI_API_ACCESS
(the static identifier for the web credential)Now return to edit page 1:
Create a button named UPLOAD in the Bucket List static content region. Set the following attributes:
When the button’s action has been set, click the No Link Defined button in the Target attribute and set the following values:
Create a dynamic action for the UPLOAD button. Set the attributes for the action:
To facilitate file downloads, create page 3 from the Application home page, with the following components:
declare
l_request_url varchar2(32767);
l_content_type varchar2(32767);
l_content_length varchar2(32767);
l_response blob;
download_failed_exception exception;
begin
l_request_url := :G_BASE_URL || 'b/'
|| :P3_BUCKET_NAME || '/o/'
|| apex_util.url_encode(:P3_OBJECT_NAME);
l_response := apex_web_service.make_rest_request_b(
p_url => l_request_url
, p_http_method => 'GET'
, p_credential_static_id => :G_OCI_WEB_CREDENTIAL
);
if apex_web_service.g_status_code != 200 then
raise download_failed_exception;
end if;
for i in 1..apex_web_service.g_headers.count
loop
if apex_web_service.g_headers(i).name =
'Content-Length'
then
l_content_length :=
apex_web_service.g_headers(i).value;
end if;
if apex_web_service.g_headers(i).name =
'Content-Type'
then
l_content_type :=
apex_web_service.g_headers(i).value;
end if;
end loop;
sys.htp.init;
if l_content_type is not null then
sys.owa_util.mime_header(trim(l_content_type), false);
end if;
sys.htp.p('Content-length: ' || l_content_length);
sys.htp.p('Content-Disposition: attachment; filename="'
|| :P3_OBJECT_NAME || '"' );
sys.htp.p('Cache-Control: max-age=3600'); -- if desired
sys.owa_util.http_header_close;
sys.wpg_docload.download_file(l_response);
apex_application.stop_apex_engine;
end;
Back on page 1, add a virtual column to the classic report. Set the type to Link, and reference the target to the download page, setting the values for both P3_BUCKET_NAME
and P3_OBJECT_NAME
.
The object deletion function will be implemented on the home page with a DELETE_OBJECT
Ajax callback.
<button type="button="
class=="delete-object-button t-Button w80p
t-Button--danger t-Button--link="
data-object-name=="#NAME#=">Delete</button>
var oss = {
deleteObject: function(bucketName, objectName, report) {
if(confirm('Are you sure?')) {
var result = apex.server.process('DELETE_OBJECT', {
x01: bucketName,
x02: objectName
});
result.done(function(data) {
apex.message.showPageSuccess(
'Object deleted successfully.');
apex.event.trigger(report, 'apexrefresh');
}).fail(function (jqXHR, textStatus, errorThrown) {
apex.message.alert('Failed to delete object.');
});
}
}
};
button.delete-object-button
class. Ensure that the event scope is dynamic, and then add an action to execute the following JavaScript code with the classic report as the affected element:
oss.deleteObject(
apex.item('P1_BUCKET_NAME').getValue(),
$(this.triggeringElement).data('object-name'),
this.affectedElements
);
DELETE_OBJECT
with the following PL/SQL code:
declare
c_bucket_name apex_application.g_x01%type :=
apex_application.g_x01;
c_object_name apex_application.g_x02%type :=
apex_application.g_x02;
l_request_url varchar2(32767);
l_response clob;
begin
l_request_url := :G_BASE_URL || 'b/' || c_bucket_name
|| '/o/' || apex_util.url_encode(c_object_name);
l_response := apex_web_service.make_rest_request(
p_url => l_request_url
, p_http_method => 'DELETE'
, p_credential_static_id => :G_OCI_WEB_CREDENTIAL
);
if apex_web_service.g_status_code != 200 then
owa_util.status_line(
nstatus => apex_web_service.g_status_code
);
sys.htp.p('{ "message": "Failed" }');
else
sys.htp.p('{ "message": "Success" }');
end if;
end;
AcknowledgementsI am grateful to Christoph Ruepprich and Carsten Czarski for our conversations on Oracle Cloud Infrastructure REST API support in Oracle APEX 19.2. They helped me figure out how powerful this new feature is to an APEX developer. Thank you! |
TRY Oracle Cloud Infrastructure Always Free Tier.
LEARN more about Oracle APEX 19.2.
Illustration by Wes Rowell
Adrian Png is a senior consultant at Insum. He has a passion for emerging technologies and believes that innovation, optimization, and adaptability are required to succeed in the data economy.
Previous Post