Oracle Cloud Infrastructure Documentation

DBMS_CLOUD Package Format Options for EXPORT_DATA

Describes the valid format parameter options for DBMS_CLOUD.EXPORT_DATA with text file formats, CSV, JSON, Parquet, or XML, and for Oracle Data Pump.

These are the valid format parameters for use with DBMS_CLOUD.EXPORT_DATA. You specify text file output when you use the format type option and the value is one of: csv, json, parquet, or xml. This also shows the format options when the format type is datapump.

The two ways to specify the format argument are:

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

And:

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

Examples:

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

To specify multiple format options, separate the values with a ",".

For example:

format => json_object('compression' value 'gzip', 'type' value 'json')

This table covers the format options for DBMS_CLOUD.EXPORT_DATA when the format parameter type option is one of: CSV, JSON, Parquet, or XML. For other procedures and other output types, see DBMS_CLOUD Package Format Options for the list of format options.

Format Option Description Syntax

compression

Specifies the compression type of the source file.

Note: ZIP archiving format is not supported.

When the format type is csv, json, or xml, the default compression is Null, meaning no compression.

When the format type is parquet, the default compression is snappy.

When the format type is datapump you can specify supported Oracle Data Pump access parameters:

  • compression: The valid values are: BASIC, LOW, MEDIUM, and HIGH.

  • version: The valid values are: COMPATIBLE, LATEST, and a specified version_number.

When the type is:csv | json | xml

compression:gzip

Default value: Null value meaning no compression.

When the type is parquet

compression: gzip | snappy

Default value: snappy

When the type is datapump

compression: BASIC|LOW|MEDIUM|HIGH

delimiter

Specifies a custom field delimiter.

format => json_object('delimiter' value '|')

The delimiter value cannot be an ASCII code or an escape character.

Note

This option only applies with csv type.

delimiter : character

Default value , (comma)

endquote

Specifies that fields can be enclosed between two delimiters, with quote and endquote. If endquote is not specified, then the quote character will be used by default as the endquote character.

For example:

format => JSON_OBJECT(‘quote’ value ‘(’, ‘endquote’ value ‘)’)
Note

This option only applies with csv type.

endquote:character

Default value: Null, meaning no endquote.

escape

Specifies the occurrence of quote character in the field value using "\" character.

Note

This option only applies with csv type.

escape : true

Default value: false

encryption

The format option encryption specifies the encryption and decryption options to export and import data to and from the Object Store.

Use encryption to specify the following parameters to encrypt and decrypt:

  • user_defined_function: Specifies a fully qualified user-defined function to decrypt or encrypt the specified BLOB (binary large object). It returns a decrypted or encrypted BLOB. This parameter is mutually exclusive with other parameters for encryption.

    For example, ADMIN.DECRYPTION_CALLBACK.

  • type: Specifies the built-in encryption algorithm to decrypt or encrypt. user_defined_function and type are mutually exclusive.

    type accepts values in the Block Cipher Algorithms + Block Cipher Chaining Modifiers + Block Cipher Padding Modifiers format.

    Supported Block Cipher Algorithms are:

    • DBMS_CRYPTO.ENCRYPT_AES256

    Supported Block Cipher Chaining Modifiers are:

    • DBMS_CRYPTO.CHAIN_CBC

    • DBMS_CRYPTO.CHAIN_CFB

    • DBMS_CRYPTO.CHAIN_ECB

    • DBMS_CRYPTO.CHAIN_OFB

    Supported Block Cipher Padding Modifiers are:

    • DBMS_CRYPTO.PAD_PKCS5

    • DBMS_CRYPTO.PAD_NONE

    • DBMS_CRYPTO.PAD_ZERO

    • DBMS_CRYPTO.PAD_ORCL

  • credential_name: Specifies the credential used to store the encryption key.

The Block Cipher Chaining Modifiers and Block Cipher Padding Modifiers values defaults to DBMS_CRYPTO.CHAIN_CBC and DBMS_CRYPTO.PAD_PKCS5, if you do not specify values for these parameters.

The format option encryption is used with the following DBMS_CLOUD procedures:

  • Used to pass parameters to decrypt for these procedures:

    • DBMS_CLOUD.COPY_DATA

    • DBMS_CLOUD.CREATE_EXTERNAL_TABLE

    • DBMS_CLOUD.CREATE_HYBRID_TABLE

    • DBMS_CLOUD.COPY_COLLECTION

  • Used to pass parameters to encrypt for these procedure:

    • DBMS_CLOUD.EXPORT_DATA

For example:

format => JSON_OBJECT('encryption' value json_object ('type' value DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5, 'credential_name' value 'ENCRYPTION_CRED'))		 encryption:value

Where value is a JSON string that provides additional parameters for encryption:

type: value

Specifies the encryption type.

credential_name: value

Specifies the credential used to store the encryption key.

user_defined_function: value

Specifies a fully qualified user-defined function to decrypt or encrypt the specified BLOB (binary large object).

header

Writes column names as the first line in output files of csv type.

The header option can accept a boolean or a string value.

The valid values are:

  • false: Skips the header row.

  • true: Includes the header row. The column names are based on the SELECT statement in the query parameter. You must specify column aliases in the SELECT statement when using virtual columns or expressions.

  • String to define custom header names: Enables you to define header rows with custom names. The number of columns and delimiters in the string value must match the number of columns and delimiters in the SELECT statement. The default delimiter is comma (,).

For example:

format => JSON_OBJECT('type' value 'csv', 'delimiter' value '|', 'compression' value 'gzip', 'header' value true)
Note

This option only applies with csv type.

header: true| false| String to define custom header names

Default value: false

fileextension

Custom file extension to override the default choice for the format type. This applies to text formats with DBMS_CLOUD.EXPORT_DATA: CSV, JSON, Parquet, or XML.

If the specified string does not start with period (dot), then a dot is automatically inserted before the file extension in the final file name.

If no file extension is desired, use the value: fileextension ='none'

Valid values: Any file extension.

Default value: Depends on the format type option:
  • CSV format: .csv
  • JSON format: .json
  • PARQUET format: .parquet
  • XML format: .xml

maxfilesize

Number in bytes for maximum size of output generated.

This applies to text based formats for exporting data with DBMS_CLOUD.EXPORT_DATA when the format type option is set to,csv, json, or xml.

Note: This option is not valid when the format type option is parquet.

Minimum value: 10485760 (10 MB )

Maximum value: 1 GB

Default value: 10485760 (10 MB)

quote

In CSV format, fields can be enclosed between two delimiters. Specify the delimiters with quote and endquote. If endquote is not specified, then the quote character will be used by default as the endquote character.

Note

This option only applies with csv type.

quote: character

Default value: Null meaning do not enclose fields with quotes.

trimspaces

Specifies how the leading and trailing spaces of the fields are trimmed for CSV format. Trim spaces is applied before quoting the field, if the quote parameter is specified.

See the description of trim_spec.

Note

This option only applies with csv type.

trimspaces: rtrim| ltrim| notrim| lrtrim| ldrtrim

Default value: notrim

type

Specifies the output file type.

csv: specifies Character Separated Values (CSV) format that allows you to export query results as a set of column values separated by any custom character.

json: specifies to export the query results as JSON files.

parquet: specifies to export the query results as Parquet files.

xml: specifies to export query results as rows of valid XML documents. Each row is encapsulated in a root XML tag of <RECORD> </RECORD>.

The query result is automatically transformed into XML format using XMLFOREST SQL function. Use Column Aliases to customize the XML tag names for columns.

When the format type is datapump you can specify supported Oracle Data Pump access parameters:

  • compression: The valid values are: BASIC, LOW, MEDIUM, and HIGH.

  • version: The valid values are: COMPATIBLE, LATEST, and a specified version_number.

See access_parameters Clause for more information.

type: csv|datapump|json|parquet|xml

Parent topic: DBMS_CLOUD Package