首页 > 数据库 > Oracle > Using SQL-Loader to load data into Oracle

Using SQL-Loader to load data into Oracle

原创 Oracle 作者:YoungEric 时间:2007-09-19 12:46:18 0 删除 编辑

Oracle Resources

Using SQL-Loader to load data into Oracle

The Oracle system in the labs has a software program called SQL-Loader that can be used to load large amounts of data into Oracle. This guide describes the command line version which is run from DOS. Oracle also has a Windows interface to this program. If you chose to use the Windows interface then the last step in this guide (Run SQL-Loader) will be different.

There are four stages to loading data using SQL-Loader:

  1. Create a data file The data file contains the data that you wish to load. There is one record per line and each attribute value is separated by a comma.
  2. Create the relation for the data
  3. Create a control file The control file tells Oracle how to load data from the data file.
  4. Run SQL-Loader SQL-Loader reads the control file and loads the data. SQL-Loader creates a number of files as it loads the data. A log file is produced that describes what happened and describes any errors that may have occurred.


There is a lot of documentation for Oracle online. SQL-Loader is described in the Oracle8i Utilities User's Guide. The example used below is based on one of the case studies described in the guide book. There are a number of other examples and you should read them all. Before going any further, please read the documentation.


1. Create a data file

The data file is a text file that contains the data. Create a text file by running the text editor and typing the following data:

12, Research, "Saratoga"
10, "Accounting", Cleveland
11, "Art", Salem
13, Finance, Boston
21, Sales, Phila
22, Sales, Rochester
42, "Int'l", "San Fran"

Save the file as test.dat in your h:.

2. Create the relation

Create a relation testdept in your Oracle account using the following command:

(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));

The data in h: est.dat will be loaded into the relation testdept using SQL-Loader. The relation testdept must exist in the database before SQL-Loader can load data into it.

3. Create a control file

The control file describes the structure of the data and indicates the relation into which the data should be loaded. Create a text file containing the following:

INFILE 'h:	est.dat'
INTO TABLE testdept
(deptno, dname, loc)

Save the file as test.ctl in your h:.

This file instructs SQL-Loader to load the data from the data file h: est.dat.txt into the relation testdept. It also states that each line or record in the file contains three attributes corresponding to the attributes deptno, dname and loc in the relation testdept. The attribute names must be in the same order in the control file as the attribute values are in the data file. The attribute values in the data file are separated by commas and may be enclosed in double quotation marks.

This example puts the data in a separate file to the control information. The documentation shows examples of case studies where the control information and the data are held in the one file. It is easier to generate data when the data and control information are stored in separate files. The double quotes are necessary because some data may contain commas as part of the actual data. If data that contains commas is enclosed in double quotes then the commas will be ignored when processed by SQL-Loader. Although it is possible to use other delimiters and quotes, it is best to enclose all strings in double quotes and separate all attributes by commas.

4. Run SQL-Loader

Start a DOS window and change to the directory where you stored the control and data files (e.g. h:). The command to execute the SQL-Loader is:

sqlldr userid=u9700000@dec1 control=h:	est.ctl log=h:	est.log

Type this command on one line. It runs the SQL-Loader program, logs into dec1 as the given user (u9700000) and asks for a password. The command also uses the control file test.ctl and writes a log file called test.log. If sqldr fails to run in DOS you will have to find its location in the Windows file explorer and run the command using the full path name (e.g. c:oracle...insqldr.exe). You must replace the username u9700000 with your Oracle username.

When the SQL-Loader finishes it will have created a log file, for example, the file h: est.log contains:

SQL*Loader: Release

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

Control File:   test.ctl
Data File:      test.dat
  Bad File:     test.bad
  Discard File:  none specified
 (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 TESTDEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name    Position   Len  Term Encl Datatype
---------------- ---------- ----- ---- ---- ---------
DEPTNO              FIRST     *   ,    O(") CHARACTER            
DNAME                NEXT     *   ,    O(") CHARACTER            
LOC                  NEXT     *   ,    O(") CHARACTER            

  7 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:   49536 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Mon Nov 28 15:29:43 2005
Run ended on Mon Nov 28 15:29:50 2005

Elapsed time was:     00:00:06.42
CPU time was:         00:00:00.04

This log file says that 7 records have been loaded into the testdept relation and there where no records rejected as incorrect. A record is incorrect if it is missing values or an integrity constraint is broken (e.g. non-unique primary keys, etc).

If some records are rejected they are normally placed in a file with a .bad extension (e.g. test.bad). If, after loading a lot of records, the log file indicates there are a few records with errors it is normally easier to ignore the errors than to try and find them.

Additional Control File Options

The example control file loads data into an empty relation. If data is to be appended to existing data then the control file must instruct SQL-Loader to append the new data to the existing relation:

INFILE 'h:	est.dat'
(deptno, dname, loc)

The documentation explains these options in detail.

一:sql loader 的特点
sql loader 工具却没有这方面的问题,它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。

二:sql loader 的帮助


SQL*Loader: Release - Production on 星期六 10月 9 14:48:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

用法: SQLLDR keyword=value [,keyword=value,...]


userid -- ORACLE username/password
control -- Control file name
log -- Log file name
bad -- Bad file name
data -- Data file name
discard -- Discard file name
discardmax -- Number of discards to allow (全部默认)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (全部默认)
errors -- Number of errors to allow (默认50)
rows -- Number of rows in conventional path bind array or between direct p
ath data saves
(默认: 常规路径 64, 所有直接路径)
bindsize -- Size of conventional path bind array in bytes(默认256000)
silent -- Suppress messages during run (header,feedback,errors,discards,part
direct -- use direct path (默认FALSE)
parfile -- parameter file: name of file that contains parameter specification
parallel -- do parallel load (默认FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
readsize -- Size of Read buffer (默认1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(
columnarrayrows -- Number of rows for direct path column array(默认5000)
streamsize -- Size of direct path stream buffer in bytes(默认256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session(默认FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE(默认7200)
date_cache -- size (in entries) of date conversion cache(默认1000)

PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlload
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。


三:sql loader使用例子
a)SQLLoader将 Excel 数据导出到 Oracle

  load data           --1、控制文件标识
  infile 'test.txt'       --2、要输入的数据文件名为test.txt
  append into table test    --3、向表test中追加记录
  fields terminated by X'09'  --4、字段终止于X'09',是一个制表符(TAB)
  (id,username,password,sj)   -----定义列对应顺序


来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。



  • 博文量
  • 访问量