DBMS_CLOUD for Bulk File Management

The subprograms for bulk file operations within the DBMS_CLOUD package.

Note:

DBMS_CLOUD package subprograms for bulk file management can only be used with Autonomous Database versions 19.22 or later.

Subprogram	Description
BULK_COPY Procedure	This procedure copies files from one Cloud Object Storage bucket to another.
BULK_DELETE Procedure	The procedure deletes files from Cloud Object Storage bucket or folder.
BULK_DOWNLOAD Procedure	This procedure downloads files from Cloud Object store bucket to a directory in Autonomous Database.
BULK_MOVE Procedure	This procedure moves files from one Cloud Object Storage bucket to another.
BULK_UPLOAD Procedure	This procedure uploads files from a directory in Autonomous Database to the Cloud Object Storage.

Related Topics

Bulk Operations for Files in the Cloud

Prerequisites

As a developer, you can use DBMS_CLOUD procedures with Autonomous Databases deployed on Oracle Public Cloud or Exadata Cloud@Customer.

Depending on the deployment choice, the following prerequisites must be met to use the DBMS_CLOUD procedures with Amazon S3, Azure Blob Storage, and Google Cloud Storage service providers.

Oracle Public Cloud
Exadata Cloud@Customer

An outbound connectivity must have been configured using a NAT gateway, by your fleet administrator as described below:

Create a NAT gateway in the Virtual Cloud Network (VCN) where your Autonomous Database resources reside by following the instructions in Create a NAT Gateway in Oracle Cloud Infrastructure Documentation.
After creating the NAT gateway, add a route rule and an egress security rule to each subnet (in the VCN) where Autonomous Database resources reside so that these resources can use the gateway to obtain a public key from your Azure AD instance:
1. Go to the Subnet Details page for the subnet.
2. In the Subnet Information tab, click the name of the subnet's Route Table to display its Route Table Details page.
3. In the table of existing Route Rules, check whether there is already a rule with the following characteristics:
  - Destination: 0.0.0.0/0
  - Target Type: NAT Gateway
  - Target: The name of the NAT gateway you just created in the VCN
  If such a rule does not exist, click Add Route Rules and add a route rule with these characteristics.
4. Return to the Subnet Details page for the subnet.
5. In the subnet's Security Lists table, click the name of the subnet's security list to display its Security List Details page.
6. In the side menu, under Resources, click Egress Rules.
7. In the table of existing Egress Rules, check whether there is already a rule with the following characteristics:
  - Destination Type: CIDR
  - Destination: 0.0.0.0/0
  - IP Protocol: TCP
  - Source Port Range: 443
  - Destination Port Range: All
  If such a rule does not exist, click Add Egress Rules and add an egress rule with these characteristics.

The HTTP Proxy settings in your environment must allow the database to access the cloud service provider.

These settings are defined by your fleet administrator while creating the Exadata Cloud@Customer infrastructure as described in Using the Console to Provision Exadata Database Service on Cloud@Customer .

Note:

The network configuration including the HTTP Proxy can only be edited until the Exadata Infrastructure is in Requires Activation state. Once it is activated, you cannot edit those settings.

Setting up an HTTP Proxy for an already provisioned Exadata Infrastructure needs a Service Request (SR) in My Oracle Support. See Create a Service Request in My Oracle Support for details.

BULK_COPY Procedure

This procedure bulk copies files from one Cloud Object Storage bucket to another. The overloaded form enables you to use the operation_id parameter.

You can filter the list of files to be deleted using a regular expression pattern compatible with REGEXP_LIKE operator.

The source and target bucket or folder can be in the same or different Cloud Object store provider.

When the source and target are in distinct Object Stores or have different accounts with the same cloud provider, you can give separate credential names for the source and target locations.

The source credential name is by default also used by the target location.

Syntax

DBMS_CLOUD.BULK_COPY (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_COPY (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL,
      operation_id            OUT NUMBER
);

Parameters

Parameter	Description
`source_credential_name`	The name of the credential to access the Cloud Object Storage. If you do not supply a `source_credential_name` value, the `credential_name` is set to `NULL`.
`source_location_uri`	Specifies URI, that point to the source Object Storage bucket or folder location. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`target_location_uri`	Specifies the URI for the target Object Storage bucket or folder, where the files need to be copied. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`target_credential_name`	The name of the credential to access the target Cloud Object Storage location. If you do not supply a `target_credential_name` value, the `target_location_uri` is set to the `source_credential_name` value.
`regex_filter`	Specifies the REGEX expression to filter files. The REGEX expression pattern must be compatible with the `REGEXP_LIKE` operator. If you do not supply a `regex_filter` value, the `regex_filter` is set to `NULL`. See REGEXP_LIKE Condition for more information.
`format`	Specifies the additional configuration options for the file operation. These options are specified as a JSON string. The supported format options are: `logretention`: It accepts an integer value that determines the duration in days for which the status table is retained for a bulk operation. The default value is 2 days. `logprefix`: It accepts a string value that determines the bulk operation status table name prefix string. The operation type is the default value. For `BULK_COPY`, the default `logprefix` value is `COPYOBJ`. `priority`: It accepts a string value that determines the number of file operations performed concurrently. An operation with a higher priority consumes more database resources and should run faster. It accepts the following values: `HIGH`: Determines the number of parallel files handled using the database's ECPU count (OCPU count if your database uses OCPUs) `MEDIUM`: Determines the number of simultaneous processes using the concurrency limit for Medium service. The default value is 4. `LOW`: Process the files in serial order. The default value is `MEDIUM`. The maximum number of concurrent file operations is limited to 64. If you do not supply a `format` value, the `format` is set to `NULL`.
`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 Notes

An error is returned when the source and target URI point to the same Object Storage bucket or folder.

Example

BEGIN 
DBMS_CLOUD.BULK_COPY (
     source_credential_name => 'OCI_CRED',
     source_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/o',
     target_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/o',
     format       => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKOP')
);
END;
/

BULK_DELETE Procedure

This procedure bulk deletes files from the Cloud Object Storage. The overloaded form enables you to use the operation_id parameter. You can filter the list of files to be deleted using a regular expression pattern compatible with REGEXP_LIKE operator.

Syntax

 DBMS_CLOUD.BULK_DELETE(
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_DELETE (
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL,
     operation_id     OUT NUMBER
);

Parameters

Parameter	Description
`credential_name`	The name of the credential to access the Cloud Object Storage. If you do not supply a `credential_name` value, the `credential_name` is set to `NULL`.
`location_uri`	Specifies URI, that point to an Object Storage location in the Autonomous Database. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`regex_filter`	Specifies the REGEX expression to filter files. The REGEX expression pattern must be compatible with the `REGEXP_LIKE` operator. If you do not supply a `regex_filter` value, the `regex_filter` is set to `NULL`. See REGEXP_LIKE Condition for more information.
`format`	Specifies the additional configuration options for the file operation. These options are specified as a JSON string. The supported format options are: `logretention`: It accepts an integer value that determines the duration in days for which the status table is retained for a bulk operation . The default value is 2 days. `logprefix`: It accepts a string value that determines the bulk operation status table name prefix string. The operation type is the default value. For `BULK_DELETE`, the default `logprefix` value is `DELETE`. `priority`: It accepts a string value that determines the number of file operations performed concurrently. An operation with a higher priority consumes more database resources and is completed sooner. It accepts the following values: `HIGH`: Determines the number of parallel files handled using the database's ECPU count (OCPU count if your database uses OCPUs). `MEDIUM`: Determines the number of simultaneous processes using the concurrency limit for Medium service. The default value is 4. `LOW`: Process the files in serial order. The default value is `MEDIUM`. The maximum number of concurrent file operations is limited to 64. If you do not supply a `format` value, the `format` is set to `NULL`.
`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.BULK_DELETE (    
     credential_name => 'OCI_CRED',
     location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
     format          => JSON_OBJECT ('logretention' value 5, 'logprefix' value 'BULKDEL')
);
END;
/

BULK_DOWNLOAD Procedure

This procedure downloads files into an Autonomous Database directory from Cloud Object Storage. The overloaded form enables you to use the operation_id parameter. You can filter the list of files to be downloaded using a regular expression pattern compatible with REGEXP_LIKE operator.

Syntax

DBMS_CLOUD.BULK_DOWNLOAD (
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     directory_name   IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_DOWNLOAD (
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     directory_name   IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL,
     operation_id     OUT NUMBER
);

Parameters

Parameter	Description
`credential_name`	The name of the credential to access the Cloud Object Storage. If you do not supply a `credential_name` value, the `credential_name` is set to `NULL`.
`location_uri`	Specifies URI, that point to an Object Storage location in the Autonomous Database. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`directory_name`	The name of the directory on the Autonomous Database from where you want to download the files. This parameter is mandatory.
`regex_filter`	Specifies the REGEX expression to filter files. The REGEX expression pattern must be compatible with the `REGEXP_LIKE` operator. If you do not supply a `regex_filter` value, the `regex_filter` is set to `NULL`. See REGEXP_LIKE Condition for more information.
`format`	Specifies the additional configuration options for the file operation. These options are specified as a JSON string. The supported format options are: `logretention`: It accepts an integer value that determines the duration in days for which the status table is retained for a bulk operation. The default value is 2 days. `logprefix`: It accepts a string value that determines the bulk operation status table name prefix string. For `BULK_DOWNLOAD`, the default `logprefix` value is `DOWNLOAD`. The operation type is the default value. `priority`: It accepts a string value that determines the number of file operations performed concurrently. An operation with a higher priority consumes more database resources and is completed sooner. It accepts the following values: `HIGH`: Determines the number of parallel files handled using the database's ECPU count (OCPU count if your database uses OCPUs). `MEDIUM`: Determines the number of simultaneous processes using the concurrency limit for Medium service. The default value is 4. `LOW`: Process the files in serial order. The default value is `MEDIUM`. The maximum number of concurrent file operations is limited to 64. If you do not supply a `format` value, the `format` is set to `NULL`.
`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.BULK_DOWNLOAD (    
     credential_name => 'OCI_CRED',
     location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
     directory_name  => 'BULK_TEST',
     format          => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKOP')
 );
END;
/

BULK_MOVE Procedure

This procedure bulk moves files from one Cloud Object Storage bucket or folder to another. The overloaded form enables you to use the operation_id parameter.

You can filter the list of files to be deleted using a regular expression pattern compatible with REGEXP_LIKE operator.

The source and target bucket or folder can be in the same or different Cloud Object store provider.

When the source and target are in distinct Object Stores or have different accounts with the same cloud provider, you can give separate credential names for the source and target locations.

The source credential name is by default also used by the target location when target credential name is not provided.

The first step in moving files is copying them to the target location, then deleting the source files, once they are successfully copied.

The object is renamed rather than moved if Object Store allows renaming operations between source and target locations.

Syntax

DBMS_CLOUD.BULK_MOVE (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_MOVE (
      source_credential_name  IN  VARCHAR2 DEFAULT NULL,
      source_location_uri     IN  VARCHAR2,
      target_location_uri     IN  VARCHAR2,
      target_credential_name  IN  VARCHAR2 DEFAULT NULL,
      regex_filter            IN  VARCHAR2 DEFAULT NULL,
      format                  IN  CLOB     DEFAULT NULL,
      operation_id            OUT NUMBER
);

Parameters

Parameter	Description
`source_credential_name`	The name of the credential to access the source Cloud Object Storage. If you do not supply a `source_credential_name` value, the `credential_name` is set to `NULL`.
`source_location_uri`	Specifies URI, that point to the source Object Storage bucket or folder location. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`target_location_uri`	Specifies the URI for the target Object Storage bucket or folder, where the files need to be moved. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`target_credential_name`	The name of the credential to access the target Cloud Object Storage location. If you do not supply a `target_credential_name` value, the `target_location_uri` is set to the `source_credential_name` value.
`regex_filter`	Specifies the REGEX expression to filter files. The REGEX expression pattern must be compatible with the `REGEXP_LIKE` operator. If you do not supply a `regex_filter` value, the `regex_filter` is set to `NULL`. See REGEXP_LIKE Condition for more information.
`format`	Specifies the additional configuration options for the file operation. These options are specified as a JSON string. The supported format options are: `logretention`: It accepts an integer value that determines the duration in days for which the status table is retained for a bulk operation. The default value is 2 days. `logprefix`: It accepts a string value that determines the bulk operation status table name prefix string. The operation type is the default value. For `BULK_MOVE`, the default `logprefix` value is `MOVE`. `priority`: It accepts a string value that determines the number of file operations performed concurrently. An operation with a higher priority consumes more database resources and is completed sooner. It accepts the following values: `HIGH`: Determines the number of parallel files handled using the database's ECPU count (OCPU count if your database uses OCPUs). `MEDIUM`: Determines the number of simultaneous processes using the concurrency limit for Medium service. The default value is 4. `LOW`: Process the files in serial order. The default value is `MEDIUM`. The maximum number of concurrent file operations is limited to 64. If you do not supply a `format` value, the `format` is set to `NULL`.
`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.BULK_MOVE (    
     source_credential_name => 'OCI_CRED',
     source_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/o',
     target_location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/o',
     format                 => JSON_OBJECT ('logretention' value 7, 'logprefix' value 'BULKMOVE')
);
END;
/

Note:

An error is returned when the source and target URI point to the same Object Storage bucket or folder.

BULK_UPLOAD Procedure

This procedure copies files into Cloud Object Storage from an Autonomous Database directory. The overloaded form enables you to use the operation_id parameter.

Syntax

DBMS_CLOUD.BULK_UPLOAD (
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     directory_name   IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL
);

DBMS_CLOUD.BULK_UPLOAD (
     credential_name  IN  VARCHAR2 DEFAULT NULL,
     location_uri     IN  VARCHAR2,
     directory_name   IN  VARCHAR2,
     regex_filter     IN  VARCHAR2 DEFAULT NULL,
     format           IN  CLOB     DEFAULT NULL,
     operation_id     OUT NUMBER
);

Parameters

Parameter	Description
`credential_name`	The name of the credential to access the Cloud Object Storage. If you do not supply a `credential_name` value, the `credential_name` is set to `NULL`.
`location_uri`	Specifies URI, that points to an Object Storage location to upload files. This parameter is mandatory. The format of the URIs depends on the Cloud Object Storage service. See DBMS_CLOUD Package File Cloud Object Storage URI Formats for more information.
`directory_name`	The name of the directory on the Autonomous Database from where you upload files. This parameter is mandatory.
`regex_filter`	Specifies the REGEX expression to filter files. The REGEX expression pattern must be compatible with `REGEXP_LIKE` operator. If you do not supply a `regex_filter` value, the `regex_filter` is set to `NULL`. See REGEXP_LIKE Condition for more information.
`format`	Specifies the additional configuration options for the file operation. These options are specified as a JSON string. The supported format options are: `logretention`: It accepts an integer value that determines the duration in days for which the status table is retained for a bulk operation. The default value is 2 days. `logprefix`: It accepts a string value that determines the bulk operation status table name prefix string. The operation type is the default value. For `BULK_UPLOAD`, the default `logprefix` value is `UPLOAD`. `priority`: It accepts a string value that determines the number of file operations performed concurrently. An operation with a higher priority consumes more database resources and is completed sooner. It accepts the following values: `HIGH`: Determines the number of parallel files handled using the database's ECPU count (OCPU count if your database uses OCPUs). `MEDIUM`: Determines the number of simultaneous processes using the concurrency limit for Medium service. The default value is 4. `LOW`: Process the files in serial order. The default value is `MEDIUM`. The maximum number of concurrent file operations is limited to 64. If you do not supply a `format` value, the `format` is set to `NULL`.
`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.BULK_UPLOAD ( 
     credential_name => 'OCI_CRED',
     location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o',
     directory_name  => 'BULK_TEST',
     format          => JSON_OBJECT ('logretention' value 5, 'logprefix' value 'BULKUPLOAD')
 );
END;
/

Title and Copyright Information

DBMS_CLOUD for Bulk File Management

Oracle and/or its affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.