Use OCI object storage from PL/SQL

Images by OpenClipart-Vectors from Pixabay

Got too much data in the database? Offloading it to object storage is one option. Even better, get stuff in to the database and analyse it with SQL.

Accessing OCI Object Storage from PL/SQL

As mentioned in a previous post about OCI Object Storage I wanted to investigate how to access the OCI Object Storage from PL/SQL, in an Autonomous Database¹.

First step, looking into the documentation, it is not trivial where to start. There are good introductions on how to get started with different SDKs for Java, Python, TypeScript/JavaScript, .Net, Go and Ruby, but for the PL/SQL SDK it jumps right into a few examples as if they assume you are already set up with credentials in the database². I did a quick search and found this: Manipulating the Oracle Cloud with PL/SQL which explains easily how to get started. Adrian shows in that article how to list out all the packages that make up the PL/SQL APIs for OCI, and most importantly how to get started with setting up necessary credentials for PL/SQL. This is what I did.

Adding credentials to the database

From my admin user I granted my own lab user execute privilege on the package dbms_cloud:

grant execute on dbms_cloud to lab_user;

I generated a new API key pair, look in previous post on how to get there. dbms_cloud.create_credential has three versions documented, use this one:

DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name IN VARCHAR2,
	user_ocid       IN VARCHAR2,
	tenancy_ocid    IN VARCHAR2,
	private_key     IN VARCHAR2,
	fingerprint     IN VARCHAR2);

The parameters are pretty obvious when you have the configuration that was shown when you generated a new key pair. The value for private_key is everything between -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.

You can find all your credentials in the table user_credentials.

Digging into the API documentation

From here I went on to the API reference . The package here is called DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE. There is a type hierarchy here that took some time to navigate. You can use the function Open Declaration in SQL Developer to see the package/type specification (right-click on the name and select third option from bottom on the menu, if it does not respond make sure the user has been granted execute on the package or type).

As an example look at the function to list buckets . It returns an object of type dbms_cloud_oci_obs_object_storage_list_buckets_response_t (listed under Object Storage Types in the left menu) that contains a response_body of type dbms_cloud_oci_object_storage_bucket_summary_tbl which of course is a table of dbms_cloud_oci_object_storage_bucket_summary_t , listed under Object Storage Common Types. Finally we find what we need (and some more):

namespace
name
compartment_id
created_by
time_created
etag
freeform_tags
defined_tags

In order to access the package and all the referenced type in my code the admin user must grant execute on them:


grant execute on dbms_cloud_oci_obs_object_storage to lab_user;
grant execute on dbms_cloud_oci_obs_object_storage_list_buckets_response_t to lab_user;
grant execute on dbms_cloud_oci_object_storage_bucket_summary_tbl to lab_user;
grant execute on dbms_cloud_oci_obs_object_storage_get_object_response_t to lab_user;
grant execute on  dbms_cloud_oci_obs_object_storage_put_object_response_t to  lab_user;

Listing my buckets and objects

This code lists my buckets:

declare
   l_bucket_list_response dbms_cloud_oci_obs_object_storage_list_buckets_response_t;
   l_response_body dbms_cloud_oci_object_storage_bucket_summary_tbl;
   l_namespace varchar2(20) := 'scrufrandom';
   l_compartment_id varchar2(200) := 'ocid1.tenancy.oc1..aaaaaaaan7morerandomcharactersdglk7ngvqmmr3dk4yo6ewjk2pa';
   l_region varchar2(20) := 'eu-frankfurt-1';
   l_credential varchar2(20) := 'LAB_CREDENTIAL';
begin
    l_bucket_list_response := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.list_buckets (
        namespace_name => l_namespace,
        compartment_id => l_compartment_id,
        region => l_region,
        --endpoint can be used in stead of region
        credential_name => l_credential) ;
    l_response_body := l_bucket_list_response.response_body;
    for i in 1..l_response_body.count loop
        dbms_output.put_line(l_response_body(i).name);
    end loop;
end;
/

Similar code for listing objects in one named bucket:

declare
   l_bucket_list_response dbms_cloud_oci_obs_object_storage_list_buckets_response_t;
   l_response_body dbms_cloud_oci_object_storage_bucket_summary_tbl;
   l_object_list dbms_cloud_oci_obs_object_storage_list_objects_response_t;
   l_namespace varchar2(20) := 'scrufrandom';
   l_compartment_id varchar2(200) := 'ocid1.tenancy.oc1..aaaaaaaan7morerandomcharactersdglk7ngvqmmr3dk4yo6ewjk2pa';
   l_region varchar2(20) := 'eu-frankfurt-1';
   l_credential varchar2(20) := 'LAB_CREDENTIAL';
begin
    l_object_list := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.list_objects (
        namespace_name => l_namespace,
        bucket_name => 'labbucket',
        fields => 'name,size',
        region => l_region,
        credential_name => l_credential);
    for i in 1..l_object_list.response_body.objects.count loop
        dbms_output.put_line(l_object_list.response_body.objects(i).name 
            || ' has ' || l_object_list.response_body.objects(i).l_size || ' bytes.');
    end loop;
end;
/

In this call I added parameter fields in order to get the l_size field, or else only the name would have been returned, see doc .

Reading objects into the database

OK, I found my objects from last time. We can read an object into a blob with the function get_object that returns an object with type dbms_cloud_oci_obs_object_storage_get_object_response_t . The attribute response_body is a blob. This code is really simple. In order to store it in the database and look at it in SQL Developer I’ll create a table first:

create table object_stage(id number generated always as identity, stuff blob);

declare
    l_namespace varchar2(20) := 'scrufrandom';
    l_compartment_id varchar2(200) := 'ocid1.tenancy.oc1..aaaaaaaan7morerandomcharactersdglk7ngvqmmr3dk4yo6ewjk2pa';
    l_region varchar2(20) := 'eu-frankfurt-1';
    l_credential varchar2(20) := 'LAB_CREDENTIAL';
    l_bucket_name varchar2(20) := 'labbucket';
    l_object dbms_cloud_oci_obs_object_storage_get_object_response_t;
begin
    l_object := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.get_object (
        namespace_name =>  l_namespace,
        bucket_name => l_bucket_name,
        object_name => 'cat.png',
        region => l_region,
        credential_name => l_credential);
    insert into object_stage(stuff) values(l_object.response_body);
    commit;
end;
/
select * from object_stage;

In the output, double-click the pencil in the query result window, and in the pop-up window check View As: Image as shown here:

Cat in SQL Developer, view from blob — The cat is back, now in SQL Developer

That was easy! After the initial hassle, actually getting the objects into a blob is almost trivial.

Writing blobs to object storage

I have a simple APEX app mentioned in a blogpost First Journey with SelectAI . I collect images with my mobile and store them as BLOBs in a table called EVENT. This code uses the put_object function to write all the images to an object in the same bucket as earlier. The function returns an object of type dbms_cloud_oci_obs_object_storage_put_object_response_t :

declare 
    l_namespace varchar2(20) := 'scrufrandom';
    l_compartment_id varchar2(200) := 'ocid1.tenancy.oc1..aaaaaaaan7morerandomcharactersdglk7ngvqmmr3dk4yo6ewjk2pa';
    l_region varchar2(20) := 'eu-frankfurt-1';
    l_credential varchar2(20) := 'LAB_CREDENTIAL';
    l_bucket_name varchar2(20) := 'labbucket';
    l_response dbms_cloud_oci_obs_object_storage_put_object_response_t;
    l_keys JSON_KEY_LIST;
begin
    for i in (select replace(name,' ','_') || '.jpeg' file_name, image
        from event
        where image is not null) loop
        l_response := DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.put_object (
            namespace_name => l_namespace,
            bucket_name => l_bucket_name,
            object_name => i.file_name,
            content_length => dbms_lob.getlength(i.image),
            put_object_body => i.image,
            region => l_region,
            credential_name => l_credential);
        if l_response.status_code >= 400 then
            dbms_output.put_line('Put object failed with status code ' 
                || l_response.status_code);
            l_keys := l_response.headers.get_keys;
            for k in 1..l_keys.count loop
                dbms_output.put_line(l_keys(k) || ':' 
                    || l_response.headers.get_string(k));
            end loop;
        end if;
    end loop;
end;
/

It took 35 seconds to write 256 files with an average size of 3.25 MB. The error code did not kick in, so I have not really seen the result of that. (I’m not much of a PL/SQL programmer, I just wrote it to impress a Dutch friend.) The easiest way to confirm that it actually worked is to check the object listing in the cloud console:

I only had to mask one file name.

Conclusion

Interacting with the Object Storage in the Oracle Cloud is quite easy once you get past the initial hassle. I wish the documentation was more developer friendly. A better Getting started section as Oracle has provided for the other SDKs would have helped.

Anyway, now I know I can continue with more stuff in the APEX, PL/SQL, and AI world - I have some plans for these media files later on.

Something exciting to check out in this context is the new Multilingual Engine for JavaScript in Database 23ai.

You can install the required package dbms_cloud in a database on-prem by following this blogpost from Oracle ↩︎
The fact that the code examples for listing compartments is badly formatted does not help either. ↩︎