Jijo K Jose

Now we have two files and one table named STUDENT_DATA

The Data file         : std_data.dat
The Control file     : std_control.ctl

If you didn't have the two files then read this post –> Oracle SQL*Loader 2 – Data and Control File

Place the data and control file inside the directory "D:\A5518\Zzz\SQL Loader". Open command prompt and navigate to "D:\A5518\Zzz\SQL Loader" directory then enter the following command.

The command used to initiate this load needs to invoke SQL*Loader and point it to the control file describing the data. In this case, the input file name is not provided in the control file, that name needs to be passed in on the command line as well. The following sqlldr command will do the job:

sqlldr <username>/<password> CONTROL=<control_file_name> DATA=<data_file_name> LOG=<log_file_name> DISCARD=<discard_file_name> BAD=<bad_file_name>

or

sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

Example

D:\A5518\Zzz\SQL Loader>sqlldr jijo/kjose CONTROL=std_control DATA=std_data LOG=std_log DISCARD=std_discard BAD=std_bad

SQL*Loader: Release 11.2.0.2.0 – Production on Mon Aug 5 17:05:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached – logical record count 9
Commit point reached – logical record count 10

D:\A5518\Zzz\SQL Loader>

The above command create 3 files inside the directory "D:\A5518\Zzz\SQL Loader"

Log file          : std_log

Discard File  : std_discard

Bad File        : std_bad

Log File

SQL*Loader: Release 11.2.0.2.0 – Production on Mon Aug 5 17:05:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   std_control.ctl
Data File:      std_data.dat
  Bad File:     std_bad.bad
  Discard File: std_discard.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table STUDENT_DATA, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
ID                                  FIRST     *   ,    "  CHARACTER            
FIRST_NAME                           NEXT     *   ,    "  CHARACTER            
MIDDLE_NAME                          NEXT     *   ,    "  CHARACTER            
  (FILLER FIELD)
LAST_NAME                            NEXT     *   ,    "  CHARACTER            
ROLL_NUMBER                          NEXT     *   ,    "  CHARACTER            
JOIN_YEAR                            NEXT     *   ,    "  CHARACTER            
  (FILLER FIELD)
BATCH                                NEXT     *   ,    "  CHARACTER            
MARK                                 NEXT     *   ,    "  CHARACTER            

Record 2: Rejected – Error on table STUDENT_DATA, column MIDDLE_NAME.
Initial enclosure character not found
Record 9: Rejected – Error on table STUDENT_DATA, column MARK.
ORA-01722: invalid number

Table STUDENT_DATA:
  8 Rows successfully loaded.
  2 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  99072 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         2
Total logical records discarded:        0

Run began on Mon Aug 05 17:05:14 2013
Run ended on Mon Aug 05 17:05:14 2013

Elapsed time was:     00:00:00.26
CPU time was:         00:00:00.03

Bad File

"2","Arun",,"Dev","3","2011","March","86.14"
"9","Thomas","","Paul","30","2009","March","90%"

Discard File

This file created only when some discarded data exists in the data file. In this example there is no discarded data so this file is not created.