DBMS_CLOUD Package Format Options
The format
argument in DBMS_CLOUD
specifies the
format of source files.
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 'CSV')
To specify multiple format options, separate the values with a ",
".
For example:
format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true', 'logretention' value 7)
For Avro, ORC, or Parquet format options, see DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet.
As noted in the Format
Option column, a limited set of format options are valid with
DBMS_CLOUD.COPY_COLLECTION
or with DBMS_CLOUD.COPY_DATA
when the format
type
is JSON.
Format Option | Description | Syntax |
---|---|---|
|
Specifies the type of Apache Iceberg table, such as AWS or OCI Object Storage, and what information is used to create the external table, for example information from a data catalog or from a direct metadata URI. |
See CREATE_EXTERNAL_TABLE Procedure for Apache Iceberg, for details on the |
|
When set to |
Default value: |
Valid with |
Specifies the characterset of source files |
Default value: Database characterset |
Only use with |
Array of JSON path expressions that correspond to the fields that need to be extracted from the JSON records. Each of the JSON path expressions in the array should follow the rules described in SQL/JSON Path Expressions . Only use with format JSON and |
JSON Array of json path expressions expressed in string
format. For example: 'columnpath' value |
Option valid with JSON data |
Specifies the compression type of the source file. ZIP archiving format is not supported. Specifying the value |
Default value: Null value meaning no compression. |
|
If a row is rejected because of data type conversion errors, the related columns are stored as null or the row is rejected. |
Default value: |
|
Specifies the date format in the source file. The format
option J MM-DD-YYYYBC MM-DD-YYYY YYYYMMDD HHMISS YYMMDD HHMISS YYYY.DDD YYYY-MM-DD |
Default value: Database date format |
|
Specifies the field delimiter. To use a special character as the delimiter, specify the HEX value of the ASCII code of the character. For example, the following specifies the TAB character as the delimiter:
|
Default value |
|
Specifies that the fields in the external data files are in a different order than the columns in the table. Detect the order of fields using the first row of each external data file and map it to the columns of the table. The field names in external data files are compared in case insensitive manner with the names of the table columns. This format option is applicable for the following procedures:
Restrictions for
See FIELD NAMES and the
description for |
Default value: |
|
The format option
For example: format => JSON_OBJECT('enablelogs' value FALSE) |
Default value: |
|
The format option Use
The Block Cipher Chaining Modifiers and Block Cipher Padding Modifiers values defaults to The format option
encryption is used with the following DBMS_CLOUD procedures:
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:
Specifies the encryption type.
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). |
|
Data can be enclosed between two delimiters, specified with
For example: format => JSON_OBJECT(‘quote’ value ‘(’,
‘endquote’ value ‘)’) |
Default value: Null, meaning no |
|
The character "\" is used as the escape character when specified. |
Default value: |
Option valid with JSON data |
Blank lines are ignored when set to true. |
Default value: |
|
If there are more columns in the
|
Default value |
|
Enable implicit partitioning and specify the partition
column names by using the
Implicit partitioning is enabled in the following ways:
See |
Default value: If
|
|
Enable implicit partitioning and specify the data types
of partition columns by using the
See |
Default value: If
|
Only use with
|
JSON path to identify the document to load. This option is valid only for JSON collection data with
|
Default value: Null |
keyassignment Only use with
|
Specifies whether a new collection is created as a mongo-compatible collection or as a SODA collection. When the value is set to By default this parameter is not set, meaning a new collection is created as a SODA collection. |
Default: |
Only use with
|
Specifies an attribute in the data to be loaded as the
If Set the value to a path, for example,
This parameter is optional and is only valid for loading into mongo-compatible collections. If not specified, Oracle generates a 12-byte unique
system ID and populates that as the |
Default: When |
|
Specifies a language name (for example, FRENCH), from which locale-sensitive information can be derived. |
Default value: Null See Locale Data in Oracle Database Globalization Support Guide for a listing of Oracle-supported languages. |
|
Specifies a string value that determines the directory object name where the By default, the For example:
The
The |
Default value: |
|
Specifies a string value that determines the prefix for the The log table name format is: logprefix By default, the logprefix is in upper case, but the case is reserved when the specified value is enclosed in double-quotes. For example: format => JSON_OBJECT ('logprefix' value 'TEST') Log files then use the |
Default value: |
|
Specifies a positive integer duration, in days, for which the
Valid values: For example: format => JSON_OBJECT ('logretention' value
7) |
Default value: |
This option is valid only with JSON data |
Maximum size of JSON documents. |
Default value: 1 Megabyte Maximum allowed value: 2 Gigabytes |
|
Specifies the characters to use as the group separator and decimal character. decimal_character: The decimal separates the integer portion of a number from the decimal portion. group_separator: The group separator separates integer groups (that is, thousands, millions, billions, and so on). |
Default value: See NLS_NUMERIC_CHARACTERS in Oracle Database Globalization Support Guide for more information. |
|
Specifies the number format model. Number format models cause the number to be rounded to the specified number of significant digits. A number format model is composed of one or more number format elements. This is used in combination with
|
Default value: is derived from the setting of the
See Number Format Models in SQL Language Reference for more information. |
|
The format option
If the data files are unstructured and the
For object names that are not based on hive format, the
order of the |
|
|
Specifies the quote character for the fields, the
|
Default value: Null meaning no quote |
Option valid with JSON data |
Specifies the record delimiter. By default, Specify this argument explicitly if you want to override the default behavior, for example:
To indicate that there is no record delimiter you can
specify a
The |
Default value: detected newline |
|
The format option
When the value of The characters "*" and "?" are considered wildcard characters when the 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 For external tables, this option is only supported with the tables that are created on a file in the Object Storage. For example:
See REGEXP_LIKE Condition for more information on |
Default value : |
|
The operation will error out after specified number of rows are rejected. |
Default value: |
|
Removes any quotes that are around any field in the source file. |
Default value: |
|
Specifies how many rows should be skipped from the start of the file. |
Default value: 0 if not specified, 1 if specified without a value |
|
Specifies a territory name to further determine input data characteristics. |
Default value: Null See Locale Data in Oracle Database Globalization Support Guide for a listing of Oracle-supported territories. |
|
Specifies the timestamp format in the source file. The
format option YYYY-MM-DD HH:MI:SS.FF YYYY-MM-DD HH:MI:SS.FF3 YYYY-MM-DD HH24:MI:SS.FF3 MM/DD/YYYY HH:MI:SS.FF3 |
Default value: Database timestamp format The string can contain wildcard characters such as "$". |
|
Specifies the timestamp with local timezone format in the
source file. The format option DD Mon YYYY HH:MI:SS.FF TZR MM/DD/YYYY HH:MI:SS.FF TZR YYYY-MM-DD HH:MI:SS+/-TZR YYYY-MM-DD HH:MI:SS.FF3 DD.MM.YYYY HH:MI:SS TZR |
Default value: Database timestamp with local timezone format |
|
Specifies the timestamp with timezone format in the
source file. The format option DD Mon YYYY HH:MI:SS.FF TZR MM/DD/YYYY HH:MI:SS.FF TZR YYYY-MM-DD HH:MI:SS+/-TZR YYYY-MM-DD HH:MI:SS.FF3 DD.MM.YYYY HH:MI:SS TZR |
Default value: Database timestamp with timezone format |
|
Specifies how the leading and trailing spaces of the fields are trimmed. See the description of trim_spec. |
Default value: |
|
If the data in the file is too long for a field, then this option will truncate the value of the field rather than reject the row. |
Default value: |
|
Specifies the source file type. See the description of If the If the See DBMS_CLOUD Package Format Options for Avro, ORC, or Parquet for For JSON data with For JSON data with
|
Note
Not all DBMS_CLOUD procedures support all of
these types.
Default value: Null For JSON data there are two valid |
Only use with
|
When set to When set to This option is valid only for JSON collection data with
|
Default value: |
Parent topic: DBMS_CLOUD Package