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.
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:
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.
- 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
grant execute on dbms_cloud to myapp;
- 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
grant execute on dbms_cloud_oci_obs_object_storage to myapp;
- 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
grant execute on dbms_cloud_oci_obs_object_storage_list_buckets_response_t to myapp;
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
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.
credential_name => 'MY_OCI_CRED'
, user_ocid => 'ocid1.user.oc1..aaaaaaaa**********'
, tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaa**********'
, private_key => 'MIIE**********='
, fingerprint => '**:**:**:**:**:**:**:**:**:**:**:**:**:**:**:**'
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.
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
dbms_output.put_line('bucket: ' || where_are_my_buckets.response_body(i).name);
You should get an output similar to this:
status code: 200
bucket count 2
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.