DBMS_CLOUD Package Format Options

The format parameter is used in several DBMS_CLOUD subprograms to specify the format of source files.

The two ways to specify the format parameter 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')
Format Option Description Syntax Default

blankasnull

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

blankasnull : true

False

characterset

Specifies the characterset of source files

characterset: string

Database characterset

compression

Specifies the compression type of the source file.

Note:

ZIP archiving format is not supported.

compression: auto|gzip|zlib|bzip2

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

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

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

Database date format

delimiter

Specifies the field delimiter

delimiter : character

| (pipe character)

escape

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

escape : true

False

ignoreblanklines

Blank lines are ignored when set to true.

ignoreblanklines : true

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

False

language

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

language: string

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

Null

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'

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

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

Default is derived from the setting of the NLS_TERRITORY parameter

quote

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

quote: character

Null meaning no quote

recorddelimiter

Specifies the record delimiter.

recorddelimiter: character

newline

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 parameter explicitly if you want to override the default behavior, for example:

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

rejectlimit

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

rejectlimit: number

0

removequotes

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

removequotes: true

False

skipheaders

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

skipheaders: number

0 if not specified, 1 if specified without a value

territory

Specifies a territory name to further determine input data characteristics.

territory: string

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

Null

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

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

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

Database timestamp with timezone format

trimspaces

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

See the description of trim_spec in Oracle Database Utilities.

trimspaces: rtrim| ltrim| notrim| lrtrim| ldrtrim

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

False

type

Specifies the source file type.

Cannot be specified together with delimiter or quote.

See the description of CSV in Oracle Database Utilities.

type: csv|csv with embedded|csv without embedded

csv is the same as csv without embedded.

Null

Related Topics