A Package DBMS_CLOUD

This section provides information about the DBMS_CLOUD package you use with Oracle Autonomous Data Warehouse to load, query, and save data.

Summary of DBMS_CLOUD Subprograms

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

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.

Syntax

DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name	IN VARCHAR2
	username IN VARCHAR2
	password 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.

password

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

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.

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 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.

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.

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.

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.

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. The only allowed value is data_pump_dir.

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 data_pump_dir 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;

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.

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

Parameters

Parameter Description

directory_name

The name of the directory on the Autonomous Data Warehouse instance. The only allowed value is data_pump_dir.

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 data_pump_dir 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;

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. The only allowed value is data_pump_dir.

Foot 1

file_name

The name of the file in the directory data_pump_dir.

Footnote 1

Note:

To run DBMS_CLOUD.PUT_OBJECT with a user other than ADMIN you need to grant read privileges on the directory data_pump_dir 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.

DBMS_CLOUD Package File URI Formats

Describes the format of the source file URIs in operations with DBMS_CLOUD. The format depends on the object storage service you are using.

Oracle Cloud Infrastructure Object Storage URI Format

If your source files reside in Oracle Cloud Infrastructure Object Storage, the format for files is:

https://swiftobjectstorage.region.oraclecloud.com/v1/object_storage_namespace/bucket/filename

For example, the following is the URI for the file channels.txt in the adwc_user bucket in the adwc object storage name in the Phoenix data center:

https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/adwc/adwc_user/channels.txt

Oracle Cloud Infrastructure Object Storage Classic URI Format

If your source files reside in Oracle Cloud Infrastructure Object Storage Classic, see the REST page for a description of the URI format for accessing your files: About REST URLs for Oracle Cloud Infrastructure Object Storage Classic Resources.

Amazon S3 URI Format

If your source files reside in Amazon S3, see the following for a description of the URI format for accessing your files: Accessing a Bucket.

For example the following refers to the file channels.txt in the adwc bucket in the us-west-2 region.

https://s3-us-west-2.amazonaws.com/adwc/channels.txt 

Azure Blob Storage URI Format

If your source files reside in Azure Blob Storage, see the following for a description of the URI format for accessing your files: Resource URI Syntax.

For example the following refers to the file channels.txt in the adwc container in the storage account adwc_user:
https://adwc_user.blob.core.windows.net/adwc/channels.txt

DBMS_CLOUD Package Format Options

The format argument in DBMS_CLOUD specifies the format of source files.

The two ways to specify the format argument are:

format => '{"format_option" : “format_value” }'  

And:

format => json_object('format_option' value 'format_value'))

For example:

format => json_object('type' VALUE 'CSV')

Table A-1 DBMS_CLOUD Format Options

Format Option Description Syntax Default

recorddelimiter

Specifies the record delimiter

recorddelimiter : character

newline

delimiter

Specifies the field delimiter

delimiter : character

| (pipe character)

trimspaces

Specifies how the leading and trailing spaces of the fields are trimmed.

See Oracle Database Utilities, 12c Release 2 (12.2)

trimspaces : rtrim| ltrim| notrim| lrtrim| ldrtrim

notrim

characterset

Specifies the characterset of source files

characterset : string

Database characterset

skipheaders

Specifies how many rows should be skipped from the start of the file.

skipheaders : number

0 if not specified, 1 if specified without a value

escape

The character "\" is used as the escape character when specified.

escape : true

False

ignoreblanklines

Blank lines are ignored when set to true.

ignoreblanklines : true

False

type

Specifies the source file type.

Cannot be specified together with delimiter or quote.

See Oracle Database Utilities, 12c Release 2 (12.2)

type : csv|csv with embedded|csv without embedded

csv is the same as csv without embedded.

Null

quote

Specifies the quote character for the fields, the quote characters are removed during loading when specified.

quote : character

Null meaning no quote

ignoremissingcolumns

If there are more columns in the field_list than there are in the source files, the extra columns are stored as null.

ignoremissingcolumns : true

False

truncatecol

If the data in the file is too long for a field, then this option will truncate the value of the field rather than reject the row.

truncatecol : true

False

removequotes

Removes any quotes that are around any field in the source file.

removequotes: true

False

blankasnull

When set to true, loads fields consisting of spaces as null.

blankasnull : true

False

dateformat

Specifies the date format in the source file. The format option AUTO searches for the following formats:

J 
MM-DD-YYYYBC 
MM-DD-YYYY 
YYYYMMDD HHMISS 
YYMMDD HHMISS 
YYYY.DDD 
YYYY-MM-DD

dateformat : string

Database date format

timestampformat

Specifies the timestamp format in the source file. The format option AUTO searches for the following formats:

YYYY-MM-DD HH:MI:SS.FF 
YYYY-MM-DD HH:MI:SS.FF3 
MM/DD/YYYY HH:MI:SS.FF3

timestampformat : string

Database timestamp format

timestamptzformat

Specifies the timestamp with timezone format in the source file. The format option AUTO searches for the following formats:

DD Mon YYYY HH:MI:SS.FF TZR 
MM/DD/YYYY HH:MI:SS.FF TZR 
YYYY-MM-DD HH:MI:SS+/-TZR 
YYYY-MM-DD HH:MI:SS.FF3 
DD.MM.YYYY HH:MI:SS TZR

timestamptzformat: string

Database timestamp with timezone format

timestampltzformat

Specifies the timestamp with local timezone format in the source file. The format option AUTO searches for the following formats:

DD Mon YYYY HH:MI:SS.FF TZR 
MM/DD/YYYY HH:MI:SS.FF TZR 
YYYY-MM-DD HH:MI:SS+/-TZR 
YYYY-MM-DD HH:MI:SS.FF3 
DD.MM.YYYY HH:MI:SS TZR

timestampltzformat : string

Database timestamp with local timezone format

conversionerrors

If a row is rejected because of data type conversion errors, the related columns are stored as null or the row is rejected.

conversionerrors : reject_record | store_null

reject_record

rejectlimit

The operation will error out after specified number of rows are rejected.

rejectlimit : number

0

compression

Specifies the compression type of the source file

compression: auto|gzip|zlib|bzip2

Specifying the value auto checks for the compression types: gzip, zlib, bzip2.

Null value meaning no compression.