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.

Table A-1 DBMS_CLOUD Format Options

Format Option Description Syntax Default

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

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

delimiter

Specifies the field delimiter

delimiter : character

| (pipe character)

trimspaces

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

See the description of trim_spec in Oracle Database Utilities, 18c

trimspaces : rtrim| ltrim| notrim| lrtrim| ldrtrim

notrim

characterset

Specifies the characterset of source files

characterset : string

Database characterset

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

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

type

Specifies the source file type.

Cannot be specified together with delimiter or quote.

See the description of CSV in Oracle Database Utilities, 18c

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

type : csv|csv with embedded|csv without embedded

csv is the same as csv without embedded.

Null

quote

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

quote : character

Null meaning no quote

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

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

removequotes

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

removequotes: true

False

blankasnull

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

blankasnull : true

False

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

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

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

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

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

rejectlimit

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

rejectlimit : number

0

compression

Specifies the compression type of the source file.

compression: auto|gzip|zlib|bzip2

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

Null value meaning no compression.

language

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

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

laguage : string

Null

territory

Specifies a territory name to further determine input data characteristics.

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

territory : string

Null