Custom Formats for Tables That Use the Direct Path Unload Method

format_ID

=

custom_format

Specifies a custom DATE or TIMESTAMP format with a unique numeric ID. Ensure that each ID is a one- or two-digit number that is unique within the [DATE_FORMATS] or [TIMESTAMP_FORMATS] section of the configuration file. The length of a custom format must not exceed 99 characters. Also, the number of custom DATE or TIMESTAMP formats in a configuration must not exceed 99.

The following table specifies the datetime format elements that Fast Clone supports with the direct path unload method:

Format Element	Description
DD	Day of month
FF number	Number of fractional seconds Valid values: 1 through 9
HH	Hour of day in 12-hour format
HH24	Hour of day in 24-hour format
MI	Minutes
MM	Month
SS	Seconds
YY	Last 2 digits of the year
YYYY	4-digit year

For example:

[DATE_FORMATS]
1=dd/mm/yyyy

[TIMESTAMP_FORMATS]
1=dd/mm/yyyy hh24:mi:ss.ff6

COLUMN_NAME

Specifies the column to which to assign a custom date or timestamp format. You can also use this parameter to control numeric precision and padding and to indicate a specific date or timestamp value that replaces nulls.

Use the following general syntax:

COLUMN_NAME=
parameter_1:
value_1;[
parameter_n:
value_n;]

The

COLUMN_NAME

value must be in all uppercase.

To assign a custom date format to the column, use a statement that begins with "d:" and specifies a

custom_format_id

that you defined for a date format:

d:
custom_format_id;

This custom format overrides the format that is specified by the

date_format

parameter.

To assign a custom timestamp format to the column, use a statement that begins with "t:" and specifies a

custom_format_id

that you defined for a timestamp format:

t:
custom_format_id;

This custom format overrides the format that is specified by the

timestamp_format

parameter.

To assign a value that replaces null values for the column, use a statement that begins with "n:" and specifies the replacement value:

n:
null_replacement_value;

For DATE and TIMESTAMP columns, you can specify a

null_replacement_value

in any format. However, for consistency, use a custom format that is specified by the

format_ID

=

custom_format

parameter.

If the specified column is a trailing column and the

suppress_trailing_nullcols

parameter is set to true, Fast Clone will not unload the column, even if you specify a null replacement value. If the column should not be suppressed, ensure that the

suppress_trailing_nullcols

parameter is set to false.

To assign a custom precision to a NUMBER column, use statement that beings with "p:" and specifies the precision value:

p:
custom_precision;

To assign a custom padding value to the column, use a statement that begins with "a:" and specifies the padding value:

a:
custom_padding;

[DATE_FORMATS]
1=dd/mm/yyyy

[TIMESTAMP_FORMATS]
1=dd/mm/yyyy hh24:mi:ss.ff6

[TABLE1]
TIMESTAMP_WITHOUT_TZ_COL=t:1;n:14/12/2017 00:00:00.0;
DATE_COL=d:1;

Custom Formats for Tables That Use the Conventional Path Unload Method

To specify a custom date or timestamp format, use the following statement syntax:

to_char(
COLUMN_NAME, '
custom_format') as 
COLUMN_NAME

This custom format overrides the

date_format

or

timestamp_format

parameter value. For information about Oracle datetime format, see the Oracle documentation.

To specify a value to replace null values, use the following statement syntax:

nvl(
COLUMN_NAME, '
null_replacement_value') as 
COLUMN_NAME

To specify a custom precision value for a NUMBER column, use the following statement syntax:

round(
NUMBER_COLUMN_NAME, 
custom_precision) as 
NUMBER_COLUMN_NAME

To specify a custom column size to pad column values on the right with blank spaces when the length of the column value is less than the custom column size, use the following statement syntax:

rpad(
COLUMN_NAME, 
column_size, ' ') as 
COLUMN_NAME

table1_column_list=COL2,COL1,nvl(to_char(COL4, 'dd/mm/yyyy'), '14/12/2017') as COL4