Use SQL*Loader to load data from data file.

1.Create a table ACCOUNT used to test:

create   table  ACCOUNT
(
  USERNAME     VARCHAR2 ( 20 )  not   null ,
  PASSWORD     VARCHAR2 ( 20 )  not   null ,
  SEX          VARCHAR2 ( 5 ),
  BIRTHDAY    DATE,
  DESCRIPTION  VARCHAR2 ( 50 ),
  CREATEDATE  DATE,
  CREATEIP     VARCHAR2 ( 20 )
)

2. Using following data to create a data file named ACCOUNT.dat which to be loaded to the table ACCOUNT:

qin,130251,male,5/4/2007,test,5/4/2007 15:20,127.0.0.1
hu,130251,?,11/9/1983,sdfd,4/19/2007 11:39,127.0.0.1
??,130251,?,11/9/1983,fd,5/4/2007 10:43,127.0.0.1
dd,123,male,,fd,5/14/2007 17:50,127.0.0.1
d,123,male,11/3/1983,dfd,5/14/2007 17:58,127.0.0.1
qh,130251,?,,beautiful,5/15/2007 9:51,3.242.165.209

3.Using following text to create a Control File named controlfile.ctl:

load data infile 'account.txt' append into table account fields terminated by ',' TRAILING NULLCOLS(USERNAME,PASSWORD,SEX,BIRTHDAY date "MM/DD/YYYY",DESCRIPTION,CREATEDATE "trunc(to_date(:CREATEDATE,'MM-dd-yyyy HH24:mi:ss'))",CREATEIP)

4.Using following text to create a Batch File named account.bat:

sqlldr userid=scott/tiger@test control=account.ctl data=account.dat log=account.log bad=account.bad discard=account.dsc

5.Just run the bat file account.bat to load data to table,you will see the load result in the log file account.log and know which data are not loaded into the bad file account.bad.Following text a the running result:

---

SQL*Loader: Release 9.0.1.1.1 - Production on Thu May 17 20:27:55 2007

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Control File:   account.ctl
Data File:      account.dat
  Bad File:     account.bad
  Discard File: account.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 ACCOUNT, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
USERNAME                            FIRST     *   ,       CHARACTER           
PASSWORD                             NEXT     *   ,       CHARACTER           
SEX                                  NEXT     *   ,       CHARACTER           
BIRTHDAY                             NEXT     *   ,       DATE MM/DD/YYYY     
DESCRIPTION                          NEXT     *   ,       CHARACTER           
CREATEDATE                           NEXT     *   ,       CHARACTER           
    SQL string for column : "trunc(to_date(:CREATEDATE,'MM-dd-yyyy HH24:mi:ss'))"
CREATEIP                             NEXT     *   ,       CHARACTER           

Table ACCOUNT:
  6 Rows successfully loaded.
  0 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:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu May 17 20:27:55 2007
Run ended on Thu May 17 20:27:56 2007

Elapsed time was:     00:00:00.54
CPU time was:         00:00:00.01