DBMS_CLOUD for Objects and Files

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

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_HYBRID_PART_TABLE Procedure

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

DELETE_ALL_OPERATIONS Procedure

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

DELETE_FILE Procedure

This procedure removes the specified file from the specified directory on Autonomous Database

DELETE_OBJECT Procedure

This procedure deletes the specified object on object store.

EXPORT_DATA Procedure

This procedure exports data from Autonomous Database to files in the Cloud based on the result of 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, 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 in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

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

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 for JSON data are: characterset, compression, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, unpackarray.

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

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 in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

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

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.

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 in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

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

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_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 in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

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

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

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

Usage Notes

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

    • Oracle Cloud Infrastructure Object Storage

    • Azure Blob Storage

    • Amazon S3

    • Google Cloud 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 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 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 in the file names in your URIs. The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

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

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, there are only two supported parameters. See DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet for details.

Examples ORC

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

Examples Avro

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

Examples Parquet

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

Avro, ORC, or Parquet Column Name Mapping to Oracle Column Names

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

CREATE_HYBRID_PART_TABLE Procedure

This procedure creates a hybrid partitioned table. This allows you to run queries on hybrid partitioned data from Autonomous Database 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.

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:

    • Oracle Cloud Infrastructure Object Storage

    • Azure Blob Storage

    • Amazon S3

    • Google Cloud 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;
/ 

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, or XML format.

Syntax

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

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

Parameters

Parameter Description

credential_name

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

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, or XML) for more information.

  • 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 | json | 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, or XML. 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, or XML 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, 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, 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.

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

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

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

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

BEGIN  
   DBMS_CLOUD.EXPORT_DATA(
      credential_name => 'OBJ_STORE_CRED',
      file_uri_list   => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/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);


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

Parameters

Parameter Description

credential_name

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

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 1

file_name

The name of the file in the specified directory.

Footnote 1

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.