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…
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:
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):
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.
This section describes subroutines to handle credential objects that give access to cloud services.
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:
The meaning of the other parameters depends on the accessed cloud services. Refer to the documentation for information about them.
Notes:
Example:
BEGIN dbms_cloud.create_credential( credential_name => 'CHRIS', username => 'chris', password => 'mysecret' ); END;
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:
Notes:
Example:
BEGIN dbms_cloud.drop_credential(credential_name => 'CHRIS'); END;
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:
Example:
BEGIN dbms_cloud.enable_credential(credential_name => 'CHRIS'); END;
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:
Example:
BEGIN dbms_cloud.disable_credential(credential_name => 'CHRIS'); END;
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:
Example:
BEGIN dbms_cloud.update_credential( credential_name => 'CHRIS', attribute => 'PASSWORD', value => 'anothersecret' ); END;
This section describes subroutines that interact with database tables.
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:
Notes:
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;
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:
Notes:
Example:
BEGIN DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE ( table_name => 'CHANNELS' ); END;
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:
Notes:
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;
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:
Notes:
Example:
BEGIN dbms_cloud.delete_operation(id => 42); END;
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:
Notes:
Example:
BEGIN dbms_cloud.delete_all_operations(type => 'VALIDATE'); END;
This section describes subroutines to handle objects stored in a cloud service.
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:
Notes:
Example:
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;
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;
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:
Return value:
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
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:
Return value:
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}
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:
Return value:
Notes:
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
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:
Example:
BEGIN dbms_cloud.delete_object( credential_name => 'CHRIS', object_uri => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/chris/tmp/channels.txt' ); END;
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.
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:
Return value:
Notes:
Example:
SELECT * FROM table(dbms_cloud.list_files('DATA_PUMP_DIR')) WHERE object_name LIKE '%.txt' OBJECT_NAME BYTES ------------- ------ channels.txt 99
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:
Notes:
Example:
BEGIN dbms_cloud.delete_file( directory_name => 'DATA_PUMP_DIR', file_name => 'channels.txt' ); END;
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 10 weeks ago
2 years 11 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 19 weeks ago