Upload Multiple Files to Oracle Object Storage from an Oracle Apex Application
How to: Deploy a Low-Code Apex App to upload Multiple Files to OCI Object Storage
Oracle Apex is a low-code platform which has amazing capability to build cloud apps, with limited knowledge of programming. If you have been a PL/SQL programmer then you can leverage your existing skills to build cloud apps with ease. In this article we will build an app to upload multiple files to OCI Object Storage
With APEX available on OCI with the Autonomous Database our customers are building some amazing apps on OCI. A colleague of mine reached out to me asking about the functionality to upload files directly to Oracle Object Storage using an Apex App.
There is already an excellent blog article written to-do this for a single file. However, for the use-case of our customer, they wanted a way to upload multiple files to Object Storage for Data science purpose.
So if you want to upload multiple files to Oracle Object Storage then follow this blog post and we will help you create an app in a few easy steps.
Business Use-cases:
- Simplified User Experience:
- Users may find it more convenient to upload files directly to object storage without the need to provide credentials or tokens every time they upload a file.
2. Reduced Security Risks:
- Storing and managing authentication credentials or tokens within an application may pose security risks.
3. Batch Processing and Automation:
- If your APEX application involves batch processing or automated file uploads, eliminating the need for manual authentication can simplify scripting and automation
4. Public-Facing Applications:
- In cases where the APEX application is public-facing and does not require user authentication, allowing direct file uploads to object storage without credentials may be more practical.
Setup:
This app was created on Oracle Apex 23c on Autonomous Database on OCI, but this should work on any platform where you have Apex 23c installed.
Step 1. Create an Oracle Object Storage bucket in your OCI Tenancy
If you uploaded objects to this bucket the object storage URL would be something like
https://objectstorage.ap-sydney-1.oraclecloud.com/n/apaccpt04/b/apexupload/o/Step 2. Create API Key for your OCI Account which has privileges to read write data to your bucket
Login to OCI Console, click on the top right user icon
Add API Key
Generate Key Pair and Download the Private key file
We will use the same private key and public fingerprint to create the web credentials for OCI in your Apex workspace in next step.
Step 3. Login to your Apex workspace and Create web credentials
Go-to Workspace Utilities > Web Credentials
Click on “Create” and add a new web credential for the API key created in Step 2.
OCI User ID —It is the OCID for your user
OCI Private Key —Paste the private key which was created in Step 2. make sure to copy the full text including the Start and End lines
OCI Tenancy ID —It is your tenancy’s OCID
OCI Public Key Finger print — It is the fingerprint in Step 2.
Valid for URL’s — It is the regional URL for the Oracle object storage.
The static identified for this web credential is called ‘oci_api_key’ which we will use in the App later
Step 4. Create an Apex Application
Create a blank page
Step 5. Create Region and Page Item for the New Page
Create Region on the Page
Create Page Item
Add the name of the page item as FILE_UPLOAD and select type as ‘File Upload’
Scroll down to Storage and enable option ‘Allow Multiple Files’
Create a Button on the same region
Step 6. Create a Process for the Page
Go to the Process tab, click on processing
Right click processing and add a process
Paste PL/SQL Code and change the below 2 parameters as per your environment
p_credential_static_id => 'oci_api_key'
l_request_url := 'https://objectstorage.ap-sydney-1.oraclecloud.com/n/apaccpt04/b/apexupload/o/' || apex_util.url_encode(l_file.FILENAME);Main PL/SQL Code block
declare
l_file_names apex_t_varchar2;
l_file apex_application_temp_files%rowtype;
l_request_url varchar(32000);
l_content_length number;
l_response clob;
upload_failed_exception exception;
l_request_object blob;
l_request_filename varchar2(500);
begin
-- Create or truncate a collection to store file information
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'DOCUMENT');
-- Split the file names from the colon-separated string
l_file_names := apex_string.split(p_str => :FILE_UPLOAD, p_sep => ':');
-- Process each file
for i in 1 .. l_file_names.count loop
-- Fetch file information from APEX temporary table
select *
into l_file
from apex_application_temp_files
where name = l_file_names(i);
-- Add file information to the collection
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'DOCUMENT',
p_c001 => l_file.FILENAME,
p_c002 => l_file.MIME_TYPE,
p_blob001 => l_file.BLOB_CONTENT,
p_n001 => l_file.ID,
p_d001 => sysdate
);
-- Construct the URL for each file
l_request_url := 'https://objectstorage.ap-sydney-1.oraclecloud.com/n/apaccpt04/b/apexupload/o/' || apex_util.url_encode(l_file.FILENAME);
-- Make REST request for each file
l_response := apex_web_service.make_rest_request(
p_url => l_request_url,
p_http_method => 'PUT',
p_body_blob => l_file.BLOB_CONTENT,
p_credential_static_id => 'oci_api_key'
);
-- Optionally, handle the response or raise an exception if needed
-- If (condition based on the response) then
-- raise upload_failed_exception;
-- end if;
end loop;
end;Scroll down to ‘Server-side condition’ and select ‘When button pressed’ to ‘Upload’
Scroll down to ‘Success Message’ and add a success and error message
Save and Run the Application
Step 7. Upload Multiple Files and Test
Select the files or drop it in the box and click on Upload
If files were uploaded successfully, you will see a green color banner pop-up on the top-right corner of your screen.
Check the files in the OCI bucket to confirm. You should see the files in there
The OCI Multiple Files Upload Sample App is also available to download directly from this Github Repo : https://github.com/shadabshaukat/Oracle-Apex-Sample-Apps
Download the file OCI_Upload_App.zip and import into your Apex workspace.
