Export file to fixed length format

Answer

To export a file to a fixed length format, use an external table with all VARCHAR fields. The fields should be defined with a width equivalent to the space allocated in the flat file for that column. When inserting into the external table, typecast all fields to the desired format, and LPAD/RPAD as needed. Note that CHAR cannot be used as it automatically gets trimmed on export. Also, the output file will still have a pipe delimiter, which can be ignored by the import program.

For example:

DROP TABLE fixed_length;
CREATE EXTERNAL TABLE fixed_length (
c_custkey VARCHAR(10),UNLOAD_DATE VARCHAR(16),c_name VARCHAR(25),
c_address VARCHAR(80),c_nationkey VARCHAR(10),c_phone VARCHAR(15),
c_acctbal VARCHAR(15), c_mktsegment VARCHAR(10) ,c_comment VARCHAR(117)) USING (DATAOBJECT ('/tmp/fixed_length.dat'));


INSERT INTO fixed_length
    SELECT      LPAD(c_custkey,10),
                        CURRENT_DATE,
                        RPAD(c_name,25),
                        RPAD(c_address,40),
                        LPAD(c_nationkey,10),
                        LPAD(c_phone,15),
                        LPAD(c_acctbal,10),
                        RPAD(c_mktsegment,10),
                        RPAD(c_comment,117)
   FROM          customer
   LIMIT 5;