Store and Access Files in OCI Bucket Using DBMS_CLOUD in Oracle APEX
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
Upload a file
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;
Delete a file
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 CLOB;
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);
— Delete the object
l_response := apex_web_service.make_rest_request(
p_url => l_url,
p_http_method => ‘DELETE’,
p_credential_static_id => ‘OCI_OBJECT_STORAGE’
);
dbms_output.put_line(‘Delete Response: ‘ || l_response);
END;
OCI Status codes
Status CodeMeaningDescription200 OK SuccessFile downloaded or deleted successfully201 Created CreatedFile uploaded successfully204 No Content DeletedFile deleted, no content in response400 Bad Request Client ErrorInvalid URL, parameters, or headers401 Unauthorized Auth ErrorWeb credential not authorized or expired token403 Forbidden Permission DeniedMissing permission for bucket access404 Not Found Not FoundFile or bucket not found409 Conflict ConflictDuplicate upload or conflict in file name500 Internal Server Error Server ErrorSomething went wrong on OCI side503 Service Unavailable OCI DownOCI service is temporarily unavailable
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.
