Browse > Home / Oracle / SQL*Loader Input Data and Data File

SQL*Loader Input Data and Data File

January 15, 2007 by Mark Marucot 

SQL Loader reads data from one of more files specified in the control files. There three record formats of data file, they are Fixed-Record, Variable-Record and Stream Record Format.

Record Format

Fixed Record Format

A file is a fixed-record format if all of the record in a data file are same byte length. Since this is fixed, this format is the least flexible but results better performance than variable and stream format.

Syntax

PLAIN TEXT
CODE:
  1. INFILE “fix n”

where every record in the data file is in n bytes long.

Example 4.1
sample1.ctl

PLAIN TEXT
CODE:
  1. load data
  2. infile ’sample1.dat’ “fix 10″
  3. into table example
  4. fields terminated by ‘,’ optionally enclosed by ‘"’
  5. (col1 char(5),
  6. col2 char(7),
  7. col2 char(3))

sample1.dat

PLAIN TEXT
CODE:
  1. 001, Apple, 5
  2. 002, Orange, 3
  3. 003, Lemon, 4

In the Example 4.1, all record is 10 bytes long.
Variable Record Format

A file is a variable-record format if the length of the each record in a character field is included in the beginning of each record in the data file. This provides more flexibility than fixed-record format.
Syntax

PLAIN TEXT
CODE:
  1. INFILE "var n"

where every record in the data file is in n bytes long.

Example 4.2
sample2.ctl

PLAIN TEXT
CODE:
  1. load data
  2. infile ’sample2.dat’ “var 10″
  3. into table example
  4. fields terminated by ‘,’ optionally enclosed by ‘"’
  5. (col1 char(5),
  6. col2 char(7))

sample2.dat

PLAIN TEXT
CODE:
  1. 005Apple, 5
  2. 007Orange, 3
  3. 006Lemon, 4

In the Example 4.2, the first record is 5 bytes long, next record is 7 bytes long and the the last is 6 bytes long.
Stream Record Format

A file is a stream-record format when the records are not specified in size. SQL *Loader parsed the data based on the terminator defined in the control file.
Syntax

PLAIN TEXT
CODE:
  1. INFILE [“str terminator_string”]

The terminator_string is specified as either ‘char_string’ or X’hex_string’ where:

  • ‘char_string’ is a string of characters enclosed in single or double quotation marks
  • X’hex_string’ is a byte string in hexadecimal format

If the terminator string contains special characters, it should be X’hex_string’. Some special characters can be specified as ‘char_string’ by using a backslash. Such as:

  • \n indicates a line feed
  • \t indicates a horizontal tab
  • \f indicates a form feed
  • \v indicates a vertical tab
  • \r indicates a carriage return

Example 4.3
sample3.ctl

PLAIN TEXT
CODE:
  1. load data
  2. infile ’sample3.dat’ “str ‘|\n‘”
  3. into table example
  4. fields terminated by ‘,’ optionally enclosed by ‘"’
  5. (item char(10),
  6. destination char(2))

sample3.dat

PLAIN TEXT
CODE:
  1. Apple, US,
  2. Orange, AU,
  3. Lemon, UK,

In the Example 4.3, each record is terminated by ‘|\n’.

Related Posts

Filed Under: Oracle
Tagged:

Comments

Feel free to leave a comment...
and oh, if you want a pic to show with your comment, go get a gravatar!