sqlldr导入平面文件实例

目标：将一个平面文件学生表导入oracle数据库

1.准备一个学生文件（st.txt）

zs,chinese,91,
zs,maths,92,
zs,english,93,
ls,chinese,94,
ls,maths,95,
ls,english,96

2.oracle中创建一个学生表(scott.st)

SQL> create table st (name varchar2(20),subject varchar2(20),score int);

Table created.

3.创建一个控制文件（stctl.ctl）

load
  infile 'd:\ocp\script\st.txt'
  into table scott.st
  (
    name char terminated by ',',
subject char terminated by ',',
score integer external terminated by ','
  )

4.开始导入

C:\Users>sqlldr scott/tiger@orcl control='d:\ocp\script\stctl.ctl'


SQL*Loader: Release 10.2.0.1.0 - Production on Tue Dec 2 09:31:00 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Commit point reached - logical record count 5
Commit point reached - logical record count 6

5.查看结果

SQL> select * from st;


NAME                 SUBJECT                   SCORE
-------------------- -------------------- ----------
zs                   chinese                      91
zs                   maths                        92
zs                   english                      93
ls                   chinese                      94
ls                   maths                        95
ls                   english                      96

6 rows selected.