Uploading Files to OCI Object Storage from Autonomous


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).

  1. Username and Password
  2. 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 

Popular posts from this blog

Installing Oracle Instant Client on Ubuntu

Now that Oracle has enabled us to download instant client without any click through for accepting the license , I wanted to revisit a seamless install of the instant client on a new set up. Ubuntu has the documentation about installing the instant client here: https://help.ubuntu.com/community/Oracle%20Instant%20Client. First - because Oracle provides their releases in RPM archive format (or a tarball), in order to have an installer you need to create a DEB archive. There is a package in the archives, alien, which aids this process. This gives the start of the script: #!/bin/bash # Install dependencies sudo apt install alien The 3 packages the Ubuntu documentation tells us to retrieve are: - devel - basic (I opt for basiclite instead) - sqlplus So, over at the downloads page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html we can grab the link. # Download files. Example specific to 19.3 # Some links were not correct on the do...
Read more

Report row buttons firing a dynamic action

Image
A lot of the time on reports of data, we'll define a link column, and specify an image which is the beloved pencil icon. Which is good and all, but looking in the Sample Database Application (packaged application), they have taken a different approach to style links - a button that stretches the width of it's containing cell. So, the UI ends up looking like so: So, is this done? Well first, you define a new column for the placeholder. Suppose I want my link column to be Report, I define an extra column in my query: , 'Report' report_link Then I need to go to my new column's attributes, and change the column type to a link. And, in the link attributes, set the text to what you want displayed. Here, I'm just going to display the value of the new column and point to my desired page. So I set the target page, then also the Link text as #REPORT_LINK#. At this point the report will just show link text, as we expect. So, then to replicate t...
Read more

Customising APEX built-in validation messages

Image
I was working on an application and when trying to save some data, I was getting these error messages despite the fact I had no validations on my page. And what I wanted to know, how do I change these message contents? APEX does some default checking based on entered data. A prime example of this is a date field. On a brand new application, I created a region and added a page item with the type as "Date Picker". If I enter text into the date input field that is not a date conforming to the date format, I will get the following error: "Date Field does not match format DS."   First of all, DS? What is that? This particular application was created by uploading a spreadsheet, and I did not set that. In fact, my spreadsheet had no date data to begin with. In saying that, a quick search in the globalization attributes, and somehow this application has the date format set to "DS". So first, let us change that - I like to use DD-MON-YYYY. So that fixes the error ...
Read more