Summary of DBMS_CLOUD Subprograms

This section covers the DBMS_CLOUD subprograms provided with Autonomous Data Warehouse.

Note:

To run DBMS_CLOUD subprograms with a user other than ADMIN you need to grant EXECUTE privileges to that user. For example, run the following command as ADMIN to grant privileges to adwc_user::
GRANT EXECUTE ON DBMS_CLOUD TO adwc_user;

COPY_DATA Procedure

This procedure loads data into existing Autonomous Data Warehouse tables from files in the Cloud.

Syntax

DBMS_CLOUD.COPY_DATA (
	table_name        IN VARCHAR2,
	credential_name   IN VARCHAR2,		
	file_uri_list     IN CLOB,	
	schema_name       IN VARCHAR2 DEFAULT,
	field_list        IN CLOB DEFAULT,
	format            IN CLOB DEFAULT);

Parameters

Parameter Description

table_name

The name of the target table on the Autonomous Data Warehouse database. The target table needs to be created before you run COPY_DATA.

credential_name

The name of the credential to access the Cloud Object Storage.

file_uri_list

Comma-delimited list of source file URIs. You can use wildcards in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

The format of the URIs depend on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

schema_name

The name of the schema where the target table resides. The default value is NULL meaning the target table is in the same schema as the user running the procedure.

field_list

Identifies the fields in the source files and their data types. The default value is NULL meaning the fields and their data types are determined by the target table definition. This argument's syntax is the same as the field_list clause in regular Oracle external tables. For more information about field_list see Oracle® Database Utilities.

For an example using field_list, see CREATE_EXTERNAL_TABLE Procedure.

format

The options describing the format of the source files. For the list of the options and how to specify the values see DBMS_CLOUD Package Format Options.

For Parquet and Avro file format options, see DBMS_CLOUD Package Format Options for Parquet and Avro.

COPY_DATA Procedure for Parquet or Avro Files

This procedure with the format parameter type set to the value parquet or avro loads data into existing Autonomous Data Warehouse tables from Parquet or Avro files in the Cloud. Similar to text files, the data is copied from the source Parquet or Avro file into the preexisting internal table.

Syntax

DBMS_CLOUD.COPY_DATA (
	table_name        IN VARCHAR2,
	credential_name   IN VARCHAR2,		
	file_uri_list     IN CLOB,	
	schema_name       IN VARCHAR2 DEFAULT,
	field_list        IN CLOB DEFAULT,
	format            IN CLOB DEFAULT);

Parameters

Parameter Description

table_name

The name of the target table on the Autonomous Data Warehouse database. The target table needs to be created before you run COPY_DATA.

credential_name

The name of the credential to access the Cloud Object Storage.

file_uri_list

Comma-delimited list of source file URIs. You can use wildcards in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

The format of the URIs depend on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

schema_name

The name of the schema where the target table resides. The default value is NULL meaning the target table is in the same schema as the user running the procedure.

field_list

Ignored for Parquet and Avro files.

The fields in the source match the external table columns by name. Source data types are converted to the external table column data type.

For Parquet files, see DBMS_CLOUD Package Parquet to Oracle Data Type Mapping for details on mapping.

For Avro files, see DBMS_CLOUD Package Avro to Oracle Data Type Mapping for details on mapping.

format

The options describing the format of the source files. For Parquet and Avro files, only two options are supported: see DBMS_CLOUD Package Format Options for Parquet and Avro.

Usage Notes

As with other data files, Parquet and Avro data loads generate logs that are viewable in the tables dba_load_operations and user_load_operations. Each load operation adds a record to dba[user]_load_operations that indicates the table containing the logs.

The log table provides summary information about the load.

Note:

For Parquet or Avro files, when the format parameter type is set to the value parquet or avro, the BADFILE_TABLE table is always empty.

  • For Parquet files, PRIMARY KEY constraint errors throw an ORA error.
  • If data for a column encounters a conversion error, for example, the target column is not large enough to hold the converted value, the value for the column is set to NULL. This does not produce a rejected record.

CREATE_CREDENTIAL Procedure

This procedure stores Cloud Object Storage credentials in the Autonomous Data Warehouse database.

Use stored credentials for data loading or for querying external data residing in the Cloud, where you use DBMS_CLOUD procedures with a credential_name parameter. This procedure is overloaded. Use the Oracle Cloud Infrastructure-related parameters, including: user_ocid, tenancy_ocid, private_key, and fingerprint only when you are using Oracle Cloud Infrastructure native authentication.

Syntax

DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name   IN VARCHAR2,
	username          IN VARCHAR2,
	password          IN VARCHAR2 DEFAULT NULL);


DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name IN VARCHAR2,
	user_ocid       IN VARCHAR2,
	tenancy_ocid    IN VARCHAR2,
	private_key     IN VARCHAR2,
	fingerprint     IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to be stored.

username

The username and password arguments together specify your object storage credentials. See below for what to specify for the username and password for different object stores.

password

The username and password arguments together specify your object storage credentials.

user_ocid

Specifies the user's OCID. See Where to Get the Tenancy's OCID and User's OCID for details on obtaining the User's OCID.

tenancy_ocid

Specifies the tenancy's OCID. See Where to Get the Tenancy's OCID and User's OCID for details on obtaining the Tenancy's OCID.

private_key

Specifies the generated private key. Private keys generated with a passphrase are not supported. You need to generate the private key without a passphrase. See How to Generate an API Signing Key for details on generating a key pair in PEM format.

fingerprint

Specifies a fingerprint. After a generated public key is uploaded to the user's account the fingerprint is displayed in the console. Use the displayed fingerprint for this argument. See How to Get the Key's Fingerprint and How to Generate an API Signing Key for more details.

Usage Notes

  • This operation stores the credentials in the database in an encrypted format.

  • You can see the credentials in your schema by querying the user_credentials table.

  • The ADMIN user can see all the credentials by querying the dba_credentials table.

  • You only need to create credentials once unless your object store credentials change. Once you store the credentials you can then use the same credential name for DBMS_CLOUD procedures that require a credential_name parameter.

  • This procedure is overloaded. If you provide one of the key based authentication attributes, user_ocid, tenancy_ocid, private_key, or fingerprint, the call is assumed to be an Oracle Cloud Infrastructure Native credential.

  • Private keys generated with a passphrase are not supported. You need to generate the private key without a passphrase. See How to Generate an API Signing Key for more information.

Oracle Cloud Infrastructure Object Storage Credentials

If your source files reside in Oracle Cloud Infrastructure Object Storage the username is your Oracle Cloud Infrastructure user name. The password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens.

Oracle Cloud Infrastructure Object Storage Classic Credentials

If your source files reside in Oracle Cloud Infrastructure Object Storage Classic, the username is your Oracle Cloud Infrastructure Classic user name and the password is your Oracle Cloud Infrastructure Classic password.

Amazon S3 Credentials

If your source files reside in Amazon S3 the username is your AWS access key ID and the password is your AWS secret access key. See AWS Identity and Access Management.

Azure Blob Storage Credentials

If your source files reside in Azure Blob Storage, the username is your Azure storage account name and the password is an Azure storage account access key. See About Azure storage accounts.

CREATE_EXTERNAL_TABLE Procedure

This procedure creates an external table on files in the Cloud. This allows you to run queries on external data from Autonomous Data Warehouse.

Syntax

DBMS_CLOUD.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,
	format           IN CLOB DEFAULT);

Parameters

Parameter Description

table_name

The name of the external table.

credential_name

The name of the credential to access the Cloud Object Storage.

file_uri_list

Comma-delimited list of source file URIs. You can use wildcards in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

The format of the URIs depend on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

column_list

Comma-delimited list of column names and data types for the external table.

field_list

Identifies the fields in the source files and their data types. The default value is NULL meaning the fields and their data types are determined by the column_list parameter. This argument's syntax is the same as the field_list clause in regular Oracle external tables. For more information about field_list see Oracle® Database Utilities.

format

The options describing the format of the source files. For the list of the options and how to specify the values see DBMS_CLOUD Package Format Options.

For Parquet or Avro format files, see CREATE_EXTERNAL_TABLE Procedure for Parquet or Avro Files.

Example

BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(   
      table_name =>'WEATHER_REPORT_DOUBLE_DATE',   
      credential_name =>'OBJ_STORE_CRED',   
      file_uri_list =>'&base_URL/Charlotte_NC_Weather_History_Double_Dates.csv',
      format => json_object('type' value 'csv', 'skipheaders' value '1'),   
      field_list => 'REPORT_DATE DATE''mm/dd/yy'',                   
                     REPORT_DATE_COPY DATE ''yyyy-mm-dd'',
                     ACTUAL_MEAN_TEMP,                 
                     ACTUAL_MIN_TEMP,                 
                     ACTUAL_MAX_TEMP,                 
                     AVERAGE_MIN_TEMP,                    
                     AVERAGE_MAX_TEMP,     
                     AVERAGE_PRECIPITATION',   
      column_list => 'REPORT_DATE DATE,   
                     REPORT_DATE_COPY DATE,
                     ACTUAL_MEAN_TEMP NUMBER,  
                     ACTUAL_MIN_TEMP NUMBER,  
                     ACTUAL_MAX_TEMP NUMBER,  
                     AVERAGE_MIN_TEMP NUMBER,   
                     AVERAGE_MAX_TEMP NUMBER,                  
                     AVERAGE_PRECIPITATION NUMBER');
   END;
/ 

SELECT * FROM WEATHER_REPORT_DOUBLE_DATE where         
   actual_mean_temp > 69 and actual_mean_temp < 74

CREATE_EXTERNAL_TABLE Procedure for Parquet or Avro Files

This procedure with the format parameter type set to the value parquet or avro creates an external table with either Parquet or Avro format files in the Cloud. This allows you to run queries on external data from Autonomous Data Warehouse.

Syntax

DBMS_CLOUD.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,
	format           IN CLOB DEFAULT);

Parameters

Parameter Description

table_name

The name of the external table.

credential_name

The name of the credential to access the Cloud Object Storage.

file_uri_list

Comma-delimited list of source file URIs. You can use wildcards in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

The format of the URIs depend on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

column_list

(Optional) This field, when specified, overrides the format->schema parameter which specifies that the schema, columns and data types, are derived automatically. See the format parameter for details.

When the column_list is specified for a Parquet or Avro source, the column names must match those columns found in the file. Oracle data types must map appropriately to the Parquet or Avro data types.

For Parquet files, see DBMS_CLOUD Package Parquet to Oracle Data Type Mapping for details.

For Avro files, see DBMS_CLOUD Package Avro to Oracle Data Type Mapping for details.

field_list

Ignored for Parquet or Avro files.

The fields in the source match the external table columns by name. Source data types are converted to the external table column data type.

For Parquet files, see DBMS_CLOUD Package Parquet to Oracle Data Type Mapping for details.

For Avro files, see DBMS_CLOUD Package Avro to Oracle Data Type Mapping for details.

format

For Parquet and Avro, there are only two supported parameters. See DBMS_CLOUD Package Format Options for Parquet and Avro for details.

Examples Avro

format => '{"type":"avro", "schema": "all"}'
format => json_object('type' value 'avro', 'schema' value 'first')

Examples Parquet

format => '{"type":"parquet", "schema": "all"}'
format => json_object('type' value 'parquet', 'schema' value 'first')

AVRO and Parquet Column Name Mapping to Oracle Column Names

See DBMS_CLOUD Package Parquet and AVRO to Oracle Column Name Mapping for information on column name mapping and column name conversion usage in Oracle SQL.

DELETE_ALL_OPERATIONS Procedure

This procedure clears either all data load operations logged in the user_load_operations table in your schema or clears all the data load operations of the specified type, as indicated with the type parameter.

Syntax

DBMS_CLOUD.DELETE_ALL_OPERATIONS (
	type      IN VARCHAR DEFAULT NULL);

Parameters

Parameter Description

type

Specifies the type of operation to delete. Type values can be found in the TYPE column in the user_load_operations table.

If no type is specified all rows are deleted.

Usage Note

  • DBMS_CLOUD.DELETE_ALL_OPERATIONS does not delete currently running operations (operations in a "Running" status).

DELETE_FILE Procedure

This procedure removes the specified file from the specified directory on Autonomous Data Warehouse.

Syntax

 DBMS_CLOUD.DELETE_FILE ( 
       directory_name     IN VARCHAR2,
       file_name          IN VARCHAR2); 

Parameters

Parameter Description

directory_name

The name of the directory on the Autonomous Data Warehouse instance.

file_name

The name of the file to be removed.

Note:

To run DBMS_CLOUD.DELETE_FILE with a user other than ADMIN you need to grant write privileges on the directory that contains the file to that user. For example, run the following command as ADMIN to grant write privileges to adwc_user:
GRANT WRITE ON DIRECTORY data_pump_dir TO adwc_user;

DELETE_OBJECT Procedure

This procedure deletes the specified object on object store.

Syntax

DBMS_CLOUD.DELETE_OBJECT (
       credential_name      IN VARCHAR2,
       location_uri         IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.

location_uri

Object or file URI for the object to delete. The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

DROP_CREDENTIAL Procedure

This procedure removes an existing credential from Autonomous Data Warehouse.

Syntax

DBMS_CLOUD.DROP_CREDENTIAL (
   credential_name     IN VARCHAR2);

Parameters

Parameter Description

credential_name

The name of the credential to be removed.

GET_OBJECT Procedure

This procedure reads an object from Cloud Object Storage and copies it to Autonomous Data Warehouse. The maximum file size allowed in this procedure is 5 gigabytes (GB).

Syntax

DBMS_CLOUD.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);	

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.

object_uri

Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

directory_name

The name of the directory on the Autonomous Data Warehouse database.

Foot 1

file_name

Specifies the name of the file to create. If file name is not specified, the file name is taken from after the last slash in the object_uri parameter. For special cases, for example when the file name contains slashes, use the file_name parameter.

startoffset

The offset, in bytes, from where the procedure starts reading.

endoffset

The offset, in bytes, until where the procedure stops reading.

compression

Specifies the compression used to store the object. When compression is set to ‘AUTO’ the file is uncompressed (the value ‘AUTO’ implies the object specified with object_uri is compressed with Gzip).

Footnote 1

Note:

To run DBMS_CLOUD.GET_OBJECT with a user other than ADMIN you need to grant WRITE privileges on the directory to that user. For example, run the following command as ADMIN to grant write privileges to adwc_user:

GRANT WRITE ON DIRECTORY data_pump_dir TO adwc_user;

Example

BEGIN DBMS_CLOUD.GET_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adwc/b/adwc_user/o/cwallet.sso',
     directory_name => 'DATA_PUMP_DIR'); 
END;/

LIST_FILES Function

This function lists the files and their sizes in the specified directory on Autonomous Data Warehouse.

Syntax

DBMS_CLOUD.LIST_FILES (
	directory_name      IN VARCHAR2)
       RETURN TABLE;

Parameters

Parameter Description

directory_name

The name of the directory on the Autonomous Data Warehouse database.

This is a pipelined function that returns a row for each file. For example, use the following query to use this function:

SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

Note:

To run DBMS_CLOUD.LIST_FILES with a user other than ADMIN you need to grant read privileges on the directory to that user. For example, run the following command as ADMIN to grant read privileges to adwc_user:

GRANT READ ON DIRECTORY data_pump_dir TO adwc_user;

LIST_OBJECTS Function

This function lists objects and their sizes in the specified location on object store.

Syntax

DBMS_CLOUD.LIST_OBJECTS (
       credential_name      IN VARCHAR2,
       location_uri         IN VARCHAR2)
   RETURN TABLE;

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.

location_uri

Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

This is a pipelined function that returns a row for each object. For example, use the following query to use this function:

SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('OBJ_STORE_CRED', 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adwc/b/adwc_user/o/');

PUT_OBJECT Procedure

This procedure copies a file from Autonomous Data Warehouse to the Cloud Object Storage. The maximum file size allowed in this procedure is 5 gigabytes (GB).

Syntax

DBMS_CLOUD.PUT_OBJECT (
	credential_name      IN VARCHAR2,		
	object_uri           IN VARCHAR2,		
	directory_name       IN VARCHAR2,
	file_name            IN VARCHAR2);	

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.

object_uri

Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.

directory_name

The name of the directory on the Autonomous Data Warehouse database.

Foot 2

file_name

The name of the file in the specified directory.

Footnote 2

Note:

To run DBMS_CLOUD.PUT_OBJECT with a user other than ADMIN you need to grant read privileges on the directory to that user. For example, run the following command as ADMIN to grant read privileges to adwc_user:

GRANT READ ON DIRECTORY data_pump_dir TO adwc_user;

VALIDATE_EXTERNAL_TABLE Procedure

This procedure validates the source files for an external table, generates log information, and stores the rows that do not match the format options specified for the external table in a badfile table on Autonomous Data Warehouse.

Syntax

DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (
	table_name      IN VARCHAR2,
	schema_name     IN VARCHAR2 DEFAULT,		
	rowcount        IN NUMBER DEFAULT,
	stop_on_error   IN BOOLEAN DEFAULT);

Parameters

Parameter Description

table_name

The name of the external table.

schema_name

The name of the schema where the external table resides. The default value is NULL meaning the external table is in the same schema as the user running the procedure.

rowcount

Number of rows to be scanned. The default value is NULL meaning all the rows in the source files are scanned.

stop_on_error

Determines if the validate should stop when a row is rejected. The default value is TRUE meaning the validate stops at the first rejected row. Setting the value to FALSE specifies that the validate does not stop at the first rejected row and validates all rows up to the value specified for the rowcount parameter.

If the external table refers to Parquet or Avro file(s) the validate stops at the first rejected row. When the external table specifies the format parameter type set to the value parquet or avro, the parameter stop_on_error is effectively always TRUE. Thus, the table badfile will always be empty for an external table referring to Parquet or Avro file(s).