Lining Up Columns in an External File

In the SQL statement of the external set, use concatenation operators (||) and native format and conversion functions to line up external file fields.

Example:

Suppose column1 is a variable string, column2 a date, and column3 a fixed-length string. You can produce a regular external file format by writing an export SQL statement of the following type.

SELECT  RPAD(<col1>, 20) || TO_CHAR(<col2>,'DDMMYYYY') || <col3>

FROM    <table>

This example uses the Oracle-specific TO_CHAR() and RPAD() functions.

RPAD(<col1>, 20) results in <col1> padded out to a 20-position string with blank positions if <col1> is shorter than 20 positions.

The TO_CHAR function is used here to convert the date value into a fixed-length string.