Oracle DBMS_CLOUD Package

Jan 18, 2025·3 min read

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what the DBMS_CLOUD package is and how it's used.

The DBMS_CLOUD package is what lets your Oracle database connect with cloud object storage services like Amazon S3, Azure Blob Storage, and Oracle Cloud Infrastructure (OCI) Object Storage

What is DBMS_CLOUD?

DBMS_CLOUD is a package introduced in Oracle Autonomous Database that lets you work with files stored in cloud storage services directly from your database. With DBMS_CLOUD, you can easily load data from and store data in the cloud without ETL tools or middle-tier servers.

Key Features of DBMS_CLOUD

Load data from cloud storage into your database
Store data from your database into cloud storage
List, delete, and rename files in cloud storage
Supports major cloud storage providers (Amazon S3, Azure Blob Storage, OCI Object Storage)

Getting Started with DBMS_CLOUD

To start using DBMS_CLOUD, you'll need to follow these steps:

Prerequisites:
- Set up an Oracle database (DBMS_CLOUD is included in Autonomous DB and can be installed on-premises depending on the version)
  - this is the MOS note showing how to install it: How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)
  - the install process for Oracle Database +23.7
- Set up an object storage account with your cloud provider
Create Credentials:
- Use the CREATE_CREDENTIAL procedure to securely store the access details for your cloud storage. Example:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OBJ_STORE_CRED',
    username => 'oracle-cloud-username',
    password => 'auth-token'
  );
END;
/

Loading Data:

DBMS_CLOUD provides the COPY_DATA procedure to load data from files in cloud storage into database tables. Here are a few examples:

Loading data from a CSV file:

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'EMPLOYEES',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/employees.csv',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
  );
END;
/

Loading data from multiple CSV files using wildcards:

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'EMPLOYEES',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_*.csv',
    format          => json_object('type' value 'csv', 'skipheaders' value '1')
  );
END;
/

Loading data from a JSON file:

BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name      => 'PRODUCTS',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/products.json',
    format          => json_object('type' value 'json', 'compression' value 'gzip'));
END;
/

Exporting Data:

The EXPORT_DATA procedure allows you to export data from database tables to files in cloud storage. Here are a couple of examples:

Exporting data to a CSV file:

BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/emp_export.csv',
    query           => 'SELECT * FROM employees',
    format          => json_object('type' value 'csv', 'compression' value 'gzip')
  );
END;
/

Exporting data to a JSON file:

BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/emp_export.json',
    query           => 'SELECT * FROM employees',
    format          => json_object('type' value 'json')
  );
END;
/

Creating External Tables

DBMS_CLOUD allows you to create external tables that directly query data in cloud storage without loading it into the database. Here's an example:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name => 'SALES_DATA',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list => 'https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_*.csv',
    format => json_object('type' value 'csv', 'skipheaders' value '1', 'delimiter' value ','),
    column_list => 'SALE_ID NUMBER,
                    CUSTOMER_ID NUMBER,
                    PRODUCT_ID NUMBER,
                    QUANTITY NUMBER,
                    SALE_DATE DATE,
                    PRICE NUMBER(10,2)'
  );
END;
/

Creating Partitioned External Tables:

You can create partitioned external tables using the CREATE_EXTERNAL_PART_TABLE procedure. Here's an example:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
    table_name          => 'EXT_SALES',
    credential_name     => 'OBJ_STORE_CRED',
    format              => json_object('delimiter' value ',', 'skipheaders' value '1'),
    column_list         => 'SALE_ID NUMBER, SALE_DATE DATE, AMOUNT NUMBER',
    partitioning_clause => 'PARTITION BY RANGE (SALE_DATE)
                             (PARTITION SALES_2020 VALUES LESS THAN (TO_DATE(''01-01-2021'', ''DD-MM-YYYY''))
                                LOCATION (''https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2020.csv''),
                              PARTITION SALES_2021 VALUES LESS THAN (TO_DATE(''01-01-2022'', ''DD-MM-YYYY''))
                                LOCATION (''https://objectstorage.uk-london-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2021.csv''))'
  );
END;
/

Creating Hybrid Partitioned Tables:

Hybrid partitioned tables allow you to have some partitions stored in the database and others in cloud storage. Here's an example:

BEGIN
  DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name          => 'HYBRID_SALES',
    credential_name     => 'OBJ_STORE_CRED',
    format              => json_object('delimiter' value ',', 'skipheaders' value '1'),
    column_list         => 'SALE_ID NUMBER, SALE_DATE DATE, AMOUNT NUMBER',
    partitioning_clause => 'PARTITION BY RANGE (SALE_DATE)
                             (PARTITION SALES_2020 VALUES LESS THAN (TO_DATE(''01-01-2021'', ''DD-MM-YYYY''))
                                EXTERNAL LOCATION (''https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2020.csv''),
                              PARTITION SALES_2021 VALUES LESS THAN (TO_DATE(''01-01-2022'', ''DD-MM-YYYY''))
                                EXTERNAL LOCATION (''https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/sales_2021.csv''),
                              PARTITION SALES_2022 VALUES LESS THAN (TO_DATE(''01-01-2023'', ''DD-MM-YYYY'')))'
  );
END;
/

There are a handful of other procedures in DBMS_CLOUD like move, delete, put and more. You can learn more about the procedures in the docs: Oracle DBMS_CLOUD docs

Conclusion:

DBMS_CLOUD is an easy way for organizations looking to integrate their Oracle databases with cloud storage services.

Additional Resources:

-Killian