ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql load的CTL文件

sql load的CTL文件

原创 Linux操作系统 作者:fengjin821 时间:2009-07-18 09:49:07 0 删除 编辑

有一个文件,名称是export.txt,该文件前面几行如下所示:
000000000800,SEP000000000800,800
000000000810,SEP000000000810,810
000000000813,000000000813,813
000000000820,SEP000000000820,820
...

其中第一列和第二列是VARCHAR2,第三列是INTEGER
我在scott下建立了一个表是phone,三个字段的名称分别是MAC, DESCRIPTIONS, EXTENTION
前两个字段类型为VARCHAR2(20), 第三个字段类型为INTEGER

我现在希望将txt文件导入到scott.phone表里

 

任意文本编辑器创建.ctl文件,内容如下:
LOAD DATA
INFILE export.txt
INTO TABLE phone
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(MAC,DESCRIPTIONS,EXTENTION)

 

SQLLDR   username/password@tns_name   CONTROL=c:\temp\discount.ctl   LOG=c:\temp\discount.log  DATA=c:\temp\discount.txt   BAD=c:\temp\discount.bad   ERRORS=100000

 

如果没有使用 trailing nullcols,则当导入的数据中有字段为空值的情况下,会报如下错误:

 

export.log的内容如下所示,193条记录中有11条没有导入。

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 20 23:34:27 2009

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

Control File:   Export.CTL
Data File:      export.txt
  Bad File:     export.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 PHONE, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MAC                                 FIRST     *   ,       CHARACTER            
DESCRIPTIONS                         NEXT     *   ,       CHARACTER            
EXTENTION                            NEXT     *   ,       CHARACTER            

Record 41: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 149: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 169: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 170: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 177: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 179: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 185: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 186: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 187: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 189: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 190: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table PHONE:
  182 Rows successfully loaded.
  11 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:           193
Total logical records rejected:        11
Total logical records discarded:        0

Run began on Sat Jun 20 23:34:27 2009
Run ended on Sat Jun 20 23:34:27 2009

Elapsed time was:     00:00:00.23
CPU time was:         00:00:00.04

 

 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-609548/,如需转载,请注明出处,否则将追究法律责任。

下一篇: db2 Q复制的问题
请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    505266