DBMS_CLOUD is a package that manages several Autonomous Database processes and routines, especially when working with all of the cloud resources that are part of this database system.

The cloud console provides an interface, but behind the scenes many of these activities use the DBMS_CLOUD package. Instead of using the user interface, there is a package that you can use to do several of these tasks, from loading data to setting up credentials.

DBMS_CLOUD offers subprograms for the following areas:
• Access management
• Object and file storage
• Bulk file management
• REST APIs

Credentials are set up for the management within the package and will allow for setting up least privileges for loading data and for querying external data in cloud resources including in other clouds besides OCI.

Permissions on the package are needed. When you create a user in Database Actions, there are options to allow for setting the permissions, but through SQL you can also just grant the following:

SQL> grant execute on dbms_cloud to mmalcher;

Credentials are stored in the DBA/ALL/USER_CREDENTIALS view, which grants access to OCI users for managing resources that are external to the Autonomous Database and allowing for data to be exported and loaded or processed for various sources.

To run these procedures, you can be connected through SQL in Database Actions or SQL Developer connections to the Autonomous Database.

SQL> begin dbms_cloud.create_credential( credential_name => ‘OCI_ADB_DATAMGMT’, username => ‘[email protected]’, password => ‘Cr4zyPa$$w0rd!’);end;

Objects and files can be in object storage in the cloud and in other clouds, and instead of having a local file, you need to access these files for external tables to work with the data in data lakes and in other formats.

Here is an example of creating an external table in Autonomous using object storage files (assuming we have the customer name, dates, and totals in the file):

SQL> begin dbms_cloud.create_external_table ( table_name => ‘CUSTOMER_SALES_JULY’,credential_name => ‘OCI_ADB_DATAMGMT’,file_uri_list => ‘https://objectstorage.us-ashburn-1.oracelcloud.com/n/ namespace1/b/customer_sales/cust_sales_0723.csv’,format => json_object(‘type’ value ‘csv’, ‘skipheaders’ value ‘1’), field_list => ‘CUSTOMER_ID,CUSTOMER_NAME, CUSTOMER_TOTAL,SALE_DATE DATE ”mm/dd/yyyy”’, column_list => ‘CUSTOMER_ID NUMBER, CUSTOMER_NAME VARCHAR2(100), CUSTOMER_TOTAL NUMBER,SALE_DATE DATE’); end;

The format for the external tables can be CSV, JSON, ORC, Avro, or Parquet and is not just limited to CSV files. Indexes can also be created on the externalfileswithdbms_cloud.create_external_text_index to be able to search through the files and find values.

As one more example for objects and files, here is an example to copy a file:

SQL> begin dbms_cloud.copy_object (source_credential_name => ‘OCI_ADB_DATAMGMT’,
source_object_uri => ‘https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace1/b/customer_sales/o/cust_sales_june.csv’,target_object_uri => ‘https://objectstorage.us-ashburn-1.oraclecloud.com/n/ namespace1/b/ext_tables_bucket/o/cust_sales_june.csv’);end;

You can also use the dbms_cloud.list_files function to get a list of the files in directories. As we saw for data_pump, there is a directory that is needed, and in Autonomous, there isn’t a file system; however, object storage can serve as thesedirectories.
You can query using this function to get the details of the files listed in the data_pump_dir directory.

SQL> select * from dbms_cloud.list_files(‘DATA_PUMP_DIR’);

And if you want to see the objects in one of the locations, you can list the objects in a bucket:

SQL> select * from dbms_cloud.list_objects(‘OCI_ADB_DATAMGMT’, ‘https://objectstorage.us-ashburn-1.oraclecloud.com/n/namspace1 /b/customer_sales/o/’);

Just like there are individual files, there are also procedures to handle bulk moves, uploads, and copies.
Another area for DBMS_CLOUD is the Cloud REST API procedures and functions. These procedures can get details about the Cloud REST APIs and can be used in PL/SQL and application code to get API requests and results. Since these are some of the various cloud APIs, you can do things such as creating buckets in object storage to store the files and manage these type of resources.

Tip there are several procedures and functions available in DBMS_CLOUD, and reviewing the documentation is very useful here.We barely touched the surface of this package, and it is most important to understand that the capabilities are there for managing the data and files in the cloud for autonomous environments.