ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sqlloader分隔符文件中有三个字段,是否将任意两个字段导入表中?

sqlloader分隔符文件中有三个字段,是否将任意两个字段导入表中?

原创 Linux操作系统 作者:hrb_qiuyb 时间:2008-01-31 08:22:09 0 删除 编辑

applezh提问:

分隔符文件 是否可以指定字段

比如:
分隔符文件中有三个字段,是否将任意两个字段导入表中?


这个问题主要是filler关键字的使用问题,以一个5字段导入3个和测试来回答:

1、在qiuyb用户下建一张表,只包含A,C,E三个字段

SQL> show user
USER is "QIUYB"
SQL> desc t1
Name Null? Type
------------ -------- ---------------
A VARCHAR2(10)
C VARCHAR2(10)
E VARCHAR2(10)

2、包含数据的文件1.txt则有7个字段
$ cat 1.txt
"a","b","c","d","e","f","g"
"aaa","bb","c","d0","eee","f","gg"
"baa","db","f","d1","eee","f","gg"
"caa","eb","g","d2","eee","f","gg"
"daa","fb","h","d3","eee","f","gg"

3、编写control文件,使用filler关键字,把不需要的字段filler掉

$ cat 1.ctl
LOAD DATA
INFILE '1.txt'
INTO TABLE T1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
a char(10),
b filler char(10),
c char(10),
d filler char(10),
e char(10),
f filler char(10),
g filler char(10)
)

4、执行sqlldr操作,可以见到成功导入5条记录

$ sqlldr qiuyb/qiuyb control=1.ctl log=1.log

SQL*Loader: Release 9.2.0.6.0 - Production on Sat Sep 9 12:23:29 2006

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

Commit point reached - logical record count 5

5、到库内查询,校验一下。

$ sqlplus qiuyb/qiuyb

SQL*Plus: Release 9.2.0.6.0 - Production on Sat Sep 9 12:24:08 2006

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> select * from t1;

A C E
---------- ---------- ----------
a c e
aaa c eee
baa f eee
caa g eee
daa h eee

SQL>

<完>

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2008-01-30

  • 博文量
    50
  • 访问量
    534854