SQL*Loader Discarded and Rejected Records

Not all records read by SQL *Loader from the data file will be inserted to the database. All rejected and discarded records are placed in bad file. Records can be rejected by SQL *Loader and/or the Oracle database.

Causes of Record Rejection

Rejected by SQL *Loader

• Invalid input format
• Missing enclosure delimiter
• Delimited fields exceeds the maximum length

Read more

SQL*Loader Input Data and Data File

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

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

Read more

SQL *Loader Control File

SQL *Loader Control File is a text file that contains the information that defines how the data will be loaded. This file contains definition of the destination table, columns and it’s data types. It also includes definition of how the data will be read by defining the field limiter used in the data file.

Syntax

SQL *Loader Data Types

SQL *Loader is grouped into portable and non-portable data types. Each group is sub-grouped into value data type and length-value data type.

Nonportable Datatypes

Nonportable Value Datatypes

• INTEGER(n)
• SMALLINT
• FLOAT
• DOUBLE
• BYTEINT
• ZONED
• (packed) DECIMAL

Nonportable Length - Value Datatypes

• VARGRAPHIC
• VARCHAR
• VARRAW
• LONG VARRAW

Portable Datatypes

Portable Value Datatypes

• CHAR
• Datetime and Interval
• GRAPHIC
• GRAPHIC EXTERNAL
• Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, ZONED)
• RAW

Portable Length-Value Datatypes

• VARCHARC
• VARRAWC