SEian.G

苦练七十二变,笑对八十一难

  • 博客访问: 41217
  • 博文数量: 70
  • 用 户 组: 普通用户
  • 注册时间: 2016-03-12 13:43
  • 认证徽章:
个人简介

冰冻三尺非一日之寒,滴水穿石非一日之功。

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(70)

文章存档

2017年(69)

2016年(1)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle


        在之前的四篇文章中,介绍了SQL*Loader的使用方法及使用案例,本篇使用SQL*Loader导入CLOBBLOB数据
      Oracle SQL*Loader使用案例(一)
      Oracle SQL*Loader使用案例(二)
      Oracle SQL*Loader使用案例(三)
      Oracle SQL*Loader使用案例(四)


      在使用SQL*Loader往数据库中导入blobclob数据总令人有些头疼。本文详细记录了用OracleSQL*Loader实现该功能的一个实验案例,希望能对大家有帮助。

 

 

首先创建一张用于实验的测试表

 

SCOTT@seiang11g>create table tb_clob(new_id number(20),new_to varchar2(100),new_subject varchar2(100),new_date date,new_content clob);
Table created.

 

下面是数据文件中的内容:

[oracle@wjq SQL*Loader]$ vim wjq_clob.csv
1,wjq123@qq.com,"Greeting from Mars",2017-11-02 10:59:43,\/u01\/app\/oracle\/SQL*Loader\/new_clob001.dat
2,seiang@126.com,"Special discount",2017-11-02 11:28:55,\/u01\/app\/oracle\/SQL*Loader\/new_clob002.dat
(特别注意:一定要注意数据文件的格式,不能有多余的空格存在,作者在这里栽过好多次的坑,否则导入的时候会报错,所以执行导入之后最好查看一下日志信息)

 

new_content是消息正文,用clob数据类型表示。在数据文件中该字段仅保存每条记录的消息正文的clob文件名。new_clob001.dat,new_clob002.dat文件中保存new_content的真正内容。

 

new_clob001.dat文件中的内容:

[oracle@wjq SQL*Loader]$ cat new_clob001.dat
China's top judicial authorities presented long-anticipated reports on Wednesday to national legislators on their progress in upholding the law and preventing wrongful convictions in the wake of important judicial reforms in 2013.

The Supreme People's Court and Supreme People's Procuratorate both submitted reports to the bimonthly session of the Standing Committee of the National People's Congress on Wednesday.The top court said it had overturned 37 wrongful convictions since November 2012, including in the high-profile case of Nie Shubin, who was exonerated on Dec 2, 2016, more than two decades after he was wrongly executed for rape and murder.

 

new_clob001.dat文件中的内容:

[oracle@wjq SQL*Loader]$ cat new_clob002.dat  
Thanks to these efforts, courts acquitted 4,032 defendants in accordance with the law between 2013 and September this year, the report said.

Courts have also been ordered to strictly exclude evidence obtained illegally, including evidence gained by torture, "and not to force anyone to plead guilty", Zhou said.In Shanghai, for example, between July 2016 and September this year, the city's courts received 24 applications from defense attorneys to strike evidence suspected to have been obtained illegally, leading to 15 reviews on the legality of evidence, he said.

 

导入上述的数据需要写如下的控制文件:

[oracle@wjq SQL*Loader]$ cat wjq_clob.ctl
LOAD DATA  
INFILE '/u01/app/oracle/SQL*Loader/wjq_clob.csv' 
TRUNCATE INTO TABLE tb_clob 
FIELDS TERMINATED BY ',' 
( new_id       CHAR(20), 
  new_to       CHAR(100),   
  new_subject  CHAR(100), 
  new_date     DATE "YYYY-MM-DD HH24:MI:SS" ":new_date",
  clob_filename FILLER CHAR(1000000), 
  new_content  LOBFILE(clob_filename) TERMINATED BY EOF 
)

上述控制文件的关键在于定义一个clob_filename的伪字段(SQL*Loader中就是filler)以获取clob文件名,紧接着用lobfile从该文件导入消息正文。如果需要导入blob类型的数据,其方法完全一样。

 

下面就开始执行导入操作:

[oracle@wjq ~]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_clob.ctl log=/u01/app/oracle/SQL*Loader/wjq_clob.log

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 11:43:54 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

 

下面查看一下日志信息:

Table TB_CLOB:
  2 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:                1000488 bytes(1 rows)
Read   buffer bytes: 1048576

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

Run began on Thu Nov 02 11:43:54 2017
Run ended on Thu Nov 02 11:43:54 2017

Elapsed time was:     00:00:00.05
CPU time was:         00:00:00.00

 

通过日志可以看到,导入成功,下面通过查询表中的内容来进行验证





作者:SEian.G(苦练七十二变,笑对八十一难)

阅读(1122) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

何小琴12017-11-15 18:16:26

dsfdffdklfj.ccedip.com
fdsfgfgfgfdg.ccedip.com
jfghidfdshfn.ccedip.com
fsfdsgfgfdhg.ccedip.com
gfdggdgdgfgd.b2b.chinamining.com
kogklggffh.b2b.chinamining.com
jkafhdjkfhjkdajkf.b2b.chinamining.com
13538017209.54086.com
fdg546456gfh4gfh.b2b.chinamining.com
sfhfhdoew89798.b2b.chinamining.com
fdg546456gfh4gfh.zixin.com.cn
fsfdsgfgfdhg.zixin.com.cn
dsfdffdklfjdsfk.zixin.com.cn
xgdkjkjfd.zixin.com.cn
Shop.ebdoor.com/shops/3854728/
Shop.ebdoor.com/shops/3851063/

评论热议
请登录后评论。

登录 注册