Oracle Cloud Infrastructure (OCI) Object Storage is a highly scalable, reliable, and secure solution to store unstructured data. In this article, Let’s explore how to store and retrieve files from an OCI bucket using PL/SQL in an Oracle APEX application.
Prerequisites
Oracle APEX Application with REST API enabled
OCI Account with Object Storage enabled
Bucket created in OCI
Authentication credentials (Access Key and Secret Key)
RESTful access enabled for Object Storage
Step 1: Create Web Credentials in Oracle APEX
Web credentials are used to authenticate and authorize RESTful web services. To access OCI Object Storage, we need to create web credentials using OCI’s Auth Token.
Steps:
Go to APEX > SQL Workshop > RESTful Services > Web Credentials
Click Create
Credential Name: OCI_OBJECT_STORAGE
Username: tenancy_namespace/username
Password: OCI Auth Token
Save it
Step 2: Find Required OCI Details
You can find the following required details from OCI Console:
ItemWhere to FindNamespaceObject Storage > Namespace (top bar)Bucket NameObject Storage > BucketsRegionTop-right corner of OCI ConsoleUser OCIDIdentity > UsersTenancy OCIDIdentity > TenancyCompartment OCIDIdentity > Compartment
Step 3: PL/SQL Code Examples
DECLARE
l_blob BLOB;
l_clob CLOB;
l_file_name VARCHAR2(255) := ‘test_upload.txt’;
l_bucket_name VARCHAR2(100) := ‘your_bucket’;
l_namespace VARCHAR2(100) := ‘your_namespace’;
l_url VARCHAR2(500);
l_response CLOB;
BEGIN
— Get the file from APEX collection or page item (example BLOB)
SELECT blob_content
INTO l_blob
FROM apex_application_temp_files
WHERE name = l_file_name;
l_url := ‘https://objectstorage.ap-mumbai-1.oraclecloud.com/n/’
|| l_namespace || ‘/b/’
|| l_bucket_name || ‘/o/’
|| apex_util.url_encode(l_file_name);
apex_web_service.g_request_headers(1).name := ‘Content-Type’;
apex_web_service.g_request_headers(1).value := ‘application/octet-stream’;
— Upload using PUT method
l_response := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => ‘PUT’,
p_body_blob => l_blob,
p_credential_static_id => ‘OCI_OBJECT_STORAGE’ — Your Web Credential
);
dbms_output.put_line(‘Upload Response: ‘ || l_response);
END;
Download a File from OCI Bucket
DECLARE
l_bucket_name VARCHAR2(100) := ‘your_bucket’;
l_namespace VARCHAR2(100) := ‘your_namespace’;
l_file_name VARCHAR2(255) := ‘test_upload.txt’;
l_url VARCHAR2(500);
l_response BLOB;
BEGIN
l_url := ‘https://objectstorage.ap-mumbai-1.oraclecloud.com/n/’
|| l_namespace || ‘/b/’
|| l_bucket_name || ‘/o/’
|| apex_util.url_encode(l_file_name);
— Set Accept header if needed
apex_web_service.g_request_headers(1).name := ‘Accept’;
apex_web_service.g_request_headers(1).value := ‘application/octet-stream’;
— Download
l_response := apex_web_service.make_rest_request_b(
p_url => l_url,
p_http_method => ‘GET’,
p_credential_static_id => ‘OCI_OBJECT_STORAGE’
);
— You can now display or download this BLOB in APEX
— For example: store in a table or use as download link
INSERT INTO downloaded_files (file_name, file_blob)
VALUES (l_file_name, l_response);
COMMIT;
END;
Best Practices
Always encode the file name in the URL using apex_util.url_encode.
Use OCI’s recommended regional endpoint (like objectstorage.ap-mumbai-1.oraclecloud.com).
Secure your Web Credentials – don’t expose Auth Token in code.
Use DBMS_CLOUD or ORDS proxy for enhanced security if required.
The post Storing and Accessing Files in OCI Bucket Using PL/SQL for Oracle APEX appeared first on Ontoor blogs.
