top of page

Oracle DBMS_CLOUD Package

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 its used.


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


What is DBMS_CLOUD?

DBMS_CLOUD is a package introduced in Oracle Database 21c (and back ported to 19.10) 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, Oracle Cloud Infrastructure Object Storage)


Getting Started with DBMS_CLOUD


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

  1. Prerequisites:

    • Set up an Oracle database (DBMS_CLOUD is included in Autonomous DB and can be installed in 19c+ on-premises)

      • this is the MOS note showing how to install it: How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

    • Set up an object storage account with your cloud provider

  2. 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_CRED1',
    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:

  1. 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 'CVS', 'skipheaders' value '1')
  );
END;
/
  1. 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 'CVS', 'skipheaders' value '1')
  );
END;
/
  1. 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:

  1. 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;
/
  1. 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 copy, delete, move, 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




Kommentare


Disclaimer: The blogs and content I produce are entirely my own and do not represent an official stance of my employer, Oracle Corp. I do not speak on behalf of Oracle.

© 2023 Killiansbytes. All rights reserved.

bottom of page