Wednesday, 5 March 2025

Understanding Oracle DBMS_CLOUD Common Packages

Understanding Oracle DBMS_CLOUD Common Packages

Oracle Cloud provides the DBMS_CLOUD package to simplify the integration of cloud-based storage, data loading, and external data access. 

This package is useful for managing data in Oracle Autonomous Database and other cloud environments, enabling users to efficiently interact with external cloud services.

Overview of DBMS_CLOUD

The DBMS_CLOUD package is a collection of procedures and functions that help users perform operations such as loading data, managing credentials, and accessing external storage. It eliminates the need for complex scripting and provides secure access to cloud-based resources.

DBMS_CLOUD Packages and their usages.

1. DBMS_CLOUD.CREATE_CREDENTIAL

This procedure securely stores cloud service credentials, which are used to authenticate database access to object storage.

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'my_credential',
        username => 'your_username',
        password => 'your_password'
    );
END;

2. DBMS_CLOUD.LIST_OBJECTS

Retrieves a list of objects from a specified cloud storage bucket.

SELECT * FROM TABLE(DBMS_CLOUD.LIST_OBJECTS(
    credential_name => 'my_credential',
    location => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/'
));

3. DBMS_CLOUD.GET_OBJECT

Downloads a file from cloud storage into the database directory.

BEGIN
    DBMS_CLOUD.GET_OBJECT(
        credential_name => 'my_credential',
        object_uri => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/sample.csv',
        directory_name => 'DATA_PUMP_DIR',
        file_name => 'sample.csv'
    );
END;

4. DBMS_CLOUD.COPY_DATA

Loads data from an external source (such as an object storage CSV file) into an Oracle database table.

BEGIN
    DBMS_CLOUD.COPY_DATA(
        table_name => 'employees',
        credential_name => 'my_credential',
        file_uri_list => 'https://objectstorage.region.oraclecloud.com/n/namespace/b/bucket/o/employees.csv',
        format => JSON_OBJECT('delimiter' VALUE ',', 'skipheaders' VALUE 1)
    );
END;

5. DBMS_CLOUD.DELETE_CREDENTIAL

Removes a stored credential when it is no longer needed.

BEGIN
    DBMS_CLOUD.DELETE_CREDENTIAL('my_credential');
END;

Benefits of Using DBMS_CLOUD

  • Secure Cloud Storage Integration – Simplifies connecting Oracle databases with cloud storage services.
  • Efficient Data Loading – Enables quick and efficient bulk data imports from external sources.
  • Flexible Querying – Allows querying external data sources without physically moving data.
  • Improved Performance – Optimized for cloud environments, reducing manual data transfer overhead.
Posted by at