OCI Object Storage: APEX Upload Guide

Uploading documents to OCI Object Storage can be quite challenging when using PL/SQL due to its complexity and the need for precise coding. However, with the right approach, you can simplify this process significantly. In this guide, I will provide a detailed explanation of the steps required to upload documents efficiently using Oracle Cloud Infrastructure (OCI) and APEX. By following these steps, you can achieve document uploads with minimal code. I assume you’ve a oracle database and APEX up and running.

Generate API key

First, create an API key in the Identity & Security domain in OCI by navigating to My Profile. In the Tokens and Keys tab, you can manage your API keys. To create a new key, click on Add API Key. A new window will open, offering the option to generate or upload an API key.

Choose Generate API Key Pair and click on the Download Private Key button. After downloading, click Add to proceed to the final window.

The final window displays the parameters you need in APEX. Copy these and store them in a file or notepad for later use.

Create bucket in object storage

To store files in the OCI object storage you need a bucket. This bucket can be created by navigating to ‘Object Storage & Archive Storage‘ and clicking ‘Create bucket‘. This opens a window with multiple options, for the most simple we leave the default storage tier on Standard and the other options unchecked.

The Standard storage tier is "hot" storage used for data that you need to access quickly, immediately, and frequently.
The Archive storage tier is "cold" storage used for data seldom or rarely access, but that must be retained and preserved for long periods of time (oracle documentation)

The bucket is created and we need the namespace for the endpoint in APEX.

When writing to the object storage you always need to authenticate the user. Now the OCI supplies pre-authenticated requests (PAR), with these endpoints the authentication happens before the actual processing. The authentication credentials are included in the request url and the authentication parameters doesn’t have to be set anymore. On the same page you can create a PAR and permit object reads and writes. This PAR will be used when writing to the bucket from APEX.

Create web credentials

To authenticate some requests, we need to create a web credential. In the APEX builder, go to Workspace Utilities and then Web Credentials. Here, we can create a credential. Use the copied data from the created API key to fill in the required fields and add the private key as well.

Create REST Data source

To display all the items in the bucket, we create a REST data source to base our report on. Navigate to the shared components, then go to REST Data Sources and create a REST data source. Choose the data source type Oracle Cloud Infrastructure (OCI), give the data source a descriptive name and provide the endpoint URL. The URL endpoint is constructed as follows: https://objectstorage.{region}.oraclecloud/com/n/{storage namespace}/b/{bucket name}/o/

The next step involves creating a remote server and disconnecting the service from the URL path. Provide authentication by selecting the web credentials created in the previous step. After selecting the authentication select Advanced to customize the request.

We add a URL query string to the request with the name fields and the value name,size,timeCreated,md5, then select the is Static option. Now, we can explore the data source and verify if the REST data source is set up correctly.

Create upload procedure

Create a blank page in your application. On that page we’re going to add a upload item/function and a report based on the REST data source. The example page in my application is page 7.

First, let's set up the upload feature. Create a static content region with two page items and a button. The first item should be of type File Upload, and base the storage on the table APEX_APPLICATION_TEMP_FILES. The second item will store the document ID from the object storage and will be hidden. The button will only submit the page, with request UPLOAD.

After submit we put a process to upload the document to the object storage. Here we’ll use the PAR, so we do not have to authenticate the application or user. The PAR is concatenated with the filename and send using apex_web_service.make_rest_request.

declare
   l_url         varchar2(1000);
   l_length      number;
   l_response    clob;
   failed_upload exception;
   l_header_name varchar2(1000);
   l_version_id  varchar2(1000);
begin
    for rec_files in (
        select *
        from apex_application_temp_files
        where name = :P7_FILE
    ) loop
        l_url := :G_BUCKET_PAR || rec_files.filename;
        apex_web_service.g_request_headers(1).name := 'Content-Type';
        apex_web_service.g_request_headers(1).value := rec_files.mime_type;

        l_response := apex_web_service.make_rest_request(
            p_url                  => l_url
        ,   p_http_method          => 'PUT'
        ,   p_body_blob            => rec_files.blob_content
        );

        for i in 1..apex_web_service.g_headers.count loop
            if apex_web_service.g_headers(i).name = 'version-id' then
                l_version_id := apex_web_service.g_headers(i).value;
                exit;
            end if;
        end loop;

        if apex_web_service.g_status_code != 200 then
            raise failed_upload;
        end if;
   end loop;

   :P7_DOC_ID := l_version_id;
end;

The OCI stores the documentID in the result header, there isn’t a function in Oracle to catch this. So we loop over the headers until we’ve found the version-id and store this in the P7_DOC_ID page item. Now the document should be stored in the object storage.

List documents on page

To list the documents in the application we’re going to create a classic report based on the REST data source from earlier. In the source you can specify the data source and the columns will be synced automatically.

In conclusion, uploading documents to OCI Object Storage using APEX can be streamlined by following the outlined steps. By generating an API key, creating a storage bucket, setting up web credentials, and establishing a REST data source, you can efficiently manage document uploads with minimal coding. The use of pre-authenticated requests simplifies authentication, making the process more secure and user-friendly. With these techniques, you can handle document storage effectively.