Jijo K Jose

The Data File – std_data.dat

The particular file used for this example contains the details of some students.

"1","Jijo","K","Jose","10","2010","March","92.82"
"2","Arun",,"Dev","3","2011","March","86.14"
"3","Deepak","T","Raj","5","2010","July","75.14"
"4","James","Tom","Mathew","8","2011","June","70"
"5","Sam","","Thomas","25","2010","July","89.52"
"6","Ajith","John","Mathew","1","2011","March","90.1475"
"7","Kamal","G","Sekar","17","2011","June","74"
"8","John","","James","12","2011","July","86.47"
"9","Thomas","","Paul","30","2009","March","90%"
"10","Praveen","Thomas","Jacob","22","2010","June","90.475"

As you can see, the data in the file is comma-delimited, and each field is enclosed within double quotes.
We used the following SQL statement to create the table into which all this data will be loaded:

CREATE TABLE STUDENT_DATA (
    id NUMBER(3),
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    roll_number NUMBER(2),
    batch CHAR(10),
    mark NUMBER(5,2)
) TABLESPACE SYSAUX;

As you can see, not all fields in the data file are to be loaded into the table.

The Control File – std_control.ctl

The following control file will be used to load the data into the table.

LOAD DATA
   APPEND INTO TABLE STUDENT_DATA
   (

       id INTEGER EXTERNAL TERMINATED BY "," ENCLOSED BY '"',
       first_name CHAR TERMINATED BY "," ENCLOSED BY '"',
       middle_name FILLER CHAR TERMINATED BY "," ENCLOSED BY '"',
       last_name CHAR TERMINATED BY "," ENCLOSED BY '"',
       roll_number  INTEGER EXTERNAL TERMINATED BY "," ENCLOSED BY '"',
       join_year FILLER INTEGER EXTERNAL TERMINATED BY "," ENCLOSED BY '"',
       batch CHAR TERMINATED BY "," ENCLOSED BY '"',
       mark DECIMAL EXTERNAL TERMINATED BY "," ENCLOSED BY '"'
   )

INTO TABLE
    The destination table is identified by the following INTO TABLE clause

APPEND
    The APPEND keyword tells SQL*Loader to preserve any preexisting data in the table

TERMINATED BY "," ENCLOSED BY '"'
    The field definitions are all contained within parentheses, and are separated from each other by commas. The fields in the data file are delimited by commas, and are also enclosed by double quotes

CHAR
    Tells SQL*Loader that a field is a text field

INTEGER EXTERNAL
    Tells SQL*Loader that a field is an integer represented using the text digits "0" through "9"

DECIMAL EXTERNAL
    Tells SQL*Loader that a field is a decimal value represented using the text digits "0" through "9" and an optional decimal point (".")

FILLER
    The keyword FILLER identifies the fields that are not being loaded into the database