Manipulating the Oracle Cloud with PL/SQL

It was only days ago that I had finalised my slides for my upcoming Oracle Groundbreakers APAC Virtual Tour talk on using Oracle Cloud Infrastructure (OCI) Object Storage for storing large files in Oracle Application Express (APEX). Today, I found out with a lot of excitement, that one of my slides was now factually wrong!

For a while now, Oracle has provided SDKs (Software Development Kits) for a wide range of programming languages, but strangely, that didn't include PL/SQL. Thankfuly, the wait and bewilderment is now finally over! Well, at least for folks who are running their applications on the Oracle Autonomous Database (ADB). And yes, even if you're on an Always Free instance.

Don't believe me? Use your favourite database client and login to your ADB instance using the admin account. Run the query below to get a list of available packages that line up with the APIs listed here.

select object_name
from dba_objects
where object_name like 'DBMS_CLOUD_OCI_%'
  and object_type = 'PACKAGE'
order by object_name;

You should see a long list of 70 packages (at the time of writing), something like this:

OBJECT_NAME
----------------------------------------
DBMS_CLOUD_OCI_AD_AUDIT
DBMS_CLOUD_OCI_AG_DEPLOYMENT
DBMS_CLOUD_OCI_AG_GATEWAY
DBMS_CLOUD_OCI_AG_WORK_REQUESTS
DBMS_CLOUD_OCI_AM_APPLICATION_MIGRATION
DBMS_CLOUD_OCI_AN_ANALYTICS
DBMS_CLOUD_OCI_ASC_AUTO_SCALING
DBMS_CLOUD_OCI_AS_ANNOUNCEMENT
DBMS_CLOUD_OCI_BC_BLOCKCHAIN_PLATFORM
DBMS_CLOUD_OCI_BDS_BDS
DBMS_CLOUD_OCI_BD_BUDGET
...
DBMS_CLOUD_OCI_WS_WAAS

70 rows selected.

Required Database Privileges

To use them with your application schema, e.g. myapp, you will need to do the following tasks. Begin with the admin account.

1. Allow myapp to create the required OCI Cloud Services credentials (OCI credentials). We will discuss what this is in greater detail later. The procedures that you will need are in the package DBMS_CLOUD.

   grant execute on dbms_cloud to myapp;
2. Identify the API and corresponding PL/SQL package that you wish to use. For example, to access the Object Storage, I will require privileges to the package DBMS_CLOUD_OCI_OBS_OBJECT_STORAGE.

   grant execute on dbms_cloud_oci_obs_object_storage to myapp;
3. Check the API reference and identify database types that you might need to use and grant these privileges as well. For example, in my code, I will be declaring a variable of the type dbms_cloud_oci_obs_object_storage_list_buckets_response_t.

   grant execute on dbms_cloud_oci_obs_object_storage_list_buckets_response_t to myapp;

OCI Setup

Setup the Object Storage bucket, user, group and policy using the steps described in my Oracle Magazine article Better File Storage in Oracle Cloud, specifically:

• Identity Setup; and
• Bucket Setup

Be sure to generate the API keypair and associate it with the OCI user that will act on your application's behalf. Also ensure that this user has the necessary policies applied. Finally, please note the following information to setup the required OCI credentials:

• User's ocid
• Tenancy ocid
• Private key
• Fingerprint

Next, connect to the database as the application user/schema. You will need to execute the procedure DBMS_CLOUD.CREATE_CREDENTIAL. There are two overloaded procedures and you must execute the procedure for the OCI Signing Key Credentials.

begin
  dbms_cloud.create_credential(
    credential_name => 'MY_OCI_CRED'
    , user_ocid => 'ocid1.user.oc1..aaaaaaaa**********'
    , tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaa**********'
    , private_key => 'MIIE**********='
    , fingerprint => '**:**:**:**:**:**:**:**:**:**:**:**:**:**:**:**'
  );
end;
/

NOTE

The private key is to be prepared the same way we do when setting up the Web Credentials in APEX for OCI. Remove the header, footer and any newlines in the PEM-formatted contents.

Check that the OCI credentials were created successfully using the following query:

select credential_name, username from user_credentials;

Now run the following sample code to get the list of Object Storage buckets in the compartment defined in the parameter compartment_id. You will need to also specify your tenancy's Object Storage namespace and region.

declare
  where_are_my_buckets dbms_cloud_oci_obs_object_storage_list_buckets_response_t;
begin
  where_are_my_buckets :=
      dbms_cloud_oci_obs_object_storage.list_buckets(
        namespace_name => 'mynamespace'
        , compartment_id => 'ocid1.compartment.oc1..aaaaaaaa**********'
        , region => 'us-ashburn-1'
        , credential_name => 'MY_OCI_CRED'
      );
  dbms_output.put_line('status code: ' || where_are_my_buckets.status_code);
  dbms_output.put_line('bucket count ' || where_are_my_buckets.response_body.count);
  for i in 1..where_are_my_buckets.response_body.count
  loop
    dbms_output.put_line('bucket: ' || where_are_my_buckets.response_body(i).name);
  end loop;
end;
/

You should get an output similar to this:

status code: 200
bucket count 2
bucket: apex_file_storage
bucket: data-import

Closing

I have barely scratched the surface of what we can do with this new PL/SQL SDK for OCI REST APIs, especially for OCI adminsitrators and APEX developers looking to integrate Cloud Native features in their applications. The APEX team did us developers a huge service when the web credentials for OCI was introduced in version 19.2. It greatly simplified how we accessed the OCI REST services. However, not every endpoint worked out-of-the-box and I have found problems accessing some endpoints. We also had to write our own code for constructing the request payload and parsing the responses. This SDK handles and shields us from all the low-level marshalling tasks, and provides a structured approach for retrieving information from the API responses.

The power of the Converged Database is here. One database to rule them all! #ThinkAutonomous