Customizing Formats for Tables That Use the Direct Path Unload Method

custom_format_id

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. For information about valid Oracle datetime formats, see the Oracle documentation.

For example:

[DATE_FORMATS]
1=dd/mm/yyyy

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

COLUMN_NAME

Assigns a custom date or timestamp format to a column. You can also use this parameter to control numeric precision and padding and 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;]

Ensure that you use all uppercase for the

COLUMN_NAME

value.

To assign a custom date format, 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, 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, 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

custom_format_id

parameter.

Ensure that the

suppress_trailing_nullcols

parameter is set to false. If the

suppress_trailing_nullcols

parameter is set to true, Fast Clone does not unload trailing columns that contain null values, even if you specify a null replacement value.

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

p:
custom_precision;

To assign a custom padding value, 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/2012 00:00:00.0;
DATE_COL=d:1;

Customizing 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 spaces when the length of a 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/2012') as COL4