DBMS_CLOUD Package – A Reference Guide – Striving for Optimal Performance

The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it might help others, here it is…

Introduction

To interact with other cloud services, the Oracle Autonomous Data Warehouse Cloud service provides the DBMS_CLOUD package. With it, the database engine can store/retrieve objects into/from the following cloud services:

Oracle’s Object Storage
Amazon Simple Storage Service (S3)
Microsoft’s Azure Blob Storage

Those services store data as objects within buckets (containers). Therefore, when in this post I use the terms “object” and “bucket”, I mean the concepts provided by those services.

Objects are identified with an URI having the following format (for more details refer to the documentation):

Oracle Cloud: https://swiftobjectstorage.<region>.oraclecloud.com/v1/<tenant>/<bucket>/<objectname>
AWS: https://s3-<region>.amazonaws.com/<bucket>/<objectname>
Azure: https://<account>.blob.core.windows.net/<container>/<objectname>

The access to the objects stored within buckets is protected. To access an object the Oracle Autonomous Data Warehouse Cloud service has to provide identification information. That information is stored into a credential object.

Managing Credentials

This section describes subroutines to handle credential objects that give access to cloud services.

CREATE_CREDENTIAL

The CREATE_CREDENTIAL procedure creates a credential into the current schema. Its specification is the following:

PROCEDURE create_credential(
  credential_name IN VARCHAR2,
  username        IN VARCHAR2,
  password        IN VARCHAR2 DEFAULT NULL,
  tenancy_ocid    IN VARCHAR2 DEFAULT NULL,
  user_ocid       IN VARCHAR2 DEFAULT NULL,
  private_key     IN VARCHAR2 DEFAULT NULL,
  public_key      IN VARCHAR2 DEFAULT NULL,
  region          IN VARCHAR2 DEFAULT NULL
);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

The meaning of the other parameters depends on the accessed cloud services. Refer to the documentation for information about them.

Notes:

A credential is a regular database object belonging to a schema. Its name must comply the usual naming rules used in Oracle Database.

Example:

BEGIN
  dbms_cloud.create_credential(
    credential_name => 'CHRIS',
    username => 'chris',
    password => 'mysecret'
  );
END;

DROP_CREDENTIAL

The DROP_CREDENTIAL procedure drops a credential object from the current schema. Its specification is the following:

PROCEDURE drop_credential(credential_name IN VARCHAR2);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Notes:

The credential is dropped also in case another object relies on it.

Example:

BEGIN
  dbms_cloud.drop_credential(credential_name => 'CHRIS');
END;

ENABLE_CREDENTIAL

The ENABLE_CREDENTIAL procedure enables a credential stored in the current schema. Its specification is the following:

PROCEDURE enable_credential(credential_name IN VARCHAR2);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Example:

BEGIN
  dbms_cloud.enable_credential(credential_name => 'CHRIS');
END;

DISABLE_CREDENTIAL

The DISABLE_CREDENTIAL procedure disables a credential stored in the current schema. Its specification is the following:

PROCEDURE disable_credential(credential_name IN VARCHAR2);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)

Example:

BEGIN
  dbms_cloud.disable_credential(credential_name => 'CHRIS');
END;

UPDATE_CREDENTIAL

The UPDATE_CREDENTIAL procedure updates an attribute associated to a credential stored in the current schema. Its specification is the following:

PROCEDURE update_credential(
  credential_name IN VARCHAR2,
  attribute       IN VARCHAR2,
  value           IN VARCHAR2
);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
ATTRIBUTE: identify the attribute to update (valid values are USERNAME, PASSWORD, TENANCY_OCID, USER_OCID, PRIVATE_KEY, PUBLIC_KEY and REGION)
VALUE: the value to associate to the attribute to update

Example:

BEGIN
  dbms_cloud.update_credential(
    credential_name => 'CHRIS',
    attribute => 'PASSWORD',
    value => 'anothersecret'
  );
END;

Managing Tables

This section describes subroutines that interact with database tables.

CREATE_EXTERNAL_TABLE

The CREATE_EXTERNAL_TABLE procedure creates an external table that can be used to query data stored into a cloud service from the database engine. Its specification is the following:

PROCEDURE create_external_table(
  table_name      IN VARCHAR2,
  credential_name IN VARCHAR2,
  file_uri_list   IN CLOB,
  column_list     IN CLOB,
  field_list      IN CLOB DEFAULT NULL,
  format          IN CLOB DEFAULT NULL
);

Parameters:

TABLE_NAME: the name of the external table
CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
FILE_URI_LIST: comma-delimited list of one or several objects stored in a cloud service
COLUMN_LIST: comma-delimited list of column names and data types
FIELD_LIST: describes the fields in the object; it has the same syntax as the FIELD_LIST clause of external table; refer to the documentation for further information
FORMAT: data formatting options in JSON format; refer to the documentation for further information.

Notes:

The external table is created with the NOLOGFILE and NOBADFILE options. So, in case of troubles, there is no easy way to immediately understand what is wrong. To validate its content and know what is wrong, the VALIDATE_EXTERNAL_TABLE procedure is available (see next section).
The FILE_URI_LIST parameter supports the wildcards “*” and “?”.
The external table is created in the current schema

Example:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS',
    credential_name => 'CHRIS',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
    column_list => 'channel_id CHAR(1), channel_desc VARCHAR2(20), channel_class VARCHAR2(20)',
    format => json_object('type' VALUE 'CSV')
  );
END;

VALIDATE_EXTERNAL_TABLE

The VALIDATE_EXTERNAL_TABLE procedure validates an external table, specifically it checks whether data can be loaded through it. It is overloaded to provide also the ability to return an id associated to the load operation. This id permits to get access to details about the operation itself. Its specification is the following:

PROCEDURE validate_external_table(
  table_name    IN VARCHAR2,
  schema_name   IN VARCHAR2 DEFAULT  NULL,
  rowcount      IN NUMBER   DEFAULT  0,
  stop_on_error IN BOOLEAN  DEFAULT  TRUE
);
PROCEDURE validate_external_table(
  table_name    IN         VARCHAR2,
  operation_id  OUT NOCOPY NUMBER,
  schema_name   IN         VARCHAR2 DEFAULT  NULL,
  rowcount      IN         NUMBER   DEFAULT  0,
  stop_on_error IN         BOOLEAN  DEFAULT  TRUE
);

Parameters:

TABLE_NAME: the name of the external table
SCHEMA_NAME: the owner of the external table (NULL = current schema)
ROWCOUNT: how many rows to read during the validation (0 = all rows)
STOP_ON_ERROR: whether to stop immediately in case bad records are found
OPERATION_ID: output parameter that provides the id of the load operation; it can for example be used to identify the corresponding information in USER_LOAD_OPERATIONS

Notes:

For two days, the log and bad files associated to the operation can be accessed through external tables. Their name is visible in USER_LOAD_OPERATIONS.LOGFILE_TABLE and USER_LOAD_OPERATIONS.BADFILE_TABLE.

Example:

BEGIN
  DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
    table_name => 'CHANNELS'
  );
END;

COPY_DATA

The COPY_DATA procedure reads, through an external table it creates, an object stored in a cloud service and loads its contents into a heap table. It is overloaded to provide also the ability to return an id associated to the load operation. This id permits to get access to details about the operation itself. In other words, the procedure can be used to move data from a cloud service to a heap table. Its specification is the following:

PROCEDURE copy_data(
  table_name      IN VARCHAR2,
  credential_name IN VARCHAR2,
  file_uri_list   IN CLOB,
  schema_name     IN VARCHAR2 DEFAULT NULL,
  field_list      IN CLOB     DEFAULT NULL,
  format          IN CLOB     DEFAULT NULL
);
PROCEDURE copy_data(
  table_name      IN         VARCHAR2,
  credential_name IN         VARCHAR2,
  file_uri_list   IN         CLOB,
  operation_id    OUT NOCOPY NUMBER,
  schema_name     IN         VARCHAR2 DEFAULT NULL,
  field_list      IN         CLOB     DEFAULT NULL,
  format          IN         CLOB     DEFAULT NULL
);

Parameters:

TABLE_NAME: the name of the heap table
CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
FILE_URI_LIST: comma-delimited list of one or several objects stored in a cloud service; refer to the documentation for further information
SCHEMA_NAME: the owner of the heap table (NULL = current schema)
FIELD_LIST: describes the fields in the object; it has the same syntax as the FIELD_LIST clause of external table; refer to the documentation for further information
FORMAT: data formatting options in JSON format; refer to the documentation for further information
OPERATION_ID: output parameter that provides the id of the load operation; it can for example be used to identify the corresponding information in USER_LOAD_OPERATIONS

Notes:

The FILE_URI_LIST parameter supports the wildcards “*” and “?”.
To access the object stored in the cloud service, an external table is created and, when the load operation is over, it is immediately dropped.
For two days, the log and bad files associated to the operation can be accessed through external tables. Their name is visible in USER_LOAD_OPERATIONS.LOGFILE_TABLE and USER_LOAD_OPERATIONS.BADFILE_TABLE.

Example:

BEGIN
  dbms_cloud.copy_data(
    table_name => 'CHANNELS',
    credential_name => 'CHRIS',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
    format => json_object('type' VALUE 'CSV')
  );
END;

DELETE_OPERATION

The DELETE_OPERATION procedure cleans up the objects and information created during the execution of an operation carried out by the VALIDATE_EXTERNAL_TABLE and COPY_DATA procedures. Its specification is the following:

PROCEDURE delete_operation(id IN NUMBER);

Parameters:

ID: the id of the operation to be cleaned up (USER_LOAD_OPERATIONS.ID)

Notes:

Only operations carried out by the current user are considered.

Example:

BEGIN
  dbms_cloud.delete_operation(id => 42);
END;

DELETE_ALL_OPERATIONS

The DELETE_ALL_OPERATIONS procedure cleans up the objects and information related to all operations carried out by the VALIDATE_EXTERNAL_TABLE and COPY_DATA procedures. Its specification is the following:

PROCEDURE delete_all_operations(type IN VARCHAR DEFAULT NULL);

Parameters:

TYPE: the type of the operations to delete (USER_LOAD_OPERATIONS.TYPE; NULL = all types)

Notes:

Only operations carried out by the current user are considered.

Example:

BEGIN
  dbms_cloud.delete_all_operations(type => 'VALIDATE');
END;

Managing Objects

This section describes subroutines to handle objects stored in a cloud service.

PUT_OBJECT

The PUT_OBJECT procedure stores an object into a bucket. It is overloaded to provide the ability to read data from a BLOB or from a directory object. In other words, it can be used to move data from the database server to a cloud service. Its specification is the following:

PROCEDURE put_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  contents        IN BLOB,
  compression     IN VARCHAR2 DEFAULT NULL
);
PROCEDURE put_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  directory_name  IN VARCHAR2,
  file_name       IN VARCHAR2,
  compression     IN VARCHAR2 DEFAULT NULL
);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
OBJECT_URI: the URI of the object or, when the data is read from a directory object, the URI of the bucket
DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
FILE_NAME: the name of the file
COMPRESSION: the compression algorithm used to store the object; valid values are DBMS_CLOUD.COMPRESS_NONE (NULL), DBMS_CLOUD.COMPRESS_AUTO (‘AUTO’) and DBMS_CLOUD.COMPRESS_GZIP (‘GZIP’).

Notes:

If the object already exists, it is overwritten.
In case a directory object is used and only the destination bucket is specified, the file name is used as object name.
Even though the compression algorithms ZLIB and BZIP2 are defined by the package, they are not supported by the PUT_OBJECT procedures.

Example:

Get data from a BLOB stored in the database

DECLARE
  l_contents BLOB;
BEGIN
  SELECT report_compressed INTO l_contents FROM dba_hist_reports_details WHERE rownum = 1;
  dbms_cloud.put_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/report.dat',
    contents => l_contents
  );
END;

Get data from the DATA_PUMP_DIR directory object

BEGIN
  dbms_cloud.put_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/',
    directory_name => 'DATA_PUMP_DIR',
    file_name => 'channels.txt',
    compression => dbms_cloud.compress_auto
  );
END;

LIST_OBJECTS

The LIST_OBJECTS pipelined function returns the list of objects stored in a bucket. Its specification as well as the specification of the type of the return value are the following:

FUNCTION list_objects(
  credential_name IN VARCHAR2,
  location_uri    IN VARCHAR2
) RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;

TYPE list_object_ret_t IS RECORD (object_name VARCHAR2(4000), bytes NUMBER);
TYPE list_object_ret_tab IS TABLE OF list_object_ret_t;

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
LOCATION_URI: the address of the bucket

Return value:

The table contains one record for each object. Every record provides the name of the object as well as its size in bytes. If the object is compressed, it is the size of the compressed file, not the original one.

Example:

SELECT * 
FROM table(dbms_cloud.list_objects(
             credential_name => 'CHRIS', 
             location_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/'
           ))
WHERE object_name LIKE '%.txt'

OBJECT_NAME    BYTES
------------- ------
channels.txt     104

GET_METADATA

The GET_METADATA function returns the metadata associated to an object. Its specification is the following:

FUNCTION get_metadata(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2
) RETURN CLOB;

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
OBJECT_URI: the URI of the object

Return value:

The metadata associated to the object in JSON format.

Example:

SELECT dbms_cloud.get_metadata(
         credential_name => 'CHRIS', 
         object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt'
       ) AS metadata
FROM dual

METADATA 
-----------------------
{"Content-Length":104}

GET_OBJECT

The GET_OBJECT function reads an object stored into a bucket and returns it as a BLOB. It is overloaded to provide also the ability to write the data into a file stored in a directory object. In other words, it can be used to move data from a cloud service to the database server. Its specification is the following:

FUNCTION get_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  startOffset     IN NUMBER   DEFAULT 0,
  endOffset       IN NUMBER   DEFAULT 0,
  compression     IN VARCHAR2 DEFAULT NULL
) RETURN BLOB;
FUNCTION get_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2,
  directory_name  IN VARCHAR2,
  file_name       IN VARCHAR2 DEFAULT NULL,
  startOffset     IN NUMBER   DEFAULT 0,
  endOffset       IN NUMBER   DEFAULT 0,
  compression     IN VARCHAR2 DEFAULT NULL
) RETURN BLOB;

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
OBJECT_URI: the URI of the object
STARTOFFSET: the offset (in bytes) from where data is read
ENDOFFSET: the offset (in bytes) until where data is read
DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
FILE_NAME: the name of the file
COMPRESSION: the compression algorithm used to store the object; valid values are DBMS_CLOUD.COMPRESS_NONE (NULL), DBMS_CLOUD.COMPRESS_AUTO (‘AUTO’) and DBMS_CLOUD.COMPRESS_GZIP (‘GZIP’).

Return value:

A LOB locator giving access to the data read from the object.

Notes:

In case a directory object is used and the file name is not specified, the object name is used as file name.
The offsets start from 0. For example, to read the first 1000 bytes, STARTOFFSET has to be set to 0 and ENDOFFSET has to be set to 999.
If the compression algorithm matches the one used to store the object, data is uncompressed.

Example:

SELECT to_clob(dbms_cloud.get_object(
         credential_name => 'CHRIS',		
	     object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt',
         startOffset => 0,
         endOffset => 20
       )) AS data
FROM dual

DATA 
----------------------
S,Direct Sales,Direct

DELETE_OBJECT

The DELETE_OBJECT procedure removes an object from a bucket. Its specification is the following:

PROCEDURE delete_object(
  credential_name IN VARCHAR2,
  object_uri      IN VARCHAR2
);

Parameters:

CREDENTIAL_NAME: the name of the credential object (USER_CREDENTIALS.CREDENTIAL_NAME)
OBJECT_URI: the URI of the object

Example:

BEGIN
  dbms_cloud.delete_object(
    credential_name => 'CHRIS',
    object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt'
  );
END;

Managing Files

This section describes subroutines to handle files stored in the file system of the database server. Since no directory object can be created, only the files stored in the directory referenced by the DATA_PUMP_DIR directory object are visible.

LIST_FILES

The LIST_FILES pipelined function lists the files stored in a directory. Its specification as well as the specification of the type of the return value are the following:

FUNCTION list_files(
  directory_name IN VARCHAR2
) RETURN dbms_cloud_types.list_object_ret_tab PIPELINED;

TYPE list_object_ret_t IS RECORD (object_name VARCHAR2(4000), bytes NUMBER);
TYPE list_object_ret_tab IS TABLE OF list_object_ret_t;

Parameters:

DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)

Return value:

The table contains one record for each file. Every record provides the name of the file as well as its size in bytes.

Notes:

To use the procedure successfully, the caller requires the READ privilege on the directory.

Example:

SELECT * 
FROM table(dbms_cloud.list_files('DATA_PUMP_DIR'))
WHERE object_name LIKE '%.txt'

OBJECT_NAME    BYTES
------------- ------
channels.txt      99

DELETE_FILE

The DELETE_FILE procedure deletes a single file from a directory. Its specification is the following:

PROCEDURE delete_file(
  directory_name IN VARCHAR2,
  file_name      IN VARCHAR2
);

Parameters:

DIRECTORY_NAME: the name of the directory object (ALL_DIRECTORIES.DIRECTORY_NAME)
FILE_NAME: the name of the file

Notes:

To use the procedure successfully, the caller requires the WRITE privilege on the modified directory.
If the file does not exist, no exception is raised.
Wildcards are not supported.

Example:

BEGIN
  dbms_cloud.delete_file(
    directory_name => 'DATA_PUMP_DIR',
    file_name      => 'channels.txt'
  );
END;

7 Comments

Jim Czuprynski

2 September 2018 - 18:37

Christian, a really nice bit of work here! Most useful for those of us beginning to play with ADWC and ATP. Thanks in advance!

1. Kelvin
  
  6 June 2020 - 19:37
  
  Hi Chris. Great article. I’m using a user other than admin to play around with this stuff but getting errors saying DATA_PUMP_DIR does not exist. Have you come across this issue? When I select from all_directories seeing zero records.
  
  1. Christian Antognini Author
    
    9 June 2020 - 14:19
    
    Hi Kelvin,
    
    if you don’t see it in all_directories, it’s likely that the user have neigher READ nor WRITE privilges on DATA_PUMP_DIR. I would check that via the ADMIN user.
    
    Best,
    Chris
    
Jared Still

31 January 2019 - 20:16

Very nice, thanks Chris!

Marco Patzwahl

14 February 2019 - 10:23

Good Job! Thanks for sharing

Ram Srinivasan

17 September 2021 - 16:07

This is very useful article. Thank you.

If I have forgotten the credential name I had created, how can I see the list of existing credentials.

1. Christian Antognini Author
  
  20 September 2021 - 06:02
  
  To get the credential name, query USER_CREDENTIALS or DBA_CREDENTIALS.

DBMS_CLOUD Package – A Reference Guide

Introduction

Managing Credentials

CREATE_CREDENTIAL

DROP_CREDENTIAL

ENABLE_CREDENTIAL

DISABLE_CREDENTIAL

UPDATE_CREDENTIAL

Managing Tables

CREATE_EXTERNAL_TABLE

VALIDATE_EXTERNAL_TABLE

COPY_DATA

DELETE_OPERATION

DELETE_ALL_OPERATIONS

Managing Objects

PUT_OBJECT

LIST_OBJECTS

GET_METADATA

GET_OBJECT

DELETE_OBJECT

Managing Files

LIST_FILES

DELETE_FILE

7 Comments

Leave a Reply Cancel reply