Oracle Cloud Infrastructure Documentation

DBMS_CLOUD for Objects and Files

This section covers the DBMS_CLOUD subprograms used to work with objects and files.

  • Prerequisites
  • DBMS_CLOUD Subprograms for Objects and Files
    The subprograms for object and file management within the DBMS_CLOUD package.
  • COPY_COLLECTION Procedure
    This procedure loads data into a SODA collection from Cloud Object Storage or from a directory. If the specified SODA collection does not exist, the procedure creates it. The overloaded form enables you to use the operation_id parameter.
  • COPY_DATA Procedure
    This procedure loads data into existing Autonomous Database tables from files in the Cloud, or from files in a directory. The overloaded form enables you to use the operation_id parameter.
  • 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 or from files in a directory.
  • COPY_OBJECT Procedure
    This procedure copies an object from one Cloud Object Storage bucket or folder to another.
  • CREATE_EXTERNAL_PART_TABLE Procedure
    This procedure creates an external partitioned table on files in the Cloud, or from files in a directory. This allows you to run queries on external data from Autonomous Database.
  • CREATE_EXTERNAL_TABLE Procedure
    This procedure creates an external table on files in the Cloud or from files in a directory. 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 avro, orc, or parquet creates an external table with either Avro, ORC, or Parquet format files in the Cloud or in a directory.
  • CREATE_EXTERNAL_TEXT_INDEX Procedure
    This procedure creates a text index on Object Storage files.
  • 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 using database objects and files in the Cloud, or database objects and files in a directory.
  • 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.
  • DELETE_OPERATION Procedure
    This procedure clears the data load entries for the specified operation ID logged in the user_load_operations or dba_load_operations tables in your schema.
  • DROP_EXTERNAL_TEXT_INDEX Procedure
    This procedure drops text index on the Object Storage files.
  • EXPORT_DATA Procedure
    This procedure exports data from Autonomous Database based on the result of a query. This procedure is overloaded and supports writing files to the cloud or to a directory.
  • 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.
  • MOVE_OBJECT Procedure
    This procedure moves an object from one Cloud Object Storage bucket or folder to another.
  • 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_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.
  • 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.
  • 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.

Parent topic: DBMS_CLOUD Package Reference

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.

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.

DBMS_CLOUD Subprograms 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 either from Cloud Object Storage or from files in a directory.

COPY_DATA Procedure

 This procedure loads data into existing Autonomous Database tables either from Cloud Object Storage or from files in a directory.

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 or from ORC, Parquet, or Avro files in a directory.

Similar to text files, the data is copied from the source ORC, Parquet, or Avro file into the preexisting internal table.

COPY_OBJECT Procedure

 This procedure copies files from one Cloud Object Storage bucket to another.

CREATE_EXTERNAL_TABLE Procedure

 This procedure creates an external table on files in the Cloud or on files in a directory. 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 or in a directory.

This allows you to run queries on external data from Autonomous Database.

CREATE_EXTERNAL_TEXT_INDEX Procedure

This procedure creates text index on the object store files.

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.

DELETE_OPERATION Procedure

This procedure accepts an operation_id as input and deletes the logs associated with the specified operation_id.

DROP_EXTERNAL_TEXT_INDEX Procedure

This procedure drops text index on the object store files.

EXPORT_DATA Procedure

 This procedure exports data from Autonomous Database to files in the Cloud based on the result of a query. The overloaded form enables you to use the operation_id parameter. Depending on the format parameter type option specified, the procedure exports rows to the Cloud Object store as text with options of CSV, JSON, Parquet, or XML

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.

MOVE_OBJECT Procedure

 This procedure moves an object from one Cloud Object Storage bucket to another one.

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 or from a directory. 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.

This parameter is not used when you specify a directory with file_uri_list.

file_uri_list

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Cloud source file URIs

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'

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

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

Directory

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

You can use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

format

The options describing the format of the source files. These options are specified as a JSON string.

Supported formats are: characterset, compression, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, unpackarray, keyassignment, and keypath.

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

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        => 'user_name@oracle.com',
            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, or from files in a directory. The overloaded form enables you to use the operation_id parameter.

Syntax

DBMS_CLOUD.COPY_DATA (
    table_name        IN VARCHAR2,
    credential_name   IN VARCHAR2 DEFAULT NULL,
    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.

This parameter is not used when you specify a directory with file_uri_list.

file_uri_list

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Cloud source file URIs

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'

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

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

Directory

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

You can use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

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, log, and bad files. For the list of the options and how to specify the values see Format Parameter.

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 Format Parameter for information on the recorddelmiter format option.

Examples

BEGIN
    DBMS_CLOUD.CREATE_CREDENTIAL(
            credential_name => 'DEF_CRED_NAME',
            username        => 'user_name@oracle.com',
            password        => 'password'
            );
END;
/
BEGIN
 DBMS_CLOUD.COPY_DATA(
    table_name =>'CHANNELS',
    credential_name =>'DEF_CRED_NAME',
    file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/channels.txt',
    format => json_object('delimiter' value ',')
 );
END;
/

BEGIN
    DBMS_CLOUD.COPY_DATA(
            table_name      => 'ORDERS',
            schema_name     => 'TEST_SCHEMA',
            credential_name => 'DEF_CRED_NAME',
	     file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/adbexample/b/json/o/orde[r]s.tbl.1'
            format          =>  json_object('ignoreblanklines' value TRUE,
                                            'rejectlimit' value '0',
                                            'dateformat' value 'yyyy-mm-dd',
                                            'regexuri' value TRUE)
            );
END;
/

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 or from files in a directory.

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 DEFAULT NULL,
    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.

This parameter is not used when you specify a directory with file_uri_list.

file_uri_list

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Cloud source file URIs

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'

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

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

Directory

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

You can use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

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.

COPY_OBJECT Procedure

This procedure copies an object from one Cloud Object Storage bucket or folder to another.

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.

Syntax

DBMS_CLOUD.COPY_OBJECT (
    source_credential_name  IN  VARCHAR2 DEFAULT NULL,
    source_object_uri       IN  VARCHAR2,
    target_object_uri       IN  VARCHAR2,
    target_credential_name  IN  VARCHAR2 DEFAULT NULL
);

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_object_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 Cloud Object Storage URI Formats for more information.

target_object_uri

Specifies the URI for the target Object Store.

This parameter is mandatory.

The format of the URIs depends on the Cloud Object Storage service. See 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_object_uri is set to the source_credential_name value.

Example

BEGIN 
DBMS_CLOUD.COPY_OBJECT (
    source_credential_name => 'OCI_CRED',
    source_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/bgfile.csv',
    target_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/myfile.csv'
);
END;
/

CREATE_EXTERNAL_PART_TABLE Procedure

This procedure creates an external partitioned table on files in the Cloud, or from files in a directory. 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.

partitioning_clause

Specifies the complete partitioning clause, including the location information for individual partitions.

If you use the partitioning_clause parameter, the file_uri_list parameter is not allowed.

file_uri_list

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Cloud source file URIs

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

This option is only supported with external tables that are created on a file in the Object Storage.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'

If you use the parameter file_uri_list, the partitioning_clause parameter is not allowed.

The format of the URIs depends on the Cloud Object Storage service. See Cloud Object Storage URI Formats for more information.

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

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_uri_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 CSV 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_uri_list parameter.

To see all the format parameter options describing the format of the source files, see Cloud Object Storage URI Formats.

Usage Notes

  • You cannot call this procedure with both partitioning_clause and file_uri_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

    See Cloud Object Storage URI Formats for more information.

  • The procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE supports external partitioned files in directories, either in a local file system or in a network file system.

  • When you call DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE with the file_uri_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 Cloud Object Storage URI Formats 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;
/  


BEGIN
    DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
       table_name          => 'PET',
       format              => json_object('delimiter'value ','),
       column_list         => 'name varchar2(20), gender varchar2(10), salary number',
       partitioning_clause => 'partition by range (salary)
              (   -- Use test1.csv in the DEFAULT DIRECTORY DATA_PUMP_DIR 
                  partition p1 values less than (100) LOCATION (''test1.csv''),
                   -- Use test2.csv in a specified directory MY_DIR
                  partition p2 values less than (300) DEFAULT DIRECTORY MY_DIR LOCATION (''test2.csv'')        )'   );                       
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 or from files in a directory. 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.

This parameter is not used when you specify a directory with file_uri_list.

file_uri_list

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Cloud source file URIs

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

This option is only supported with external tables that are created on a file in the Object Storage.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.csv'

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

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

Directory

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

You can use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

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 Database external tables. For more information about field_list see ORACLE_LOADER Access Driver field_list under field_definitions Clause in 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 Format Parameter.

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 Azure Blob Storage.

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

    See Cloud Object Storage 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 Format Parameter for information on the recorddelimiter 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 or in a directory.

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 DEFAULT NULL,
    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.

This parameter is not used when you specify a directory with file_uri_list.

file_uri_list

This parameter specifies either a comma-delimited list of source file URIs or one or more directories and source files.

Cloud source file URIs

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function.

This option is only supported with external tables that are created on a file in the Object Storage.

For example:

file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.parquet'

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

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

Directory

You can specify one directory and one or more file names or use a comma separated list of directories and file names. The format to specify a directory is:'MY_DIR:filename.ext'. By default the directory name MY_DIR is a database object and is case-insensitive. The file name is case sensitive.

You can use wildcards to specify file names in a directory. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character. For example:'MY_DIR:*" or 'MY_DIR:test?'

To specify multiple directories, use a comma separated list of directories: For example:'MY_DIR1:*, MY_DIR2:test?'

Use double quotes to specify a case-sensitive directory name. For example:'"my_dir1":*, "my_dir2":Test?'

To include a quote character, use two quotes. For example:'MY_DIR:''filename.ext'. This specifies the filename starts with a quote (').

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 type source files, 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_EXTERNAL_TEXT_INDEX Procedure

This procedure creates a text index on Object Storage files.

The CREATE_EXTERNAL_TEXT_INDEX procedure creates text index on the Object Storage files specified at the location_uri location. The index is refreshed at regular intervals, for any new additions or deletions done with files on location URI.

Syntax

DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
      credential_name  IN  VARCHAR2 DEFAULT NULL,
      location_uri     IN  VARCHAR2,
      index_name       IN  VARCHAR2,
      format           IN  CLOB     DEFAULT NULL
);

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage location. For public, pre-authenticated, or pre-signed bucket URIs, a NULL can be specified.

If you do not supply a credential_name value, the credential_name is set to a NULL value.

location_uri

Specifies the Object Store bucket or folder URI.

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.

index_name Specifies the name of the index you are building on the files located at the location_uri location.

This parameter is mandatory.

format

Specifies additional configuration options. Options are specified as a JSON string.

The supported format options are:

refresh_rate: Specifies the frequency in minutes at which the local index is refreshed. New file uploads and deletions result in an index refresh. The default value is 5 minutes.

binary_files: Specifies if the contents of the files to be indexed are binary. For example, PDF, MS-Word, The default value is FALSE.

stop_words: Specifies a list of stop words can be supplied when you create indexes.

The stop_words value indicates if it is a list of stop words or a table of stop words. When a JSON array is provided the stop words parameter it is treated as a list, otherwise the stop words parameter is treated as a table name whose column "STOP_WORDS" is used to read in the list of stop words.

You can specify stop words using the following methods:

  • JSON Array: For example: format := '{"stop_words":["king","queen"]}'
  • Stop word table name: For example: format := '{"stop_words":"STOP_WORDS_TABLE"}'

If you do not supply a format parameter, the format is set to a NULL value.

Example

BEGIN 
DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
        credential_name => 'DEFAULT_CREDENTIAL',
        location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/ts_data/'
        index_name      => 'EMP',
        format          => JSON_OBJECT ('refresh_rate' value 10)
);
END;
/

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 using database objects and files in the Cloud, or database objects and files in a directory.

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.

partitioning_clause

Specifies the complete partitioning clause, including the location information for individual partitions.

To use directories, the partitioning clause supports the LOCATION and DEFAULT DIRECTORY values.

You can use wildcards as well as regular expressions in the file names in Cloud source file URIs.

Regular expressions can only be used when the regexuri format parameter is set to TRUE.

The characters "*" and "?" are considered wildcard characters when the regexuri parameter is set to FALSE. When the regexuri parameter is set to TRUE the characters "*" and "?" are part of the specified regular expression pattern.

Regular expression patterns are only supported for the file name or subfolder path in your URIs and the pattern matching is identical to that performed by the REGEXP_LIKE function. Regular expression patterns are not supported for directory names.

For example:

partitioning_clause => 'partition by range (col1)
                                (partition p1 values less than (1000) external location
				    ( ''https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o(/)*year=[0-9]+(/)*month=[0-9]+(/)*[a-z]+[1-3]??.txt''),….

See REGEXP_LIKE Condition for more information on REGEXP_LIKE condition.

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

Usage Notes

  • The procedure DBMS_CLOUD.CREATE_HYBRID_PART_TABLE supports external partitioned files in the supported cloud object storage services including Azure Blob Storage.

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

    See Cloud Object Storage URI Formats for more information.

  • The procedure DBMS_CLOUD.CREATE_HYBRID_PART_TABLE supports hybrid partitioned files in directories, either in a local file system or in a network file system.

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

Examples

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


BEGIN
   DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name  => 'HPT1',
    format      => json_object('delimiter'value ',', 'recorddelimiter'value 'newline'),
    column_list => 'NAME VARCHAR2(30), GENDER VARCHAR2(10), BALANCE number',
    partitioning_clause => 'partition by range (B  2  ALANCE)
               (partition p1 values less than (1000) external DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION (''Scott_male_1000.csv''),
                partition p2 values less than (2000) external DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION (''Mary_female_3000.csv''),
                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,
       force              IN BOOLEAN DEFAULT FALSE);

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

Ignore and do not report errors if the file does not exist. Valid values are: TRUE and FALSE. The default value is FALSE.

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 atpc_user:
GRANT WRITE ON DIRECTORY data_pump_dir TO atpc_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,
       force                IN BOOLEAN DEFAULT FALSE);

Parameters

Parameter Description

credential_name

The name of the credential to access the Cloud Object Storage.
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 Cloud Object Storage URI Formats.

force

Ignore and do not report errors if object does not exist. Valid values are: TRUE and FALSE. The default value is FALSE.

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

DELETE_OPERATION Procedure

This procedure clears the data load entries for the specified operation ID logged in the user_load_operations or dba_load_operations tables in your schema.

Syntax

DBMS_CLOUD.DELETE_OPERATION (
	id      IN NUMBER);

Parameters

Parameter Description

id

Specifies the operation ID associated with the log file entries you want to delete.

Example

SELECT id FROM user_load_operations WHERE type LIKE '%BAD%';
EXEC DBMS_CLOUD.DELETE_OPERATION(id);

DROP_EXTERNAL_TEXT_INDEX Procedure

This procedure drops text index on the Object Storage files.

The DROP_EXTERNAL_TEXT_INDEX procedure drops the specified index created with the CREATE_EXTERNAL_TEXT_INDEX procedure.

Syntax

DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
      index_name       IN  VARCHAR2,
);

Parameters

Parameter Description
index_name

Specifies the name of the index you are dropping.

The index name must match the name provided at the time of the index creation.

This parameter is mandatory.

Example

BEGIN 
DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
        index_name => 'EMP',
);
END;
/

EXPORT_DATA Procedure

This procedure exports data from Autonomous Database based on the result of a query. This procedure is overloaded and supports writing files to the cloud or to a directory.

Based on the format type parameter, the procedure exports files to the Cloud or to a directory location as text files in CSV, JSON, Parquet, or XML format.

Syntax

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

DBMS_CLOUD.EXPORT_DATA (
      credential_name   IN VARCHAR2 DEFAULT NULL,
      file_uri_list     IN CLOB DEFAULT NULL,
      format            IN CLOB 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.

When the credential parameter is not included, this specifies output to a directory.

file_uri_list
There are different forms, depending on the value of the format parameter and depending on whether you include a credential parameter:

  • When the format parameter type value is json: The JSON on Object Store or to the specified directory location is saved with a generated file name based on the value of the file_uri_list parameter. See File Naming for Text Output (CSV,JSON, Parquet, 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.

  • When the credential_name parameter is not specified you provide a directory name in file_uri_list.

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

format

A JSON string that provides export format options.

Supported option is:

  • type: The type format option is required and must have one of the values: csv | datapump | json | parquet | xml.

See DBMS_CLOUD Package Format Options for EXPORT_DATA.

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, Parquet, or XML, or as dump files. For example:

SELECT warehouse_id, quantity FROM inventories

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 outputs the results of the specified query on the Cloud Object Store or to a directory location in CSV, JSON, Parquet, or XML file formats.

    See Export Data to Object Store as Text Using DBMS_CLOUD.EXPORT_DATA and Export data to a Directory Using DBMS_CLOUD.EXPORT_DATA for more information on using DBMS_CLOUD.EXPORT_DATA with CSV, JSON, Parquet, or XML output files.

  • For CSV, JSON, or XML output, by default when a generated file contains 10MB of data a new output file is created. However, if you have less than 10MB of result data you may have multiple output files, depending on the database service and the number of ECPUs (OCPUs if your database uses OCPUs) for the Autonomous Database instance.

    See File Naming for Text Output (CSV, JSON, Parquet, or XML) for more information.

    The default output file chunk size is 10MB for CSV, JSON, or XML. You can change this value with the format parameter maxfilesize option. See DBMS_CLOUD Package Format Options for EXPORT_DATA for more information.

  • For Parquet output, each generated file is less than 128MB and multiple output files may be generated. However, if you have less than 128MB of result data, you may have multiple output files depending on the database service and the number of ECPUs (OCPUs if your database uses OCPUs) for the Autonomous Database instance.

    See File Naming for Text Output (CSV, JSON, Parquet, or XML) for more information.

  • The parquet output format can only be used with high and medium database services for Autonomous Databases on Dedicated Exadata Infrastructure.

Usage Notes for DBMS_CLOUD.EXPORT_DATA with Output to a Directory

  • The provided directory must exist and you must be logged in as the ADMIN user or have WRITE access to the directory.

  • DBMS_CLOUD.EXPORT_DATA does not create directories.

  • The procedure does not overwrite files. For example, if a dump file in the file_uri_list exists, DBMS_CLOUD.EXPORT_DATA reports an error such as: 

    ORA-31641: unable to create dump file  "/u02/exports/123.dmp"
ORA-27038: created file already exists

Examples

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/exp1.json', 
      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/exp1.xml', 
      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/exp.csv', 
      query           => 'SELECT * FROM DEPT',
      format          => JSON_OBJECT('type' value 'csv', 'delimiter' value '|', 'compression' value 'gzip', 'header' value true ));
     );
   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.

object_uri

Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see Cloud Object Storage 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 atpc_user: 

GRANT WRITE ON DIRECTORY data_pump_dir TO atpc_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 atpc_user: 

    GRANT READ ON DIRECTORY data_pump_dir TO atpc_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.
location_uri

Object or file URI. The format of the URI depends on the Cloud Object Storage service you are using, for details see Cloud Object Storage 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
    Amazon S3 Returns NULL Returns timestamp
    Amazon S3-Compatible Returns NULL Returns timestamp
    Azure Returns timestamp Returns timestamp

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

MOVE_OBJECT Procedure

This procedure moves an object from one Cloud Object Storage bucket or folder to another.

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.

Syntax

DBMS_CLOUD.MOVE_OBJECT (
    source_credential_name  IN  VARCHAR2 DEFAULT NULL,
    source_object_uri       IN  VARCHAR2,
    target_object_uri       IN  VARCHAR2,
    target_credential_name  IN  VARCHAR2 DEFAULT NULL
);

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_object_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 Cloud Object Storage URI Formats for more information.

target_object_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 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_object_uri is set to the source_credential_name value.

Example

BEGIN 
DBMS_CLOUD.MOVE_OBJECT (
    source_credential_name => 'OCI_CRED',
    source_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname1/bgfile.csv',
    target_object_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname2/myfile.csv'
);
END;
/

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
       compression          IN VARCHAR2 DEFAULT  NULL);


DBMS_CLOUD.PUT_OBJECT (
       credential_name      IN VARCHAR2,
       object_uri           IN VARCHAR2,
       contents             IN BLOB
       compression          IN VARCHAR2 DEFAULT  NULL);

Parameters

Parameter Description

credential_name

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

object_uri

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

directory_name

The name of the directory on the Autonomous Database.

Foot 2

contents

Specifies the BLOB to copy from Autonomous Database to the Cloud Object Storage.

file_name

The name of the file in the specified directory.

compression

Specifies the compression used to store the object.

Default value: NULL

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

GRANT READ ON DIRECTORY data_pump_dir TO atpc_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 Cloud Object Storage 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,
       subpartition_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,
       subpartition_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.

subpartition_name

If defined, then only a specific subpartition is validated. If not specified then read from all external partitions or subpartitions 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,
       subpartition_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,
       subpartition_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.

subpartition_name

If defined, then only a specific subpartition is validated. If not specified then read from all external partitions or subpartitions 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.