If you’re working with object storage on OCI at all, you’ll notice that the Oracle 23ai Free container database is missing a crucial component: DBMS_CLOUD.

DBMS_CLOUD is a package provides a layer so that the database can manage files and directories in OCI’s object storage service. Typically found only in ADB environments, DBMS_CLOUD can also be installed on non-ADB instances. Here’s how:

Installing DBMS_CLOUD

There’s a couple of existing resources that will step through installing DBMS_CLOUD:

• Oracle-Base’s DBMS_CLOUD Guide

• Oracle Support Note 2748362.1

You’ll of course need a valid Oracle Support account to see the later one.

Either of these guides should provide the steps required, but for consistency’s sake - and to address some of the nuances of doing this in an Oracle 23ai Database container - I’ll walk through the steps here as well.

Podman Terminal

Since we’re running the database in a container, and we can’t SSH to that container, we will need to use Podman’s terminal. This terminal give us a basic shell where we can run standard commands from. It’s a little feature-starved, but it will work for what we need.

To access Prodman’s terminal:

1. Open up Podman.

2. Select the Containers icon (2nd from the top).

3. Click on the three dots and select Open Terminal.

From here, we’ll kick off the database installation.

Database Installation

First, let’s create a new directory.

1. In the Terminal, enter and run the following command:

mkdir -p /home/oracle/dbc/

Next, we will create a script that when run, will install DBMS_CLOUD.

2. To create the script, run the following command:

vi /home/oracle/dbc/dbms_cloud_install.sql

If you’re not familiar with the vi editor, shame on you. Seriously. If that is the case, there’s tons of other resources that will familiarize you with basic commands. I’ll outline precisely what you need to do here, just in case.

3. Once the previous command is run, hit the I key to place vi into INSERT mode. You should see —- INSERT -— along the bottom of the window.

4. Copy the following script:

@$ORACLE_HOME/rdbms/admin/sqlsessstart.sql

set verify off
-- you must not change the owner of the functionality to avoid future issues
define username='C##CLOUD$SERVICE'

create user &username no authentication account lock;

REM Grant Common User Privileges
grant INHERIT PRIVILEGES on user &username to sys;
grant INHERIT PRIVILEGES on user sys to &username;
grant RESOURCE, UNLIMITED TABLESPACE, SELECT_CATALOG_ROLE to &username;
grant CREATE ANY TABLE, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE,
CREATE ANY CREDENTIAL, CREATE PUBLIC SYNONYM, CREATE PROCEDURE, ALTER SESSION, CREATE JOB to &username;
grant CREATE SESSION, SET CONTAINER to &username;
grant SELECT on SYS.V_$MYSTAT to &username;
grant SELECT on SYS.SERVICE$ to &username;
grant SELECT on SYS.V_$ENCRYPTION_WALLET to &username;
grant read, write on directory DATA_PUMP_DIR to &username;
grant EXECUTE on SYS.DBMS_PRIV_CAPTURE to &username;
grant EXECUTE on SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_CRYPTO to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE ON SYS.DBMS_ISCHED to &username;
grant EXECUTE ON SYS.DBMS_PDB_LIB to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.DBMS_SERVICE to &username;
grant EXECUTE on SYS.DBMS_PDB to &username;
grant EXECUTE on SYS.CONFIGURE_DV to &username;
grant EXECUTE on SYS.DBMS_SYS_ERROR to &username;
grant EXECUTE on SYS.DBMS_CREDENTIAL to &username;
grant EXECUTE on SYS.DBMS_RANDOM to &username;
grant EXECUTE on SYS.DBMS_SYS_SQL to &username;
grant EXECUTE on SYS.DBMS_LOCK to &username;
grant EXECUTE on SYS.DBMS_AQADM to &username;
grant EXECUTE on SYS.DBMS_AQ to &username;
grant EXECUTE on SYS.DBMS_SYSTEM to &username;
grant EXECUTE on SYS.SCHED$_LOG_ON_ERRORS_CLASS to &username;
grant SELECT on SYS.DBA_DATA_FILES to &username;
grant SELECT on SYS.DBA_EXTENTS to &username;
grant SELECT on SYS.DBA_CREDENTIALS to &username;
grant SELECT on SYS.AUDIT_UNIFIED_ENABLED_POLICIES to &username;
grant SELECT on SYS.DBA_ROLES to &username;
grant SELECT on SYS.V_$ENCRYPTION_KEYS to &username;
grant SELECT on SYS.DBA_DIRECTORIES to &username;
grant SELECT on SYS.DBA_USERS to &username;
grant SELECT on SYS.DBA_OBJECTS to &username;
grant SELECT on SYS.V_$PDBS to &username;
grant SELECT on SYS.V_$SESSION to &username;
grant SELECT on SYS.GV_$SESSION to &username;
grant SELECT on SYS.DBA_REGISTRY to &username;
grant SELECT on SYS.DBA_DV_STATUS to &username;

alter session set current_schema=&username;
REM Create the Catalog objects
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_task_views.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_catalog.sql
@$ORACLE_HOME/rdbms/admin/dbms_cloud_types.sql

REM Create the Package Spec
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud_capability.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal.plb
@$ORACLE_HOME/rdbms/admin/dbms_cloud.sql
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int.plb

REM Create the Package Body
@$ORACLE_HOME/rdbms/admin/prvt_cloud_core_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_task_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_capability_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_request_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_internal_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_body.plb
@$ORACLE_HOME/rdbms/admin/prvt_cloud_admin_int_body.plb

-- Create the metadata
@$ORACLE_HOME/rdbms/admin/dbms_cloud_metadata.sql

alter session set current_schema=sys;

@$ORACLE_HOME/rdbms/admin/sqlsessend.sql

5. Select the Podman Terminal window.

6. Paste in the script with CTRL-V (PC) or Command-V (Mac).

7. Hit the Esc key.

8. Type the following: :wq and hit return.

Congrats, you’re a vi expert now!

Next, we’re going to run the script via catcon.pl. This perl script will install DBMS_CLOUD into all PDBs as well as the CDB. We’ll pass our freshly minted script as a parameter and run it as SYS.

9. Copy the following code, changing [SYS Password] to the actual SYS password in the CDB before running.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl \
  -u sys/[SYS Password] \
  --force_pdb_mode 'READ WRITE' \
  -b dbms_cloud_install \
  -d /home/oracle/dbc \
  -l /home/oracle/dbc \
  dbms_cloud_install.sql

This script will run for a few seconds. If successful, you should see something similar to the following:

Take a moment to browse the log file to see if there’s anything amiss.

Even better - simply connect to the CDB and check to see if DBMS_CLOUD is there:

10. From the Podman terminal, type the following: sqlplus / as sysdba

11. Once connected, enter and run the following: desc dbms_cloud

You should see all of the procedures and functions associated with DBMS_CLOUD.

Let’s quickly check our PDB:

12. Enter and run the following command: alter session set container = freepdb1;

13. Enter and run the following: desc dbms_cloud

Once again, you should see all of the procedures and functions associated with DBMS_CLOUD.

Wallet Installation & Configuration

In Oracle Database 23ai, there’s no need to install and configure a wallet, since all of the root CA certificates from the operating system can be shared. See this post by Connor McDonald for details.

Adding ACL Entries

Now that we have DBMS_CLOUD installed, we need to create another entry in the database’s ACL so that it can call outbound web services - such as object storage. I’ve trimmed the steps required to do this to assume that we’re running on the Oracle 23ai free container. The steps found on Tim’s blog and the Oracle Support Note are much more comprehensive, and I recommend that you take a look there if your needs fall outside the scope of the container.

Adding ACL entries needs to be done in the CDB this time, not our PDB, so we need to run the following commands from the Podman Terminal again.

1. Navigate to the Podman Terminal.

2. Start SQL*Plus and connect as SYS by entering the following: sqlplus / as sysdba

3. Enter and run the following:

begin
dbms_network_acl_admin.append_host_ace
  (
  host =>'*',
  lower_port => 443,
  upper_port => 443,
  ace => xs$ace_type
    (
    privilege_list => xs$name_list('http', 'http_proxy'),
    principal_name => upper('C##CLOUD$SERVICE'),
    principal_type => xs_acl.ptype_db)
    );

commit;

end;
/

We can verify that this worked with the following formatting & query. Since we’re stuck with SQL*Plus, we’ll have to throw some good ol’ COL commands to make the data fit.

4. Enter and run the following:

col host for a10
col lower_port for a5
col upper_port for a5
col principal for a20
col principal_type for a20
col privilege for a20
set lin 200

SELECT  
   host
  ,lower_port as lower
  ,upper_port as upper
  ,principal
  ,principal_type
  ,privilege
FROM   
  dba_host_aces
ORDER BY 
  host
 ,ace_order
/

The output should look similar to this:

HOST            LOWER      UPPER PRINCIPAL            PRINCIPAL_TYPE       PRIVILEGE
---------- ---------- ---------- -------------------- -------------------- --------------------
*                                GSMADMIN_INTERNAL    DATABASE             RESOLVE
*                 443        443 C##CLOUD$SERVICE     DATABASE             HTTP_PROXY
*                 443        443 C##CLOUD$SERVICE     DATABASE             HTTP

Creating OCI Components

Remember - what we’ve just spent time creating is the plumbing. It does not give us any specific access into OCI, but rather facilitates the calls when we make them. We still need to present the correct credentials, which need to be mapped to a user, which needs to be mapped to a group, which needs to be mapped to a policy that grants access to the object storage buckets in a specific compartment.

Create a User, Auth Token & Group

1. Login to the OCI console at https://cloud.oracle.com with an administrator account.

2. Using the main menu, navigate to Identity & Security > Domains.

3. Click Default (current domain).

4. Click on the Users tab. You should see a list of users in your domain.

5. Click Create User.

6. Fill out the form, entering a First Name, Last Name and Username/Email. Ensure that Use the email address as the username is checked and click Create.

We can dial down this user’s capabilities a bit so that all they are allowed to do is use Auth Tokens.

7. Click the Edit user capabilities button.

8. Uncheck all options except Auth Token.

9. Click Save changes.

Next, let’s create the Auth Token.

10. Under the Resources section, click Auth tokens.

11. Click the Generate token button.

12. Enter Object Storage for the Description and click Generate Token.

10. On the next screen, be sure to copy the generated token. This will be the only opportunity to do so.

11. Click Close to dismiss the modal.

While we’re here, let’s create a group. This group will he mapped to a policy that we’ll create in the next section that will allow our user & auth token to access object storage buckets.

12. In the breadcrumb, click Default Domain.

13. Click the tab for Groups.

14. Click Create Group.

15. Enter Read Object Storage as the Name and be sure to select the user that you created in the previous steps.

16. Click Create.

Create a Policy

Now that we have a user and auth token, we will need to create a policy that permits access to the object storage buckets, map that policy to a group, and then add our user to that group. That will allow us to use dbms_credential to make calls to Object Storage using the auth token associated with our user.

The steps here are similar for what was done to get OCI Email Services integrated with our portable development environment.

1. From the OCI Console, navigate to Identity & Security > Policies.

2. Click Create Policy.

3. Enter ReadObjectStorage for the Name and Read Object Storage Policy for the Description.

4. Since we used the Default Domain, set the compartment to the Root compartment. That’s the first one on the list and should end with (root).

5. In the Policy Builder, set Policy Use Cases to Storage Management.

6. Set Common policy templates to Let users download objects from Object Storage buckets.

7. In the middle select list labeled Select a group, set it to Read Object Storage.

8. In Location select list, set it to any compartment and make note of that. We will need to create an Object Storage bucket in that compartment in the next section.

9. Click Create.

At this point, we have configured our OCI security model - the policy includes a specific set of resources and a group and the group includes the user. Thus, the user that we created can call DBMS_CLOUD and present their auth token to read any bucket in the compartment we specified.

Create a Bucket

Before we can test DBMS_CLOUD, let’s make sure that we have at least one bucket in the compartment we referenced in the policy.

1. From the OCI Console, navigate to Storage > Buckets.

2. Set the Compartment to the one that you selected in the previous steps.

3. Click Create Bucket.

4. Enter bucket_test for the Name.

5. Click Create.

6. Next, click the name of the bucket that was just created - bucket_test - to edit the bucket.

Let’s upload a couple images or files to the bucket so that we can properly test it out. Anything will work.

5. In the Objects region, click Upload.

6. Drag the file you wish to upload to the region on the page and click Upload.

7. Once the file uploads, click the Close button.

Repeat steps 5 through 7 a couple of times so that you have a few files to test with.

Before we leave the Object Storage page, we need one more thing - the URL that we will use to access the button. The easiest way to get this it to edit one of the file and copy it from there.

8. In the Objects region, click the three dots next to any of your files and select View Object Details.

9. Copy the value of the URL Path (URI), as we will need that in the next section.

10. Click Cancel to dismiss the modal window.

Create a DBMS_CREDENTIAL

We’re almost there! The last thing we need to do before testing is create a DBMS_CREDENTIAL. This DBMS_CREDENTIAL will allow us to store our username and auth token in a secure place in the database. When we do call DBMS_CLOUD, we’ll pass in the DBMS_CREDENTIAL that we create here, so that DBMS_CLOUD can properly authenticate when making its calls.

The DBMS_CREDENTIAL should be created in the same schema that your APEX application will parse as. Thus, in our case, we’ll stick with the DEMO schema that we created a while back.

1. Open a new terminal window.

2. Connect to the database as the DEMO user, using the following command, replacing [demo password] with the password of the DEMO schema:

sql demo/[demo password]@localhost:1521/freepdb1

3. Once, connected, run the following command, replacing [email address] with the Username that you created previously and [auth token] with the value of the Auth Token that you copied earlier.

begin
  dbms_credential.create_credential
    (
     credential_name => 'obj_store_cred'
    ,username        => '[email address]'
    ,password        => '[auth token]'
  );
end;
/

Test DBMS_CLOUD

And now, the final step.

1. Enter and run the following command, replacing [object storage url] with the value of the URL you copied in the last section without the filename. It should look something similar to this:

   https://objectstorage.us-ashburn-1.oraclecloud.com/n/fjaltjsfsdxw/b/bucket_test/o/

select 
  object_name
 ,bytes
from   
  dbms_cloud.list_objects
    (
     'obj_store_cred'
     ,'[object storage url]'
    )
/

If everything was done correctly, you should see something like this:

The same SQL can also be used in APEX - simply copy it to a report region and run the page.

Summary

Adding DBMS_CLOUD to your containerized development environment adds yet another capability that you can use. With it, you can easily and securely interact with files stored in object storage buckets. This post only covered adding the ability to read files from a bucket. It’s entirely possible to expand on the scope and build APEX applications that provide the ability to upload and even manage buckets themselves.

Keep in mind that you made changes to the database configuration within the container. Thus, it’s different that the image that you downloaded, and if you create a new container from that image, none of these changes will be there. You would have to re-install DBMS_CLOUD to get back to where we are now. Not a huge deal, but an extra step nonetheless.

Title Photo by Vladimir Anikeev on Unsplash