DBMS_CLOUD Subprograms and REST APIs

This section covers the DBMS_CLOUD subprograms and REST APIs provided with Autonomous Database.

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 adb_user:

GRANT EXECUTE ON DBMS_CLOUD TO adb_user;

This DBMS_CLOUD package is made up of the following:

• DBMS_CLOUD for Access Management

• DBMS_CLOUD for Objects and Files

• DBMS_CLOUD REST APIs

DBMS_CLOUD for Access Management

The subprograms for credential management within the DBMS_CLOUD package, including creating, deleting, and updating credentials.

Subprogram	Description
CREATE_CREDENTIAL Procedure	This procedure stores cloud service credentials in Autonomous Database.
DROP_CREDENTIAL Procedure	This procedure removes an existing credential from Autonomous Database.
UPDATE_CREDENTIAL Procedure	This procedure updates cloud service credential attributes in Autonomous Database.

CREATE_CREDENTIAL Procedure

This procedure stores cloud service credentials in Autonomous Database.

Use stored cloud service credentials to access the cloud service for data loading, for querying external data residing in the cloud, or for other cases when 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 Signing Keys authentication.

• Use the AWS ARN-related parameter params, only when you are using Amazon Resource Names (ARNs) credentials.

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


DBMS_CLOUD.CREATE_CREDENTIAL (
	credential_name  IN VARCHAR2,
	params           IN CLOB DEFAULT);

Parameters

Parameter	Description
credential_name	The name of the credential to be stored. The credential_name parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens.
username	The username and password arguments together specify your cloud service credentials. See the usage notes for what to specify for the username and password for different cloud services.
password	The username and password arguments together specify your cloud service 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.
params	Specifies credential parameters using Amazon Resource Names (ARNs) 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 cloud service 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 Signing Key based credential.

Oracle Cloud Infrastructure Credentials (Auth Tokens)

For Oracle Cloud Infrastructure the username is your Oracle Cloud Infrastructure user name. The password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens.

For example:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => '[e-mail]',
    password => 'password' );
END;
/

Oracle Cloud Infrastructure Signing Key Based Credentials

Use the Oracle Cloud Infrastructure signing key related parameters, including: user_ocid, tenancy_ocid, private_key, and fingerprint with Oracle Cloud Infrastructure Signing Keys authentication.

For example:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => ‘OCI_KEY_CRED’,
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
       private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
       fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
END;
/

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 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 Web Services (AWS) Credentials

If your source files reside in Amazon S3 or you are calling an AWS API, the username is your AWS access key ID and the password is your AWS secret access key. See AWS Identity and Access Management.

Microsoft Azure Credentials

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

Amazon S3-Compatible Credentials

Service	Credentials Information
Oracle Cloud Infrastructure (Customer Secret Keys)	If your source files reside in Oracle Cloud Infrastructure, then you need to use Customer Secret Keys with S3-compatible URLs. See Working with Customer Secret Keys for more information.
Google Cloud Storage	If your source files reside in Google Cloud Storage or you are calling Google Cloud Storage APIs, then you need to set a default Google project and obtain an HMAC key to create credentials to supply with Google Cloud Storage S3-compatible URLs. Use the HMAC key id as the username, and the HMAC secret as the password. See Projects and HMAC Keys for more information.
Wasabi Hot Cloud Storage	If your source files reside in Wasabi Hot Cloud Storage or you are calling Wasabi Hot Cloud Storage APIs, then you need Access Keys to create credentials to supply with S3-compatible URLs. Use the Wasabi Hot Cloud Storage Access Key as the username, and the Wasabi Hot Cloud Storage Secret Key as the password. See Creating a Wasabi API Access Key Set for more information.

AWS Amazon Resource Names (ARN) Credentials

If your source files reside in Amazon S3 or you are calling an AWS API, use params to specify the parameters for the Amazon Resource Names (ARN).

Parameter	Value
aws_role_arn	Specifies the Amazon Resource Name (ARN) that identifies the AWS role. If this parameter is not supplied when creating the credential, ORA-20041 is raised.
external_id_type	Optionally set the external_id_type to use the Autonomous Database compartment OCID, database OCID, or tenancy OCID by supplying one of: compartment_ocid, database_ocid, or tenant_ocid. If this parameter is not given when creating the credential, the default value is database_ocid.

For example:

BEGIN DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name      => 'MY_CRED',
        params               => JSON_OBJECT(
               'aws_role_arn'      value 'arn:aws:iam::123456:role/AWS_ROLE_ARN',
               'external_id_type'  value 'database_ocid'));
END;
/

GitHub Personal Access Token

If your source files reside in a GitHub repository or you are calling a GitHub API, the username is your GitHub email and the password is your GitHub personal access token. See Creating a personal access token for more information.

For example:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MY_GITHUB_CRED',
    username => '[e-mail]',
    password => 'your_personal_access_token' );
END;
/

DROP_CREDENTIAL Procedure

This procedure removes an existing credential from Autonomous Database.

Syntax

DBMS_CLOUD.DROP_CREDENTIAL (
   credential_name     IN VARCHAR2);

Parameters

Parameter	Description
credential_name	The name of the credential to be removed.

UPDATE_CREDENTIAL Procedure

This procedure updates cloud service credential attributes in Autonomous Database.

Use stored credentials for data loading, for querying external data residing in the Cloud, or wherever you use DBMS_CLOUD procedures with a credential_name parameter. This procedure lets you update an attribute with a new value for a specified credential_name.

Syntax

DBMS_CLOUD.UPDATE_CREDENTIAL (
	credential_name   IN VARCHAR2,
	attribute         IN VARCHAR2,
	value             IN VARCHAR2);

Parameters

Parameter	Description
credential_name	The name of the credential to be stored.
attribute	Name of attribute to update: USERNAME or PASSWORD. For a credential for an Amazon ARN, the valid attribute values are: aws_role_arn and external_id_type.
value	New value for the selected attribute.

Usage Notes

• The user name is case sensitive. It cannot contain double quotes or spaces.

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

• You only need to create credentials once unless your cloud service 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.

Examples

BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL(
     credential_name => 'OBJ_STORE_CRED',
     attribute => 'PASSWORD',
     value => 'password'); 
END;
/

BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL(
     credential_name => 'ARN_CRED',
     attribute => 'aws_role_arn',
     value => 'NEW_AWS_ARN'); 
END;
/

DBMS_CLOUD for Objects and Files

The subprograms for object and file management within the DBMS_CLOUD package.

Subprogram	Description
COPY_COLLECTION Procedure	This procedure loads data into existing SODA collection from Cloud Object Storage.
COPY_DATA Procedure	This procedure loads data into existing Autonomous Database tables from files in the Cloud.
COPY_DATA Procedure for Avro, ORC, or Parquet Files	This procedure with the format parameter type set to the value orc, parquet, or avro loads data into existing Autonomous Database tables from ORC, Parquet, or Avro files in the Cloud. Similar to text files, the data is copied from the source ORC, Parquet, or Avro file into the preexisting internal table.
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 Database.
CREATE_EXTERNAL_TABLE Procedure for Avro, ORC, or Parquet Files	This procedure with the format parameter type set to the value parquet, orc, or avro, creates an external table with either Parquet, ORC, or Avro format files in the Cloud. This allows you to run queries on external data from Autonomous Database.
CREATE_EXTERNAL_PART_TABLE Procedure	This procedure creates an external partitioned table on files in the Cloud. This allows you to run queries on external data from Autonomous Database.
CREATE_HYBRID_PART_TABLE Procedure	This procedure creates a hybrid partitioned table. This allows you to run queries on hybrid partitioned data from Autonomous Database.
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.
DELETE_FILE Procedure	This procedure removes the specified file from the specified directory on Autonomous Database
DELETE_OBJECT Procedure	This procedure deletes the specified object on object store.
EXPORT_DATA Procedure	This procedure exports data from Autonomous Database to files in the Cloud based on the result of the specified SQL query. The overloaded form enables you to use the operation_id parameter. Depending on the format parameter specified, the procedure exports rows to the Cloud Object store as text format with options of CSV, JSON, or XML format files or using the ORACLE_DATAPUMP access driver to write data to a dump file.
GET_OBJECT Procedure and Function	This procedure is overloaded. The procedure form reads an object from Cloud Object Storage and copies it to Autonomous Database. The function form reads an object from Cloud Object Storage and returns a BLOB to Autonomous Database.
LIST_FILES Function	This function lists the files in the specified directory. The results include the file names and additional metadata about the files such as file size in bytes, creation timestamp, and the last modification timestamp.
LIST_OBJECTS Function	This function lists objects in the specified location on object store. The results include the object names and additional metadata about the objects such as size, checksum, creation timestamp, and the last modification timestamp.
PUT_OBJECT Procedure	This procedure is overloaded. In one form the procedure copies a file from Autonomous Database to the Cloud Object Storage. In another form the procedure copies a BLOB from Autonomous Database to the Cloud Object Storage.
SYNC_EXTERNAL_PART_TABLE Procedure	This procedure simplifies updating an external partitioned table from files in the Cloud. Run this procedure whenever new partitions are added or when partitions are removed from the Object Store source for the external partitioned table.
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 Database.
VALIDATE_EXTERNAL_PART_TABLE Procedure	This procedure validates the source files for an external partitioned 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 Database.
VALIDATE_HYBRID_PART_TABLE Procedure	This procedure validates the source files for a hybrid partitioned table, generates log information, and stores the rows that do not match the format options specified for the hybrid table in a badfile table on Autonomous Database.

COPY_COLLECTION Procedure

This procedure loads data into a SODA collection from Cloud Object Storage. If the specified SODA collection does not exist, the procedure creates it. The overloaded form enables you to use the operation_id parameter.

Syntax

DBMS_CLOUD.COPY_COLLECTION (
	collection_name   IN VARCHAR2,
	credential_name   IN VARCHAR2 DEFAULT NULL,		
	file_uri_list     IN CLOB,	
	format            IN CLOB     DEFAULT NULL
);

DBMS_CLOUD.COPY_COLLECTION (
	collection_name   IN VARCHAR2,
	credential_name   IN VARCHAR2 DEFAULT NULL,		
	file_uri_list     IN CLOB,	
	format            IN CLOB     DEFAULT NULL,
	operation_id      OUT NOCOPY NUMBER
);

Parameters

Parameter	Description
collection_name	The name of the SODA collection into which data will be loaded. If a collection with this name already exists, the specified data will be loaded, otherwise a new collection is created.
credential_name	The name of the credential to access the Cloud Object Storage. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 depends on the Cloud Object Storage service. See DBMS_CLOUD Package File URI Formats for more information.
format	The options describing the format of the source files. These options are specified as a JSON string. Supported formats for JSON data are: characterset, compression, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, type, unpackarrays. Apart from the mentioned formats for JSON data, Autonomous Database supports other formats too. For the list of format arguments supported by Autonomous Database, see DBMS_CLOUD Package Format Options.
operation_id	Use this parameter to track the progress and final status of the load operation as the corresponding ID in the USER_LOAD_OPERATIONS view.

Example

BEGIN
	DBMS_CLOUD.CREATE_CREDENTIAL(    
			credential_name => 'OBJ_STORE_CRED',    
			username        => '[e-mail]',    
			password        => 'password'
			);

	DBMS_CLOUD.COPY_COLLECTION(    
			collection_name => 'myCollection',    
			credential_name => 'OBJ_STORE_CRED',    
			file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adbexample/b/json/o/myCollection.json'  
			);
END;
/

COPY_DATA Procedure

This procedure loads data into existing Autonomous Database tables from files in the Cloud. The overloaded form enables you to use the operation_id parameter.

Syntax

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

DBMS_CLOUD.COPY_DATA (
	table_name        IN VARCHAR2,
	credential_name   IN VARCHAR2 DEFAULT NULL,		
	file_uri_list     IN CLOB DEFAULT NULL,	
	schema_name       IN VARCHAR2 DEFAULT NULL,
	field_list        IN CLOB DEFAULT NULL,
	format            IN CLOB DEFAULT NULL
	operation_id      OUT NOCOPY NUMBER);

Parameters

Parameter	Description
table_name	The name of the target table on the 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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
file_uri_list	This parameter specifies a 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. When the format parameter type option value is json, this parameter is ignored. 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 Avro, ORC, or Parquet file format options, see DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet.
operation_id	Use this parameter to track the progress and final status of the load operation as the corresponding ID in the USER_LOAD_OPERATIONS view.

Usage Note

The default record delimiter is detected newline. With detected newline, DBMS_CLOUD tries to automatically find the correct newline character to use as the record delimiter. DBMS_CLOUD first searches for the Windows newline character \r\n. If it finds the Windows newline character, this is used as the record delimiter for all files in the procedure. If a Windows newline character is not found, DBMS_CLOUD searches for the UNIX/Linux newline character \n, and if it finds one it uses \n as the record delimiter for all files in the procedure. If the source files use a combination of different record delimiters, you may encounter an error such as, "KUP-04020: found record longer than buffer size supported". In this case, you need to either modify the source files to use the same record delimiter or only specify the source files that use the same record delimiter.

See DBMS_CLOUD Package Format Options for information on the recorddelmiter format option.

COPY_DATA Procedure for Avro, ORC, or Parquet Files

This procedure with the format parameter type set to the value avro, orc, or parquet loads data into existing Autonomous Database tables from Avro, ORC, or Parquet files in the Cloud. Similar to text files, the data is copied from the source Avro, ORC, or Parquet 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 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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 Avro, ORC, or Parquet 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 ORC files, see DBMS_CLOUD Package ORC to Oracle Data Type Mapping 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 Avro, ORC, or Parquet files, only two options are supported: see DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet.

Usage Notes

• As with other data files, Avro, ORC, and Parquet 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.

• For Avro, ORC, or Parquet, when the format parameter type is set to the value avro, orc, or parquet, 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_EXTERNAL_PART_TABLE Procedure

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

Syntax

DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
	table_name           IN VARCHAR2,
	credential_name      IN VARCHAR2,
	partitioning_clause  IN CLOB,
	column_list          IN CLOB,
	field_list           IN CLOB DEFAULT,
	format               IN CLOB DEFAULT);


DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
	table_name           IN VARCHAR2,
	credential_name      IN VARCHAR2,
	file_uri_list        IN VARCHAR2,
	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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
partitioning_clause	Specifies the complete partitioning clause, including the location information for individual partitions. If you use the partitioning_clause parameter, the file_url_list parameter is not allowed.
file_uri_list	There are two options for the file_uri_list parameter: A common-delimited list of individual file URIs without wildcards. A single file URI with wildcards, and the wildcards can only be after the last slash "/". If you use the parameter file_url_list, the partitioning_clause parameter is not allowed. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File URI Formats for more information.
column_list	Comma-delimited list of column names and data types for the external table. This parameter has the following requirements, depending on the type of the data files specified with the file_url_list parameter: The column_list parameter is required with unstructured files. Using unstructured files, for example with CSV text files, the column_list parameter must specify all the column names and data types inside the data file as well as the partition columns derived from the object name. The column_list parameter is optional with structured files. For example, with Avro, ORC, or Parquet data files, the column_list is not required. When the column_list is not included, the format parameter partition_columns option must include specifications for both column names (name) and data types (type).
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 format option partition_columns specifies the DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE column names and data types of partition columns when the partition columns are derived from the file path, depending on the type of data file, structured or unstructured: When the DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE includes the column_list parameter and the data files are unstructured, such as with CVS text files, partition_columns does not include the data type. For example, use a format such as the following for this type of partition_columns specification: '"partition_columns":["state","zipcode"]' The data type is not required because it is specified in the DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE column_list parameter. When the DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE does not include the column_list parameter and the data files are structured, such as Avro, ORC, or Parquet files, the partition_columns option includes both the column name, name sub-clause, and the data type, type sub-clause. For example, the following shows a partition_columns specification: '"partition_columns":[ {"name":"country", "type":"varchar2(10)"}, {"name":"year", "type":"number"}, {"name":"month", "type":"varchar2(10)"}]' If the data files are unstructured and the type sub-clause is specified with partition_columns, the type sub-clause is ignored. For object names that are not based on hive format, the order of the partition_columns specified columns must match the order as they appear in the object name in the file path specified in the file_url_list parameter. To see all the format parameter options describing the format of the source files, see DBMS_CLOUD Package Format Options.

Usage Notes

• You cannot call this procedure with both partitioning_clause and file_url_list parameters.

• Specifying the column_list parameter is optional with structured data files, including Avro, Parquet, or ORC data files. If column_list is not specified, the format parameter partition_columns option must include both name and type.

• The column_list parameter is required with unstructured data files, such as CSV text files.

• The procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE supports external partitioned files in the supported cloud object storage services, including:

  • Oracle Cloud Infrastructure Object Storage

  • Azure Blob Storage

  • Amazon S3

  • Amazon S3-Compatible, including: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage, and Wasabi Hot Cloud Storage.

  • GitHub Repository

  See DBMS_CLOUD Package File URI Formats for more information.

• When you call DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE with the file_url_list parameter, the types for columns specified in the Cloud Object Store file name must be one of the following types:

  VARCHAR2(n)
  NUMBER(n)
  NUMBER(p,s)
  NUMBER
  DATE
  TIMESTAMP(9)

• The default record delimiter is detected newline. With detected newline, DBMS_CLOUD tries to automatically find the correct newline character to use as the record delimiter. DBMS_CLOUD first searches for the Windows newline character \r\n. If it finds the Windows newline character, this is used as the record delimiter for all files in the procedure. If a Windows newline character is not found, DBMS_CLOUD searches for the UNIX/Linux newline character \n, and if it finds one it uses \n as the record delimiter for all files in the procedure. If the source files use a combination of different record delimiters, you may encounter an error such as, "KUP-04020: found record longer than buffer size supported". In this case, you need to either modify the source files to use the same record delimiter or only specify the source files that use the same record delimiter.

  See DBMS_CLOUD Package Format Options for information on the recorddelmiter format option.

• The external partitioned tables you create with DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE include two invisible columns file$path and file$name. These columns help identify which file a record is coming from.

  • file$path: Specifies the file path text up to the beginning of the object name.

  • file$name: Specifies the object name, including all the text that follows the bucket name.

Examples

Example using the partitioning_clause parameter:

BEGIN  
   DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
      table_name =>'PET1',  
      credential_name =>'OBJ_STORE_CRED',
      format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),
      column_list => 'col1 number, col2 number, col3 number',
      partitioning_clause => 'partition by range (col1)
                                (partition p1 values less than (1000) location
                                    ( ''&base_URL//file_11.txt'')
                                 ,
                                 partition p2 values less than (2000) location
                                    ( ''&base_URL/file_21.txt'')
                                 ,
                                 partition p3 values less than (3000) location 
                                    ( ''&base_URL/file_31.txt'')
                                 )'
     );
   END;
/  

Example using the file_uri_list and column_list parameters with unstructured data files:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
   table_name => 'MYSALES',
   credential_name => 'DEF_CRED_NAME',
   file_uri_list     => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.csv', 
   column_list       => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)', 
   field_list        => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
   format            => '{"type":"csv", "partition_columns":["country","year","month"]}');
END;
/ 

Example using the file_uri_list without the column_list parameter with structured data files:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
  table_name => 'MYSALES',
  credential_name => 'DEF_CRED_NAME',
  DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
    table_name      => 'MYSALES',
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/*.parquet',
    format          => 
        json_object('type' value 'parquet', 'schema' value 'first',
                    'partition_columns' value 
                          json_array(
                                json_object('name' value 'country', 'type' value 'varchar2(100)'),
                                json_object('name' value 'year', 'type' value 'number'),
                                json_object('name' value 'month', 'type' value 'varchar2(2)')
                          )
         )
    );
END;
/

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

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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 Avro, ORC, or Parquet format files, see CREATE_EXTERNAL_TABLE Procedure for Avro, ORC, or Parquet Files.

Usage Notes

• The procedure DBMS_CLOUD.CREATE_EXTERNAL_TABLE supports external partitioned files in the supported cloud object storage services, including:

  • Oracle Cloud Infrastructure Object Storage

  • Azure Blob Storage

  • Amazon S3

  • Amazon S3-Compatible, including: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage, and Wasabi Hot Cloud Storage.

  • GitHub Repository

  The credential is a table level property; therefore, the external files must be on the same object store.

  See DBMS_CLOUD Package File URI Formats for more information.

• The default record delimiter is detected newline. With detected newline, DBMS_CLOUD tries to automatically find the correct newline character to use as the record delimiter. DBMS_CLOUD first searches for the Windows newline character \r\n. If it finds the Windows newline character, this is used as the record delimiter for all files in the procedure. If a Windows newline character is not found, DBMS_CLOUD searches for the UNIX/Linux newline character \n, and if it finds one it uses \n as the record delimiter for all files in the procedure. If the source files use a combination of different record delimiters, you may encounter an error such as, "KUP-04020: found record longer than buffer size supported". In this case, you need to either modify the source files to use the same record delimiter or only specify the source files that use the same record delimiter.

  See DBMS_CLOUD Package Format Options for information on the recorddelmiter format option.

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 Avro, ORC, or Parquet Files

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

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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 Avro, ORC, or Parquet source, the column names must match those columns found in the file. Oracle data types must map appropriately to the Avro, ORC, or Parquet data types. For Parquet files, see DBMS_CLOUD Package Parquet to Oracle Data Type Mapping for details. For ORC files, see DBMS_CLOUD Package ORC 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 Avro, ORC, or Parquet 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 ORC files, see DBMS_CLOUD Package ORC to Oracle Data Type Mapping 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 Avro, ORC, or Parquet, there are only two supported parameters. See DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet for details.

Examples ORC

format => '{"type":"orc", "schema": "all"}'

format => json_object('type' value 'orc', 'schema' value 'first')

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, ORC, or Parquet Column Name Mapping to Oracle Column Names

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

CREATE_HYBRID_PART_TABLE Procedure

This procedure creates a hybrid partitioned table. This allows you to run queries on hybrid partitioned data from Autonomous Database.

Syntax

DBMS_CLOUD.CREATE_HYBRID_PART_TABLE (
	table_name           IN VARCHAR2,
	credential_name      IN VARCHAR2,		
	partitioning_clause  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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
partitioning_clause	Specifies the complete partitioning clause, including the location information for individual partitions.
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.

Usage Note

• The procedure DBMS_CLOUD.CREATE_HYBRID_PART_TABLE supports external partitioned files in the supported cloud object storage services, including:

  • Oracle Cloud Infrastructure Object Storage

  • Azure Blob Storage

  • Amazon S3

  • Amazon S3-Compatible, including: Oracle Cloud Infrastructure Object Storage, Google Cloud Storage, and Wasabi Hot Cloud Storage.

  • GitHub Repository

  The credential is a table level property; therefore, the external files must be on the same object store.

  See DBMS_CLOUD Package File URI Formats for more information.

• The external partitioned tables you create with DBMS_CLOUD.CREATE_HYBRID_PART_TABLE include two invisible columns file$path and file$name. These columns help identify which file a record is coming from.

  • file$path: Specifies the file path text up to the beginning of the object name.

  • file$name: Specifies the object name, including all the text that follows the bucket name.

Example

BEGIN  
   DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
      table_name =>'HPT1',  
      credential_name =>'OBJ_STORE_CRED',  
      format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),  
      column_list => 'col1 number, col2 number, col3 number',
      partitioning_clause => 'partition by range (col1)
                                (partition p1 values less than (1000) external location
                                    ( ''&base_URL/file_11.txt'')
                                 ,
                                 partition p2 values less than (2000) external location
                                    ( ''&base_URL/file_21.txt'')
                                 ,
                                 partition p3 values less than (3000)
                                 )'
     );
   END;
/ 

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

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 Database 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 adb_user:

GRANT WRITE ON DIRECTORY data_pump_dir TO adb_user;

Example

BEGIN
   DBMS_CLOUD.DELETE_FILE(
      directory_name =>  'DATA_PUMP_DIR',
      file_name => 'exp1.dmp');
   END;
/ 

DELETE_OBJECT Procedure

This procedure deletes the specified object on object store.

Syntax

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

Parameters

Parameter	Description
credential_name	The name of the credential to access the Cloud Object Storage. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
object_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.

Example

BEGIN
   DBMS_CLOUD.DELETE_OBJECT(
       credential_name => 'DEF_CRED_NAME',
       object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.dmp');
   END;
/ 

EXPORT_DATA Procedure

This procedure exports data from Autonomous Database to files in the Cloud based on the result of the specified SQL query. The overloaded form enables you to use the operation_id parameter. Depending on the format type parameter value, the procedure exports rows to the Cloud Object Store as text files in CSV, JSON, or XML format or using the ORACLE_DATAPUMP access driver to write data to a dump file.

Syntax

DBMS_CLOUD.EXPORT_DATA (
      file_uri_list     IN CLOB,
      format            IN CLOB,
      credential_name   IN VARCHAR2 DEFAULT NULL,
      query             IN CLOB);

DBMS_CLOUD.EXPORT_DATA (
      file_uri_list     IN CLOB DEFAULT NULL,
      format            IN CLOB DEFAULT NULL,
      credential_name   IN VARCHAR2 DEFAULT NULL,
      query             IN CLOB DEFAULT NULL,
      operation_id      OUT NOCOPY NUMBER);

Parameters

Parameter	Description
credential_name	The name of the credential to access the Cloud Object Storage. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
file_uri_list	There are two different forms, depending on the value of the format parameter: When the format parameter type value is json: The JSON on Object Store is saved with a generated file name based on the value of the file_uri_list parameter. See File Naming with Text Output (CSV, JSON, or XML) for more information. When the format parameter type value is datapump, the file_uri_list is a comma-delimited list of the dump files. This specifies the files to be created on the Object Store. Use of wildcard and substitution characters is not supported in the file_uri_list. The format of the URIs depend on the Cloud Object Storage service you are using, for details see DBMS_CLOUD Package File URI Formats.
format	Specify export format options. Supported options are: type: The type format option is required and must have one of the values: csv|datapump|json|xml When type option value is csv, json, or xml, see DBMS_CLOUD Package Format Options for EXPORT_DATA with Text Files (CSV, JSON, and XML). In addition, with the format option set to the value datapump you can specify supported Oracle Data Pump access parameters. The supported Oracle Data Pump access parameters are: COMPRESSION: The valid values are: BASIC, LOW, MEDIUM, and HIGH. VERSION: The valid values are: COMPATIBLE, LATEST, and a specified version_number. See access_parameters Clause for more information.
query	Use this parameter to specify a SELECT statement so that only the required data is exported. The query determines the contents of the files you export as text files (CSV, JSON, or XML) or dump files. For example: SELECT warehouse_id, quantity FROM inventories For information with the format type value datapump, see Oracle Data Pump Export Data Filters and Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for more information. When the format type value is json, each query result is checked and if it is not JSON, as determined with the function: JSON_OBJECT_T.parse(), DBMS_CLOUD.EXPORT_DATA transforms the query to include JSON_OBJECT function to convert the row into JSON. See JSON_OBJECT_T Object Type for more information. For example: SELECT JSON_OBJECT(* RETURNING CLOB) from(SELECT warehouse_id, quantity FROM inventories)
operation_id	Use this parameter to track the progress and final status of the export operation as the corresponding ID in the USER_LOAD_OPERATIONS view.

Usage Notes:

• The query parameter value that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

• Depending on the format parameter specified, DBMS_CLOUD.EXPORT_DATA writes the results of the specified query on the Cloud Object Store in one of these formats:

  • JSON files.

    See Move Data to Object Store as CSV, JSON, or XML Using EXPORT_DATA for more information on using DBMS_CLOUD.EXPORT_DATA with JSON output files.

  • Using the ORACLE_DATAPUMP access driver to write data to a dump file.

Usage Notes for ORACLE_DATAPUMP Output (DBMS_CLOUD.EXPORT_DATA with format parameter type option datapump):

• Autonomous Database export using DBMS_CLOUD.EXPORT_DATA with format parameter type option datapump only supports Oracle Cloud Infrastructure Object Storage and Oracle Cloud Infrastructure Object Storage Classic object stores.

• Oracle Data Pump divides each dump file part into smaller chunks for faster uploads. The Oracle Cloud Infrastructure Object Storage console shows multiple files for each dump file part that you export. The size of the actual dump files will be displayed as zero (0) and its related file chunks as 10mb or less. For example:

  exp01.dmp
  exp01.dmp_aaaaaa
  exp02.dmp
  exp02.dmp_aaaaaa

  Downloading the zero byte dump file from the Oracle Cloud Infrastructure console or using the Oracle Cloud Infrastructure CLI will not give you the full dump files. To download the full dump files from the Object Store, use a tool that supports Swift such as curl, and provide your user login and Swift auth token.

  curl -O -v -X GET -u '[e-mail]:auth_token' \
     https://swiftobjectstorage.us-ashburn-1.oraclecloud.com/v1/namespace-string/bucketname/exp01.dmp

  If you import a file with the DBMS_CLOUD procedures that support the format parameter type with the value 'datapump', you only need to provide the primary file name. The procedures that support the 'datapump' format type automatically discover and download the chunks.

  When you use DBMS_CLOUD.DELETE_OBJECT, the procedure automatically discovers and deletes the chunks when the procedure deletes the primary file.

• The DBMS_CLOUD.EXPORT_DATA procedure creates the dump file(s) from the file_uri_list values that you specify, as follows:

  • As more files are needed, the procedure creates additional files from the file_uri_list.

  • The procedure does not overwrite files. If a dump file in the file_uri_list exists, DBMS_CLOUD.EXPORT_DATA reports an error.

  • DBMS_CLOUD.EXPORT_DATA does not create buckets.

• The number of dump files that DBMS_CLOUD.EXPORT_DATA generates is determined when the procedure runs. The number of dump files that are generated depends on the number of file names you provide in the file_uri_list parameter, as well as on the number of Autonomous Database OCPUs available to the instance, the service level, and the size of the data.

  For example, if you use a 1 OCPU Autonomous Database instance or the low service, then a single dump file is exported with no parallelism, even if you provide multiple file names. If you use a 4 OCPU Autonomous Database instance with the medium or high service, then the jobs can run in parallel and multiple dump files are exported if you provide multiple file names.

• The dump files you create with DBMS_CLOUD.EXPORT_DATA cannot be imported using Oracle Data Pump impdp. Depending on the database, you can use these files as follows:

  • On an Autonomous Database instance on Shared Infrastructure, you can use the dump files with the DBMS_CLOUD procedures that support the format parameter type with the value 'datapump'. You can import the dump files using DBMS_CLOUD.COPY_DATA or you can call DBMS_CLOUD.CREATE_EXTERNAL_TABLE to create an external table.

  • On any other Oracle Database, such as Oracle Database 19c on-premise, you can import the dump files created with the procedure DBMS_CLOUD.EXPORT_DATA using the ORACLE_DATAPUMP access driver. See Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver for more information.

• The query parameter value that you supply can be an advanced query, if required, such as a query that includes joins or subqueries.

Examples

The following example shows DBMS_CLOUD.EXPORT_DATA with the format type parameter with the value datapump:

BEGIN  
   DBMS_CLOUD.EXPORT_DATA(
      credential_name =>'OBJ_STORE_CRED',
      file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/exp1.dmp,
      format => json_object('type' value 'datapump', 'compression' value 'basic', 'version' value 'latest'),
      query => 'SELECT warehouse_id, quantity FROM inventories'
     );
   END;
/  

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

The following example shows DBMS_CLOUD.EXPORT_DATA with the format type parameter with the value json:

BEGIN  
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/json_exportfile, 
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'json', 'compression' value 'gzip'));
     );
   END;
/  

The following example shows DBMS_CLOUD.EXPORT_DATA with the format type parameter with the value xml:

BEGIN  
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/json_exportfile, 
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'xml', 'compression' value 'gzip'));
     );
   END;
/

The following example shows DBMS_CLOUD.EXPORT_DATA with the format type parameter with the value csv:

BEGIN  
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/json_exportfile, 
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'csv', 'delimiter' value '|', 'compression' value 'gzip'));
     );
   END;
/  

GET_OBJECT Procedure and Function

This procedure is overloaded. The procedure form reads an object from Cloud Object Storage and copies it to Autonomous Database. The function form reads an object from Cloud Object Storage and returns a BLOB to Autonomous Database.

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


DBMS_CLOUD.GET_OBJECT(
       credential_name      IN VARCHAR2 DEFAULT NULL,
       object_uri           IN VARCHAR2,
       startoffset          IN NUMBER DEFAULT  0,
       endoffset            IN NUMBER DEFAULT  0,
       compression          IN VARCHAR2 DEFAULT  NULL)
RETURN BLOB;

Parameters

Parameter	Description
credential_name	The name of the credential to access the Cloud Object Storage. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 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 adb_user:

GRANT WRITE ON DIRECTORY data_pump_dir TO adb_user;

Return Values

The function form reads from Object Store and DBMS_CLOUD.GET_OBJECT returns a BLOB.

Examples

BEGIN 
   DBMS_CLOUD.GET_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file.txt',
     directory_name => 'DATA_PUMP_DIR'); 
END;
/

To read character data from a file in Object Store:

SELECT to_clob(
     DBMS_CLOUD.GET_OBJECT(
       credential_name => 'OBJ_STORE_CRED',
       object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file.txt'))
FROM DUAL;

To add an image stored on Object Store in a BLOB in the database:

DECLARE
   l_blob BLOB := NULL;
BEGIN
   l_blob := DBMS_CLOUD.GET_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/MyImage.gif' );
END;
/

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

LIST_FILES Function

This function lists the files in the specified directory. The results include the file names and additional metadata about the files such as file size in bytes, creation timestamp, and the last modification timestamp.

Syntax

DBMS_CLOUD.LIST_FILES (
	directory_name      IN VARCHAR2)
       RETURN TABLE;

Parameters

Parameter	Description
directory_name	The name of the directory on the database.

Usage Notes

• 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 adb_user:

  GRANT READ ON DIRECTORY data_pump_dir TO adb_user;

• This is a pipelined table function with return type as DBMS_CLOUD_TYPES.list_object_ret_t.

• DBMS_CLOUD.LIST_FILES does not obtain the checksum value and returns NULL for this field.

Example

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

OBJECT_NAME       BYTES   CHECKSUM      CREATED              LAST_MODIFIED
------------ ---------- ----------    ---------------------  ---------------------
cwallet.sso        2965               2018-12-12T18:10:47Z   2019-11-23T06:36:54Z

LIST_OBJECTS Function

This function lists objects in the specified location on object store. The results include the object names and additional metadata about the objects such as size, checksum, creation timestamp, and the last modification timestamp.

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. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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.

Usage Notes

• Depending on the capabilities of the object store, DBMS_CLOUD.LIST_OBJECTS does not return values for certain attributes and the return value for the field is NULL in this case.

  All supported Object Stores return values for the OBJECT_NAME, BYTES, and CHECKSUM fields.

  The following table shows support for the fields CREATED and LAST_MODIFIED by Object Store:

  Object Store	CREATED	LAST_MODIFIED
  Oracle Cloud Infrastructure Native	Returns timestamp	Returns timestamp
  Oracle Cloud Infrastructure Swift	Returns NULL	Returns timestamp
  Oracle Cloud Infrastructure Classic	Returns NULL	Returns timestamp
  Amazon S3	Returns NULL	Returns timestamp
  Amazon S3-Compatible	Returns NULL	Returns timestamp
  Azure	Returns timestamp	Returns timestamp
  GitHub Repository

• The checksum value is the MD5 checksum. This is a 32-character hexadecimal number that is computed on the object contents.

• This is a pipelined table function with return type as DBMS_CLOUD_TYPES.list_object_ret_t.

Example

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/namespace-string/b/bucketname/o/');


OBJECT_NAME   BYTES              CHECKSUM                       CREATED         LAST_MODIFIED
------------ ---------- -------------------------------- --------------------- --------------------
cwallet.sso   2965      2339a2731ba24a837b26d344d643dc07 2019-11-23T06:36:54Z          

In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

PUT_OBJECT Procedure

This procedure is overloaded. In one form the procedure copies a file from Autonomous Database to the Cloud Object Storage. In another form the procedure copies a BLOB from Autonomous Database to the Cloud Object Storage.

Syntax

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


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

Parameters

Parameter	Description
credential_name	The name of the credential to access the Cloud Object Storage. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
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 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 adb_user:

GRANT READ ON DIRECTORY data_pump_dir TO adb_user;

Example

To handle BLOB data after in-database processing and then store the data directly into a file in the object store:

DECLARE
      my_blob_data BLOB;
BEGIN 
 /* Some processing producing BLOB data and populating my_blob_data */
DBMS_CLOUD.PUT_OBJECT(
     credential_name => 'OBJ_STORE_CRED',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/my_new_file',
     contents => my_blob_data)); 
END;
/

Usage Notes

Depending on your Cloud Object Storage, the size of the object you transfer is limited as follows:

Cloud Object Storage Service	Object Transfer Size Limit
Oracle Cloud Infrastructure Object Storage	50 GB
Amazon S3	5 GB
Azure Blob Storage	256 MB
Amazon S3-Compatible	Set by the object store provider. For more information, refer to the provider's documentation.

Oracle Cloud Infrastructure object store does not allow writing files into a public bucket without supplying credentials (Oracle Cloud Infrastructure allows users to download objects from public buckets). Thus, you must supply a credential name with valid credentials to store an object in an Oracle Cloud Infrastructure public bucket using PUT_OBJECT.

See DBMS_CLOUD Package File URI Formats for more information.

SYNC_EXTERNAL_PART_TABLE Procedure

This procedure simplifies updating an external partitioned table from files in the Cloud. Run this procedure whenever new partitions are added or when partitions are removed from the Object Store source for the external partitioned table.

Syntax

DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE (
	table_name        IN VARCHAR2,
	schema_name       IN VARCHAR2 DEFAULT,
	update_columns    IN BOOLEAN DEFAULT);

Parameters

Parameter	Description
table_name	The name of the target table. The target table needs to be created before you run DBMS_CLOUD.SYNC_EXTERNAL_PART_TABLE.
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.
update_columns	The new files may introduce a change to the schema. Updates supported include: new columns, deleted columns. Updates to existing columns, for example a change in the data type throw errors. Default Value: False

VALIDATE_EXTERNAL_PART_TABLE Procedure

This procedure validates the source files for an external partitioned 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 Database. The overloaded form enables you to use the operation_id parameter.

Syntax

DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE (
       table_name                 IN VARCHAR2,
       partition_name             IN CLOB DEFAULT,
       schema_name                IN VARCHAR2 DEFAULT,
       rowcount                   IN NUMBER DEFAULT,
       partition_key_validation   IN BOOLEAN DEFAULT,
       stop_on_error              IN BOOLEAN DEFAULT);


DBMS_CLOUD.VALIDATE_EXTERNAL_PART_TABLE (
       table_name                 IN VARCHAR2,
       operation_id               OUT NUMBER,
       partition_name             IN CLOB DEFAULT,
       schema_name                IN VARCHAR2 DEFAULT,
       rowcount                   IN NUMBER DEFAULT,
       partition_key_validation   IN BOOLEAN DEFAULT,
       stop_on_error              IN BOOLEAN DEFAULT);

Parameters

Parameter	Description
table_name	The name of the external table.
operation_id	Use this parameter to track the progress and final status of the load operation as the corresponding ID in the USER_LOAD_OPERATIONS view.
partition_name	If defined, then only a specific partition is validated. If not specified then read all partitions sequentially until rowcount is reached.
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.
partition_key_validation	For internal use only. Do not use this parameter.
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.

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 Database. The overloaded form enables you to use the operation_id parameter.

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


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

Parameter	Description
table_name	The name of the external table.
operation_id	Use this parameter to track the progress and final status of the load operation as the corresponding ID in the USER_LOAD_OPERATIONS view.
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 Avro, ORC, or Parquet files then the validate stops at the first rejected row. When the external table specifies the format parameter type set to the value avro, orc, or parquet, the parameter stop_on_error effectively always has the value TRUE. Thus, the table badfile will always be empty for an external table referring to Avro, ORC, or Parquet files.

Usage Notes

• DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE works with both partitioned external tables and hybrid partitioned tables. This potentially reads data from all external partitions until rowcount is reached or stop_on_error applies. You do not have control over which partition, or parts of a partition, is read in which order.

VALIDATE_HYBRID_PART_TABLE Procedure

This procedure validates the source files for a hybrid partitioned table, generates log information, and stores the rows that do not match the format options specified for the hybrid table in a badfile table on Autonomous Database. The overloaded form enables you to use the operation_id parameter.

Syntax

DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE (
       table_name                 IN VARCHAR2,
       partition_name             IN CLOB DEFAULT,
       schema_name                IN VARCHAR2 DEFAULT,
       rowcount                   IN NUMBER DEFAULT,
       partition_key_validation   IN BOOLEAN DEFAULT,
       stop_on_error              IN BOOLEAN DEFAULT);


DBMS_CLOUD.VALIDATE_HYBRID_PART_TABLE (
       table_name                 IN VARCHAR2,
       operation_id               OUT NUMBER,
       partition_name             IN CLOB DEFAULT,
       schema_name                IN VARCHAR2 DEFAULT,
       rowcount                   IN NUMBER DEFAULT,
       partition_key_validation   IN BOOLEAN DEFAULT,
       stop_on_error              IN BOOLEAN DEFAULT);

Parameters

Parameter	Description
table_name	The name of the external table.
operation_id	Use this parameter to track the progress and final status of the load operation as the corresponding ID in the USER_LOAD_OPERATIONS view.
partition_name	If defined, then only a specific partition is validated. If not specified then read from all external partitions sequentially until rowcount is reached.
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.
partition_key_validation	For internal use only. Do not use this parameter.
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 REST APIs

This section covers the DBMS_CLOUD REST APIs provided with Autonomous Database.

REST API	Description
GET_RESPONSE_HEADERS Function	This function returns the HTTP response headers as JSON data in a JSON object in Autonomous Database.
GET_RESPONSE_RAW Function	This function returns the HTTP response in RAW format Autonomous Database. This is useful if the HTTP response is expected to be binary format.
GET_RESPONSE_STATUS_CODE Function	This function returns the HTTP response status code as an integer in Autonomous Database. The status code helps to identify if the request is successful.
GET_RESPONSE_TEXT Function	This function returns the HTTP response in TEXT format (VARCHAR2 or CLOB) in Autonomous Database. Usually, most Cloud REST APIs return JSON response in text format. This function is useful if you expect the the HTTP response is in text format.
GET_API_RESULT_CACHE_SIZE Function	This function returns the configured result cache size.
SEND_REQUEST Function and Procedure	This function begins an HTTP request, gets the response, and ends the response in Autonomous Database. This function provides a workflow for sending a Cloud REST API request with arguments and a return response code and payload.
SET_API_RESULT_CACHE_SIZE Procedure	This procedure sets the maximum cache size for current session.

Topics

• DBMS_CLOUD REST API Overview

• DBMS_CLOUD REST API Constants

• DBMS_CLOUD REST API Results Cache

• DBMS_CLOUD REST API Examples

DBMS_CLOUD REST API Overview

When you use PL/SQL in your application and you need to call Cloud REST APIs you can use DBMS_CLOUD.SEND_REQUEST to send the REST API requests.

The DBMS_CLOUD REST API functions allow you to make HTTP requests using DBMS_CLOUD.SEND_REQUEST and obtain and save results. These functions provide a generic API that lets you call any REST API with the following supported cloud services:

• Oracle Cloud Infrastructure

  See API Reference and Endpoints for information on Oracle Cloud Infrastructure REST APIs.

• Amazon Web Services (AWS)

  See Guides and API References for information on Amazon Web Services REST APIs.

• Azure Cloud ^Foot 3

  See Azure REST API Reference for information on Azure REST APIs.

• Oracle Cloud Infrastructure Classic

  See All REST Endpoints for information on Oracle Cloud Infrastructure Classic REST APIs.

• GitHub Repository

  See GitHub REST API for more information.

DBMS_CLOUD REST API Constants

Describes the DBMS_CLOUD constants for making HTTP requests using DBMS_CLOUD.SEND_REQUEST.

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Name	Type	Value
METHOD_DELETE	VARCHAR2(6)	'DELETE'
METHOD_GET	VARCHAR2(3)	'GET'
METHOD_HEAD	VARCHAR2(4)	'HEAD'
METHOD_POST	VARCHAR2(4)	'POST'
METHOD_PUT	VARCHAR2(3)	'PUT'

DBMS_CLOUD REST API Results Cache

You can save DBMS_CLOUD REST API results when you set the cache parameter to true with DBMS_CLOUD.SEND_REQUEST. The SESSION_CLOUD_API_RESULTS view describes the columns you can use when REST API results are saved.

By default DBMS_CLOUD REST API calls do not save results for your session. In this case you use the DBMS_CLOUD.SEND_REQUEST function to return results.

When you use DBMS_CLOUD.SEND_REQUEST and set the cache parameter to TRUE, results are saved and you can view past results in the SESSION_CLOUD_API_RESULTS view. Saving and querying historical results of DBMS_CLOUD REST API requests can help you when you need to work with your previous results in your applications.

For example, to query recent DBMS_CLOUD REST API results, use the view SESSION_CLOUD_API_RESULTS:

SELECT timestamp FROM SESSION_CLOUD_API_RESULTS;

When you save DBMS_CLOUD REST API results with DBMS_CLOUD.SEND_REQUEST the saved data is only available within the same session (connection). After the session exits, the saved data is no longer available.

Use DBMS_CLOUD.GET_API_RESULT_CACHE_SIZE and DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE to view and set the DBMS_CLOUD REST API cache size, and to disable caching.

DBMS_CLOUD REST API Results cache_scope Parameter

When you save DBMS_CLOUD REST API results with DBMS_CLOUD.SEND_REQUEST, access to the results in SESSION_CLOUD_API_RESULTS is provided based on the value of cache_scope.

By default cache_scope is 'PRIVATE' and only the current user of the session can access the results. If you set the cache_scope to 'PUBLIC', then all session users can access the results. The default value for cache_scope specifies that each user can only see DBMS_CLOUD.SEND_REQUEST REST API results generated by the procedures they invoke with invoker's rights. When you invoke DBMS_CLOUD.SEND_REQUEST in a session, there are three possibilities that determines if the current user can see results in the cache, based on the cache_scope value:

• You directly execute DBMS_CLOUD.SEND_REQUEST as a top-level statement and the call to DBMS_CLOUD.SEND_REQUEST and the REST API results are saved with the same username. In this case you have access to all results with the default value, 'PRIVATE', set for cache_scope.

• You write a wrapper invoker's rights procedure and as the current user your call with DBMS_CLOUD.SEND_REQUEST calls the procedure and the REST API results are saved with the same username. In this case, and you have access to all results with the default value, 'PRIVATE', set for cache_scope.

• You write a wrapper definer's rights procedure and the procedure is owned by another user. When you call DBMS_CLOUD.SEND_REQUEST inside the procedure, the results are saved with the username of the procedure owner.

  For this case, a different definer's rights user is invoking DBMS_CLOUD.SEND_REQUEST, and the REST API results are saved with that definers procedure's owner. For this case, by default when cache_scope is PRIVATE', the invoker's session cannot see the results.

  If the definer's procedure owner wants to make the results available to any invoking session user, then they must set cache_scope to 'PUBLIC' in the DBMS_CLOUD.SEND_REQUEST.

DBMS_CLOUD REST API SESSION_CLOUD_API_RESULTS View

You can save DBMS_CLOUD REST API results when you set the cache parameter to true with DBMS_CLOUD.SEND_REQUEST. The SESSION_CLOUD_API_RESULTS view describes the columns you can use when REST API results are saved.

The view SESSION_CLOUD_API_RESULTS is the view created if you cache results with DBMS_CLOUD.SEND_REQUEST. You can query historical results which belong to your user session. When the session ends, the data in the SESSION_CLOUD_API_RESULTS is purged.

Column	Description
URI	The DBMS_CLOUD REST API request URL
TIMESTAMP	The DBMS_CLOUD REST API response timestamp
CLOUD_TYPE	The DBMS_CLOUD REST API cloud type, such as Oracle Cloud Infrastructure, AMAZON_S3, and AZURE_BLOB
REQUEST_METHOD	The DBMS_CLOUD REST API request method, such as GET, PUT, HEAD
REQUEST_HEADERS	The DBMS_CLOUD REST API request headers
REQUEST_BODY_TEXT	The DBMS_CLOUD REST API request body in CLOB
RESPONSE_STATUS_CODE	The DBMS_CLOUD REST API response status code, such as 200(OK), 404(Not Found)
RESPONSE_HEADERS	The DBMS_CLOUD REST API response headers
RESPONSE_BODY_TEXT	The DBMS_CLOUD REST API response body in CLOB
SCOPE	The cache_scope set by DBMS_CLOUD.SEND_REQUEST. Valid values are PUBLIC or PRIVATE.

GET_RESPONSE_HEADERS Function

This function returns the HTTP response headers as JSON data in a JSON object.

Syntax

DBMS_CLOUD.GET_RESPONSE_HEADERS(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN JSON_OBJECT_T;

Parameters

Parameter	Description
resp	HTTP Response type returned from DBMS_CLOUD.SEND_REQUEST.

Exceptions

Exception	Error	Description
invalid_response	ORA-20025	Invalid response type object passed to DBMS_CLOUD.GET_RESPONSE_HEADERS.

GET_RESPONSE_RAW Function

This function returns the HTTP response in RAW format. This is useful if the HTTP response is expected to be binary format.

Syntax

DBMS_CLOUD.GET_RESPONSE_RAW(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN BLOB;

Parameters

Parameter	Description
resp	HTTP Response type returned from DBMS_CLOUD.SEND_REQUEST.

Exceptions

Exception	Error	Description
invalid_response	ORA-20025	Invalid response type object passed to DBMS_CLOUD.GET_RESPONSE_RAW.

GET_RESPONSE_STATUS_CODE Function

This function returns the HTTP response status code as an integer. The status code helps to identify if the request is successful.

Syntax

DBMS_CLOUD.GET_RESPONSE_STATUS_CODE(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN PLS_INTEGER;

Parameters

Parameter	Description
resp	HTTP Response type returned from DBMS_CLOUD.SEND_REQUEST.

Exceptions

Exception	Error	Description
invalid_response	ORA-20025	Invalid response type object passed to DBMS_CLOUD.GET_RESPONSE_STATUS_CODE.

GET_RESPONSE_TEXT Function

This function returns the HTTP response in TEXT format (VARCHAR2 or CLOB). Usually, most Cloud REST APIs return JSON response in text format. This function is useful if you expect the the HTTP response is in text format.

Syntax

DBMS_CLOUD.GET_RESPONSE_TEXT(
       resp          IN DBMS_CLOUD_TYPES.resp)
   RETURN CLOB;

Parameters

Parameter	Description
resp	HTTP Response type returned from DBMS_CLOUD.SEND_REQUEST.

Exceptions

Exception	Error	Description
invalid_response	ORA-20025	Invalid response type object passed to DBMS_CLOUD.GET_RESPONSE_TEXT.

GET_API_RESULT_CACHE_SIZE Function

This function returns the configured result cache size. The cache size value only applies for the current session.

Syntax

DBMS_CLOUD.GET_API_RESULT_CACHE_SIZE()
   RETURN NUMBER;

SEND_REQUEST Function and Procedure

This function and procedure begins an HTTP request, gets the response, and ends the response. This function provides a workflow for sending a cloud REST API request with arguments and the function returns a response code and payload. If you use the procedure, you can view results and response details from the saved results with the SESSION_CLOUD_API_RESULTS view.

Syntax

DBMS_CLOUD.SEND_REQUEST(
       credential_name    IN VARCHAR2,
       uri                IN VARCHAR2,
       method             IN VARCHAR2,
       headers            IN CLOB DEFAULT NULL,
       async_request_url  IN VARCHAR2 DEFAULT NULL,
       wait_for_states    IN DBMS_CLOUD_TYPES.wait_for_states_t DEFAULT NULL,
       timeout            IN NUMBER DEFAULT 0,
       cache              IN PL/SQL BOOLEAN DEFAULT FALSE,
       cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
       body               IN BLOB DEFAULT NULL)
   RETURN DBMS_CLOUD_TYPES.resp;

DBMS_CLOUD.SEND_REQUEST(
       credential_name    IN VARCHAR2,
       uri                IN VARCHAR2,
       method             IN VARCHAR2,
       headers            IN CLOB DEFAULT NULL,
       async_request_url  IN VARCHAR2 DEFAULT NULL,
       wait_for_states    IN DBMS_CLOUD_TYPES.wait_for_states_t DEFAULT NULL,
       timeout            IN NUMBER DEFAULT 0,
       cache              IN PL/SQL BOOLEAN DEFAULT FALSE,
       cache_scope        IN VARCHAR2 DEFAULT 'PRIVATE',
       body               IN BLOB DEFAULT NULL);

Parameters

Parameter	Description
credential_name	The name of the credential for authenticating with the corresponding cloud native API. You can use 'OCI$RESOURCE_PRINCIPAL' as the credential_name when resource principal is enabled. See ENABLE_RESOURCE_PRINCIPAL Procedure for more information.
uri	HTTP URI to make the request.
method	HTTP Request Method: GET, PUT, POST, HEAD, DELETE. Use the DBMS_CLOUD package constant to specify the method. See DBMS_CLOUD REST API Constants for more information.
headers	HTTP Request headers for the corresponding cloud native API in JSON format. The authentication headers are set automatically, only pass custom headers.
async_request_url	An asynchronous request URL. To obtain the URL select your request API from the list of APIs (see https://docs.cloud.oracle.com/en-us/iaas/api/). Then, navigate to find the API for your request in the left pane. For example, Database Services API → Autonomous Database → StopAutonomousDatabase. This page shows the API home (and shows the base endpoint). Then, append the base endpoint with the relative path obtained for your work request WorkRequest link.
wait_for_states	Wait for states is a status of type: DBMS_CLOUD_TYPES.wait_for_states_t. The following are valid values for expected states: 'ACTIVE', 'CANCELED', 'COMPLETED', 'DELETED', 'FAILED', 'SUCCEEDED'. Multiple states are allowed for wait_for_states. The default value for wait_for_states is to wait for any of the expected states: 'ACTIVE', 'CANCELED', 'COMPLETED', 'DELETED', 'FAILED', 'SUCCEEDED'.
timeout	Specifies the timeout, in seconds, for asynchronous requests with the parameters async_request_url and wait_for_states. Default value is 0. This indicates to wait for completion of the request without any timeout.
cache	If TRUE specifies the request should be cached in REST result API cache. The default value is FALSE, which means REST API requests are not cached.
cache_scope	Specifies whether everyone can have access to this request result cache. Valid values: "PRIVATE" and "PUBLIC". The default value is "PRIVATE".
body	HTTP Request Body for PUT and POST requests.

Exceptions

Exception	Error	Description
invalid_req_method	ORA-20023	Request method passed to DBMS_CLOUD.SEND_REQUEST is invalid.
invalid_req_header	ORA-20024	Request headers passed to DBMS_CLOUD.SEND_REQUEST are not in valid JSON format.

Usage Notes

• If you are using Oracle Cloud Infrastructure, you must use a Signing Key based credential value for the credential_name. See CREATE_CREDENTIAL Procedure for more information.

• The optional parameters async_request_url, wait_for_states, and timeout allow you to handle long running requests. Using this asynchronous form of send_request, the function waits for the completion status specified in wait_for_states before returning. With these parameters in the send request, you pass the expected return states in the wait_for_states parameter, and you use the async_request_url parameter to specify an associated work request, the request does not return immediately. Instead, the request probes the async_request_url until the return state is one of the expected states or the timeout is exceeded (timeout is optional). If no timeout is specified, the request waits until a state found in wait_for_states occurs.

SET_API_RESULT_CACHE_SIZE Procedure

This procedure sets the maximum cache size for current session. The cache size value only applies for the current session.

Syntax

DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(
       cache_size          IN NUMBER);

Parameters

Parameter	Description
cache_size	Set the maximum cache size to the specified value cache_size. If the new maximum cache size is smaller than the current cache size, older records are dropped until the number of rows is equal to the specified maximum cache size. The maximum value is 10000. If the cache size is set to 0, caching is disabled in the session. The default cache size is 10.

Exceptions

Exception	Error	Description
invalid API result cache size	ORA-20032	The minimum value is 0 and the maximum value is 10000. This exception is shown when the input value is less than 0 or is larger than 10000.

Example

EXEC DBMS_CLOUD.SET_API_RESULT_CACHE_SIZE(101);

DBMS_CLOUD REST API Examples

Shows examples using DBMS_CLOUD.SEND_REQUEST to create and delete an Oracle Cloud Infrastructure Object Storage bucket, and an example to list all compartments in the tenancy.

Note:

These examples show Oracle Cloud Infrastructure request APIs and require that you use a Signing Key based credential for the credential_name. Oracle Cloud Infrastructure Signing Key based credentials include the private_key and fingerprint arguments.

For example:

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => ‘OCI_KEY_CRED’,
       user_ocid       => ‘ocid1.user.oc1..aaaaaaaauq54mi7zdyfhw33ozkwuontjceel7fok5nq3bf2vwetkpqsoa’,
       tenancy_ocid    => ‘ocid1.tenancy.oc1..aabbbbbbaafcue47pqmrf4vigneebgbcmmoy5r7xvoypicjqqge32ewnrcyx2a’,
       private_key     => ‘MIIEogIBAAKCAQEAtUnxbmrekwgVac6FdWeRzoXvIpA9+0r1.....wtnNpESQQQ0QLGPD8NM//JEBg=’,
       fingerprint     => ‘f2:db:f9:18:a4:aa:fc:94:f4:f6:6c:39:96:16:aa:27’);
END;
/

See CREATE_CREDENTIAL Procedure for information on DBMS_CLOUD.CREATE_CREDENTIAL.

Create Bucket Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP POST method to create an object store bucket named bucketname.

See CreateBucket for details on the Oracle Cloud Infrastructure Object Storage Service API for this example.

SET SERVEROUTPUT ON
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
BEGIN
  -- Send request
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/',
            method => DBMS_CLOUD.METHOD_POST,
            body => UTL_RAW.cast_to_raw(
                        JSON_OBJECT('name' value 'bucketname',
                                    'compartmentId' value 'compartment_OCID'))
          );
 
  -- Response Body in TEXT format
  dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));
  
  -- Response Headers in JSON format
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
 
  -- Response Status Code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));
 
END;
/

Notes:

• In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

• Where: region is an endpoint region. See Object Storage API reference in API Reference and Endpoints for more information. For example, where region is: us-phoenix-1.

Delete Bucket Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP DELETE method to delete an object store bucket named bucketname.

See DeleteBucket for details on the Oracle Cloud Infrastructure Object Storage Service API for this example.

SET SERVEROUTPUT ON
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
BEGIN
  -- Send request
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://objectstorage.region.oraclecloud.com/n/namespace-string/b/bucketname',
            method => DBMS_CLOUD.METHOD_DELETE
          );
 
  -- Response Body in TEXT format
  dbms_output.put_line('Body: ' || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_text(resp) || CHR(10));
  
  -- Response Headers in JSON format
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
 
  -- Response Status Code
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) ||
  DBMS_CLOUD.get_response_status_code(resp));
 
END;
/

Notes:

• In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

• Where: region is an endpoint region. See Object Storage API reference in API Reference and Endpoints for more information. For example, where region is: us-phoenix-1.

List Compartments Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP GET method to list all compartments in the tenancy (root compartment). This example shows how to pass request headers in the DBMS_CLOUD.SEND_REQUEST.

See ListCompartments for details on the Oracle Cloud Infrastructure Identity and Access Management Service API for this example.

--
-- List compartments
--
DECLARE
  resp DBMS_CLOUD_TYPES.resp;
  root_compartment_ocid VARCHAR2(512) := '&1';
BEGIN
  -- Send request
  dbms_output.put_line('Send Request');
  resp := DBMS_CLOUD.send_request(
            credential_name => 'OCI_KEY_CRED',
            uri => 'https://identity.region.oraclecloud.com/20160918/compartments?compartmentId=' || root_compartment_ocid,
            method => DBMS_CLOUD.METHOD_GET,
            headers => JSON_OBJECT('opc-request-id' value 'list-compartments')
          );
  dbms_output.put_line('Body: ' || '------------' || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10));
  dbms_output.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10));
  dbms_output.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_status_code(resp));
  dbms_output.put_line(CHR(10));
END;
/

Where: region is an endpoint region. See Identity and Access Management (IAM) API reference in API Reference and Endpoints for more information. For example, where region is: uk-london-1.

Asynchronous Request Example

Shows an example using DBMS_CLOUD.SEND_REQUEST with HTTP POST method to perform the Autonomous Database stop operation and wait for status. This example shows how to use DBMS_CLOUD.SEND_REQUEST with the async_request_url, wait_for_states, and timeout parameters.

--
-- Sent Work Request Autonomous Database Stop Request with Wait for Status
DECLARE
    l_resp DBMS_CLOUD_TYPES.resp;
    l_resp_json JSON_OBJECT_T;
    l_key_shape JSON_OBJECT_T;
    l_body JSON_OBJECT_T;
    status_array DBMS_CLOUD_TYPES.wait_for_states_t;
BEGIN
  status_array := DBMS_CLOUD_TYPES.wait_for_states_t('SUCCEEDED');
  l_body := JSON_OBJECT_T('{}');
  l_body.put('autonomousDatabaseId', 'ocid');
-- Send request
  dbms_output.put_line(l_body.to_clob);
  dbms_output.put_line('Send Request');
  l_resp := DBMS_CLOUD.send_request(
                       credential_name    => 'NATIVE_CRED_OCI',
                       uri                => 'https://database.region.oraclecloud.com/20160918/autonomousDatabases/ocid/actions/stop',
                       method             => DBMS_CLOUD.METHOD_POST,
                       body               => UTL_RAW.cast_to_raw(l_body.to_clob),
                       async_request_url  => 'https://iaas.region.oraclecloud.com/20160918/workRequests',
                       wait_for_states    => status_array,
                       timeout            => 600
                  );
   dbms_output.put_line('resp body: '||DBMS_CLOUD.get_response_text(l_resp));
   dbms_output.put_line('resp headers: '||DBMS_CLOUD.get_response_headers(l_resp).to_clob);
END;
/

Where: region is an endpoint region. See Identity and Access Management (IAM) API reference in API Reference and Endpoints for more information. For example, where region is: uk-london-1.

The ocid is the Oracle Cloud Infrastructure resource identifier. See Resource Identifiers for more information.

---

Footnote Legend

Footnote 3: Support for Azure Cloud REST API calls is limited to the domain "blob.windows.net".