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

May 25, 2025Posted inAPEX, OCI

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

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;

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: