Uploading Files to OCI Object Storage from Autonomous

August 23, 2021

I have been doing a little hobby project and I wanted to be able to store files in OCI's Object Storage, by an upload from an APEX application. In the past I have used DBaaS which didn't include DBMS_CLOUD so I was able to leverage the web credentials feature of APEX. But it's been a while, so time for a fresh exploration from the context of Autonomous DB.

Part 1: API Exploration

As I already mentioned, there is a package DBMS_CLOUD which has the capability to perform the uploads to object storage from the database. So, the first thing we need to do is check if its available in our app schema - you may need to connect to the admin account and grant execute on it to the given schema.

Once that's all verified, we need to register our credentials. There are two likely pathways you would take here (there is a third too, but I won't be investigating that).

Username and Password
Signing keys authentication

I will be using method two. If you want to do the same, you can figure out what to substitute in the procedure call by installing the OCI command line client and running `oci setup config` and observing what's in the file `$HOME/.oci/config`. I would suggest to create a new user with limited privileges for the credential you create.

note: this call doesn't validate the inputs - you will only get a runtime error if you entered data incorrectly or you don't have access

begin
   dbms_cloud.create_credential(
      credential_name => 'credential1',
      user_ocid => '[INSERT USER OCID]',
      tenancy_ocid => '[INSERT TENANCE OCID]',
      private_key => '[INSERT PRIVATE KEY (REMOVE LINE BREAKS)]',
      fingerprint => '[INSERT FINGERPRINT]'
   );
end;
/

Great - that completed successfully. Next, if you look at the API documentation, you will find a procedure `put_object` that takes in a BLOB datatype. That looks like it will suit perfectly. I will upload a file into SQL developer to do a quick test.

declare
   l_fls fls%rowtype;
begin
   select * into l_fls
   from fls
   where id = 1;
   
   dbms_cloud.put_object(
      credential_name => 'credential1',
      object_uri => 'https://objectstorage.[REGION].oraclecloud.com/n/[NAMESPACE]/b/[BUCKET_NAME]/o/testfile.png',
      contents => l_fls.file_contents
   );
end;
/

The object_uri parameter will be different depending on your personal environments. Check these docs for more info about the format. Another way is just to upload a file in the OCI Console and view the details of the object to see what the path will look like, and adjust accordingly.

OK, so I executed the above code and the file was uploaded successfully. Sweet. Moving on.

Step 2: Hooking this into APEX

We aren't storing our files inside Oracle Database tables, however we will still want a reference to the metadata for those files - so first we need an intermediary table to store those references. One thing to keep in mind is that you can't have two separate files in Object Storage with the same name, so we need to create some unique identifier. There are many options for this, but for the purpose of this post, I will use sys_guid().

create table oci_file_upload (
  oci_file_upload_id number generated by default on null as identity primary key, 
  original_file_name varchar2(256),
  oci_file_name varchar2(256) unique,
  mime_type varchar2(50)
);

And then with that, I created a page item (file browse), a submit button, and the following submit process

declare
  l_file_rec oci_file_upload%rowtype;
begin

  for i in (
    select 
      fl.filename,
      fl.mime_type,
      fl.blob_content
    from 
      apex_string.split(:P1_FILES, ':') upl
      join apex_application_temp_files fl on (fl.name = upl.column_value)
  )
  loop
    l_file_rec.original_file_name := i.filename;
    l_file_rec.mime_type := i.mime_type;
    l_file_rec.oci_file_name := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') || regexp_substr(i.filename, '\.[^\.]*$');

    dbms_cloud.put_object(
      credential_name => 'credential1',
      object_uri => 'https://objectstorage.[REGION].oraclecloud.com/n/[NAMESPACE]/b/e[BUCKET_NAME]/o/'||l_file_rec.oci_file_name,
      contents => i.blob_content
    );

   insert into oci_file_upload values l_file_rec;
  end loop;

end;

A quick verification the files are going into Oracle Cloud. Then you can build a report on this table, doing the reverse - using the `get_object` function instead so you can allow you users to retrieve their files. I won't cover this here since I think there are plenty of examples elsewhere about allowing users to download files.

Worth pointing out, if there is something with your request URL, you will get an access error looking like this (I injected a typo into my namespace):

Final Thoughts

So, essentially, the architecture is that you upload the file to your database server, but then push that file into object storage - essentially two network transfers of the file. In my experience the throughput internally from Object Storage to the DB server is blazing fast, so I wouldn't be too concerned.

One thing I would like to mention - in a previous project where I was using the raw REST APIs - there is more functionality that isn't exposed in this DBMS_CLOUD API. And more specifically, I like the idea to attach additional metadata into the file object in the storage bucket. From the docs, this does not appear a possibility - but on the plus side, it's super easy to get up and running and the functionality is built-in from the get-go on autonomous!

Credits

Opening splash photo by Tatiana Zhukova on Unsplash

Trent's Tech Tips