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')

Note:

For Parquet and Avro format options, see DBMS_CLOUD Package Format Options for Parquet and Avro.

As noted in the Format Option column with the text, "Option valid with JSON data", the following format options are valid for a format argument supplied with the DBMS_CLOUD.COPY_COLLECTION procedure: characterset, compression, ignoreblanklines, recorddelimiter, rejectlimit, unpackarrays. For the DBMS_CLOUD.COPY_COLLECTION procedure other listed format options are not supported.

Format Option Description Syntax

blankasnull

When set to true, loads fields consisting of spaces as null.

blankasnull : true

Default value: False

characterset

Specifies the characterset of source files

characterset: string

Default value: Database characterset

compression

Option valid with JSON data

Specifies the compression type of the source file.

ZIP archiving format is not supported.

Specifying the value auto checks for the compression types: gzip, zlib, bzip2.

compression: auto|gzip|zlib|bzip2

Default value: Null value meaning no compression.

conversionerrors

If a row is rejected because of data type conversion errors, the related columns are stored as null or the row is rejected.

conversionerrors : reject_record | store_null

Default value: reject_record

dateformat

Specifies the date format in the source file. The format option AUTO searches for the following formats:

J 
MM-DD-YYYYBC 
MM-DD-YYYY 
YYYYMMDD HHMISS 
YYMMDD HHMISS 
YYYY.DDD 
YYYY-MM-DD

dateformat : string

Default value: Database date format

delimiter

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:

format => json_object('delimiter' value 'X''9''')

delimiter : character

Default value | (pipe character)

escape

The character "\" is used as the escape character when specified.

escape : true

Default value: False

ignoreblanklines

Option valid with JSON data

Blank lines are ignored when set to true.

ignoreblanklines : true

Default value: False

ignoremissingcolumns

If there are more columns in the field_list than there are in the source files, the extra columns are stored as null.

ignoremissingcolumns : true

Default value False

language

Specifies a language name (for example, FRENCH), from which locale-sensitive information can be derived.

language: string

Default value: Null

See Locale Data in Oracle Database Globalization Support Guide for a listing of Oracle-supported languages.

numericcharacters

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

numericcharacters: 'decimal_character group_separator'

Default value: ".,"

See NLS_NUMERIC_CHARACTERS in Oracle Database Globalization Support Guide for more information.

numberformat

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

numberformat: number_format_model

Default value: is derived from the setting of the NLS_TERRITORY parameter

See Number Format Models in SQL Language Reference for more information.

quote

Specifies the quote character for the fields, the quote characters are removed during loading when specified.

quote: character

Default value: Null meaning no quote

recorddelimiter

Option valid with JSON data

Specifies the record delimiter.

By default, DBMS_CLOUD tries to automatically find the correct newline character as the delimiter. It first searches the file for the Windows newline character "\r\n". If it finds the Windows newline character, this is used as the record delimiter for the file. If a Windows newline character is not found, it searches for the UNIX/Linux newline character "\n" and if it finds one it uses it as the record delimiter for the file.

Specify this argument explicitly if you want to override the default behavior, for example:

format => json_object('recorddelimiter' VALUE '''\r\n''')

recorddelimiter: character

Default value: newline

rejectlimit

Option valid with JSON data

The operation will error out after specified number of rows are rejected.

rejectlimit: number

Default value: 0

removequotes

Removes any quotes that are around any field in the source file.

removequotes: true

Default value: False

skipheaders

Specifies how many rows should be skipped from the start of the file.

skipheaders: number

Default value: 0 if not specified, 1 if specified without a value

territory

Specifies a territory name to further determine input data characteristics.

territory: string

Default value: Null

See Locale Data in Oracle Database Globalization Support Guide for a listing of Oracle-supported territories.

timestampformat

Specifies the timestamp format in the source file. The format option AUTO searches for the following formats:

YYYY-MM-DD HH:MI:SS.FF 
YYYY-MM-DD HH:MI:SS.FF3 
MM/DD/YYYY HH:MI:SS.FF3

timestampformat : string

Default value: Database timestamp format

timestampltzformat

Specifies the timestamp with local timezone format in the source file. The format option AUTO searches for the following formats:

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

timestampltzformat : string

Default value: Database timestamp with local timezone format

timestamptzformat

Specifies the timestamp with timezone format in the source file. The format option AUTO searches for the following formats:

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

timestamptzformat: string

Default value: Database timestamp with timezone format

trimspaces

Specifies how the leading and trailing spaces of the fields are trimmed.

See the description of trim_spec.

trimspaces: rtrim| ltrim| notrim| lrtrim| ldrtrim

Default value: notrim

truncatecol

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.

truncatecol: true

Default value: False

type

Specifies the source file type.

See the description of CSV in field_definitions Clause

If the type is datapump, then the only other valid format option is rejectlimit.

See DBMS_CLOUD Package Format Options for Parquet and Avro for type values parquet and avro.

type: csv|csv with embedded|csv without embedded | datapump

csv is the same as csv without embedded.

Default value: Null

unpackarrays

This option is valid only with JSON data

When set to true, if a loaded document is an array, then the contents of the array are loaded as documents rather than the array itself. This only applies to the top-level array.

When set to true, the entire array is inserted as a single document.

This option is is valid only for JSON collection data.

unpackarrays: true

Default value: False