Storing and Accessing Files in OCI Bucket Using PL/SQL for Oracle APEX

May 25, 2025Posted inAPEX, OCI

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:

  1. Go to APEX > SQL Workshop > RESTful Services > Web Credentials
  2. Click Create
  3. Credential Name: OCI_OBJECT_STORAGE
  4. Username: tenancy_namespace/username
  5. Password: OCI Auth Token
  6. Save it

Step 2: Find Required OCI Details

You can find the following required details from OCI Console:

ItemWhere to Find
NamespaceObject Storage > Namespace (top bar)
Bucket NameObject Storage > Buckets
RegionTop-right corner of OCI Console
User OCIDIdentity > Users
Tenancy OCIDIdentity > Tenancy
Compartment OCIDIdentity > Compartment

Step 3: PL/SQL Code Examples

Upload a file

Copy Code
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

Copy Code
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

Copy Code
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 CodeMeaningDescription
200 OK✅ SuccessFile downloaded or deleted successfully
201 Created✅ CreatedFile uploaded successfully
204 No Content✅ DeletedFile deleted, no content in response
400 Bad Request❌ Client ErrorInvalid URL, parameters, or headers
401 Unauthorized❌ Auth ErrorWeb credential not authorized or expired token
403 Forbidden❌ Permission DeniedMissing permission for bucket access
404 Not Found❌ Not FoundFile or bucket not found
409 Conflict⚠️ ConflictDuplicate upload or conflict in file name
500 Internal Server Error❌ Server ErrorSomething went wrong on OCI side
503 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.
Tags: