SQL Loader – Data Files Overview

Points to consider about data file

• Data File
• File Format (CSV/DATA/….etc)
• Fixed Length /Variable Length
• DataTypes of each column, width
• Frequency (per hour, per week, per month etc..)
• Location of the file
• Database Table

The data file can be in fixed record format or variable record format.
Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

Sample Fixed Length Data File

7369 SMITH     CLERK     902   12/17/1980       800
7499 ALLEN      SALESMAN  7698  02/20/1981       1600
7521 WARD      SALESMAN  7698  02/22/1981       1250
7566 JONES     MANAGER   7839  04/02/1981        2975
7654 MARTIN   SALESMAN  7698  09/28/1981        250

Variable Length Data Files (Delimiter Based Data Files)

• Variable Record Format would like below where the data fields are separated by a delimiter.
• Note: The Delimiter can be anything you like. In this case it is “|”

1196700|9|0|692.64
1378901|2|3900|488.62
1418700|2|2320|467.92
1418702|14|8740|4056.36
1499100|1|0|3.68