Adding an End-of-Line Character to a Fixed-ASCII File

If you are writing text in a fixed-ASCII format, separate lines for each record are helpful. For example, consider a table with the following schema:

TABLE sample (
   lastname CHAR(10),
   firstname CHAR(10),
   dateofbirth DATE);

This table contains the following values:

Adams      Sam        10-02-1957

Smith      John       01-01-1920

Next, consider an external table with the following schema:

CREATE EXTERNAL TABLE sample_ext (
   lastname CHAR(10) EXTERNAL CHAR(10),
   firstname CHAR(10) EXTERNAL CHAR(10),
   dateofbirth DATE EXTERNAL CHAR(12));

Unloading sample_ext without an end-of-line character produces the following output:

Adams     Sam       10-02-1957  Smith     John      01-01-1920

An end-of-line character makes the date more legible and clear. If you use delimited format defaults, an end-of-line character is automatic. However, for fixed-format unloads, you need to add an end-of-line character to your records. You can add end-of-line characters in several ways.

Using a Program or Script

To add an end-of-line character, you can write the fixed-length records to a data file and then modify the data file with a program or script. For example, you could use a C program to find the length of each record, locate the end of a line, and then add an end-of-line character.

Adding a Newline Field in a SELECT Statement

To add an end-of-line character, select a final value from a table that contains a newline character. You can use an external table to load the newline character in your internal table, as in the following example:

1. Create a file that contains only a newline character.

   echo "" > /tmp/cr.fixed

2. Create an internal table to store this newline value to use later with your unload.

   CREATE TABLE dummyCr (cr CHAR(1)); 

3. Create the external table to load in the newline value.

   CREATE EXTERNAL TABLE x_cr (cr (CHAR(1) EXTERNAL CHAR(1)) 

   USING (DATAFILES ("disk:1:/tmp/cr.fixed"), FORMAT 'FIXED');

4. Load the external table in the internal dummyCr table.

   INSERT INTO dummyCr SELECT * FROM x_cr;

The internal table, dummyCr, now contains an end-of-line character that you can use to unload in a SELECT statement:

1. To unload from your internal table to an external table, create the external table with the end-of-line character as an external character.

   CREATE EXTERNAL TABLE sample_ext 
   (
      lastname CHAR(10) EXTERNAL CHAR(10),
      firstname CHAR(10) EXTERNAL CHAR(10),
      dateofbirth DATE EXTERNAL CHAR(12),
      eol CHAR(1) EXTERNAL CHAR(1)) 
   USING (DATAFILES ....), FORMAT 'FIXED');

2. Select from the internal table and the dummyCr table to create an output file that has rows separated by end-of-line characters.

   INSERT INTO sample_ext(lastname, firstname, dateofbirth, eol)
   SELECT a.lastname, a.firstname, a.dateofbirth, b.cr
   FROM mytable a, dummyCr b;