SQL*Loader的使用总结(二)

最新推荐文章于 2021-10-30 11:35:11 发布
最新推荐文章于 2021-10-30 11:35:11 发布
阅读量127 收藏
点赞数
SQL*Loader对不同文件及格式的处理方法
1.Excel文件
 一般的Excel文件最大行数不超过65536行，说明数据处理量并不大，处理Excel的方式是将其另存为CSV格式文件,然后即可按照正常方式导入即可。

2.要加载的文件不是以逗号分隔
有两种方式可以参考：
1)修改数据文件，将分隔符替换为逗号。
2)修改控制文件，将FIELDS TERMINATED BY的值修改为实际的分隔符。

3.要加载的数据中包含分隔符
例如,要向scott.bonus表插入数据提供的数据格式如下：
SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523
修改控制文件,注意下列示例代码中的粗体字符,OPTIONALLY ENCLOSED BY参数指明定界符为双引号(CSV格式文件默认定界符就是双引号,你可以根据实际情况修改OPTIONALLY的参数值),如下所示：
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case2.ctl
    LOAD DATA
    INFILE ldr_case2.dat
    TRUNCATE INTO TABLE BONUS
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    (ENAME,JOB,SAL)

    --数据文件
    [oracle@cancer ~]$ cat ldr_case2.dat
    SMITH,CLEAK,3904
    ALLEN,"SALER,M",2891
    WARD,"SALER,""S""",3128
    KING,PRESIDENT,2523
sqlldr运行如上代码，并查询结果如下：
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case2.ctl
    Commit point reached - logical record count 4

    --查询结果
    SQL> select * from bonus;
    ENAME  JOB       SAL       COMM
    ------ --------- ---------- ----------
    SMITH  CLEAK     3904
    ALLEN  SALER,M   2891
    WARD   SALER,"S" 3128
    KING   PRESIDENT 2523
4.数据文件没有分隔符
如下的数据文件专业叫做定长字符串，sqlldr中处理定长字符串也轻而易举。针对此例，我们将控制文件修改如下：
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case3.ctl
    LOAD DATA
    INFILE ldr_case3.dat
    TRUNCATE INTO TABLE BONUS
    (
    ENAME position(1:5),
    JOB position(10:18),
    SAL position(23:26)
    )

    --数据文件
    [oracle@cancer ~]$ cat ldr_case3.dat
    SMITH    CLEAK        3904
    ALLEN    SALESMAN     2891
    WARD     SALESMAN     3128
    KING     PRESIDENT    252
position关键字用来指定列的开始和结束位置，如JOB position(10:18)是指从第10个字符开始截止到第18个字符作为ENAME列的列值。position的写法也很灵活，要实现上述功能还可以换成下列几种形式：
①position(*+2:18):直接指定数值的方式叫作绝对偏移量，如果使用*号，专业名词叫相对偏移量，表示上一个字段从哪里结束，这次就从哪里开始，相对偏移量也 可以再做运算，比如Position(*+2:15)就表示从上次结束的位置+2的地方开始。
②position(*) char(9):这种相对偏移量+类型和长度的优势在于，你只需要为第一列 指定开始位置，其他列只需要指定列长度就可以了，实际使用中比较省事。

sqlldr运行如上代码，并查询结果如下：
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case3.ctl
    Commit point reached - logical record count 4

    --查询结果
    SQL> select * from bonus;
    ENAME      JOB        SAL       COMM
    ---------- --------- ---------- ----------
    SMITH      CLEAK     3904
    ALLEN      SALESMAN  2891
    WARD       SALESMAN  3128
    KING       PRESIDENT 2523

5.数据文件中的列比要导入的表中的列少
在前面几个例子中，数文件中的列比表中的列要少的环境中演示的，这说明列少不怕，关键是看控制文件中的配置。但是如果缺少的列必须赋值又怎么办呢？只需稍改下控制文件即可，直接指定COMM列，并赋初始值0(这里仍然引用ldr_case3.dat中的数据）：
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case4.ctl
    LOAD DATA
    INFILE ldr_case3.dat
    TRUNCATE INTO TABLE BONUS
    (
    ENAME position(1:5),
    JOB position(10:18),
    SAL position(23:26),
    COMM "0"
    )
查看演示结果
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case4.ctl
    Commit point reached - logical record count 4

    --查看结果
    SQL> select * from bonus;
    ENAME   JOB        SAL       COMM
    ------- --------- ---------- ----------
    SMITH   CLEAK     3904 0
    ALLEN   SALESMAN  2891 0
    WARD    SALESMAN  3128 0
    KING    PRESIDENT 2523 0
COMM的值也可以根据其他列的值而定，修改控制文件如下
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case5.ctl
    LOAD DATA
    INFILE ldr_case3.dat
    TRUNCATE INTO TABLE BONUS
    (
    ENAME position(1:5),
    JOB position(10:18),
    SAL position(23:26),
    COMM "substr(:SAL,1,1)"
    )
sqlldr执行上述代码,结果如下，很明显发现COMM的值是根据SAL的值的第1位数字获得
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case5.ctl
    Commit point reached - logical record count 4

    --执行结果
    SQL> select * from bonus;
    ENAME   JOB       SAL     COMM
    ------- --------- ------ ----------
    SMITH   CLEAK     3904   3
    ALLEN   SALESMAN  2891   2
    WARD    SALESMAN  3128   3
    KING    PRESIDENT 2523   2
这里COMM列的值根据SAL列值而定，我们通过一个SQL中的函数substr取SAL值的第一列，赋予COMM列，当然这只是一个示例，DBA可以根据实际需求进行适当的 修改，通过SQL中的函数可以实现很多很有意思的转换，也许能够为你省下很大力气，而且如果现有函数无法实现，甚至可以通过PL/SQL编写自定义的函数，然后在sqlldr的 控制文件中调用，调用方式与系统自带函数方式完全相同，这样就可以根据需求对要加载 的列做审灵活的处理。

6.数据文件中的列比要导入的表中列多
如果数据文件中的列比要导入的表中的列少，处理的时候可能麻烦些，多了反倒更简单，针对不同情况，一般有以下两种处理方式：
方式一：修改数据文件，将多余的数据删除，不过以这种方式处理，小数据量时还可行，一旦数据文件较大，几百甚至上千兆，修改数据文件耗时耗力。
方式二：使用sqlldr中控制文件FILLER来排除不需要的列
1）演示数据文件如下
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case6.dat
    SMITH    7369   CLERK      1020   20
    ALLEN    7499   SALESMAN   1930   30
    WARD     7521   SALESMAN   1580   30
    JONES    7566   MANAGER    3195   20
    MARTIN   7654   SALESMAN   1580   30
    BLAKE    7698   MANAGER    3180   30
    CLARK    7782   MANAGER    2172   10
    SCOTT    7788   ANALYST    3220   20
    KING     7839   PRESIDENT  4722   10
    TURNER   7844   SALESMAN   1830   30
    ADAMS    7876   CLERK      1320   20
    JAMES    7900   CLERK      1280   30
    FORD     7902   ANALYST    3220   20
    MILLER   7934   CLERK      1022   10
此时我们的需求希望我们导入第1、3、4列而跳过2、5列，创建控制文件如下
  1. --控制文件
    [oracle@cancer ~]$ cat ldr_case6.ctl
    LOAD DATA
    INFILE ldr_case6.dat
    TRUNCATE INTO TABLE BONUS
    (
    ENAME position(1:6),
    TCOL FILLER position(10:13),
    JOB position(17:25),
    SAL position(28:31)
    )
sqlldr的控制文件中对列定义时支持FILLER关键字，可以用来指定过滤列，在上述控制文件中，我们就使用该关键字来过滤列，相当于第10到第13列之间的数据不导入。
事实上由于此处为定长字串，我们在控制文件中指定的position参数，己经限定了读取的内容，你甚至可以删除控制文件中TCOL FILLER position (10:13)那行。
执行sqlldr命令：
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case6.ctl
    Commit point reached - logical record count 14

    --查询结果
    SQL> select * from bonus;
    ENAME     JOB        SAL       COMM
    --------- --------- ---------- ----------
    SMITH     CLERK      1020
    ALLEN     SALESMAN   1930
    WARD      SALESMAN   1580
    JONES     MANAGER    3195
    MARTIN    SALESMAN   1580
    BLAKE     MANAGER    3180
    CLARK     MANAGER    2172
    SCOTT     ANALYST    3220
    KING      PRESIDENT  4722
    TURNER    SALESMAN   1830
    ADAMS     CLERK      1320

    ENAME    JOB        SAL       COMM
    -------- --------- ---------- ----------
    JAMES    CLERK      1280
    FORD     ANALYST    3220
    MILLER   CLERK      1022

    14 rows selected.

2）如果数据文件中字符串不是定长格式，而是通过分隔符来处理的，那控制文件中就需要注意，如数据文件如下：
  1. --数据文件
    [oracle@cancer ~]$ cat ldr_case7.dat
    SMITH,7369,CLERK,1020,20
    ALLEN,7499,SALESMAN,1930,30
    WARD,7521,SALESMAN,1580,30
    JONES,7566,MANAGER,3195,20
    MARTIN,7654,SALESMAN,1580,30
    BLAKE,7698,MANAGER,3180,30
    CLARK,7782,MANAGER,2172,10
    SCOTT,7788,ANALYST,3220,20
    KING,7839,PRESIDENT,4722,10
    TURNER,7844,SALESMAN,1830,30
    ADAMS,7876,CLERK,1320,20
    JAMES,7900,CLERK,1280,30
    FORD,7902,ANALYST,3220,20
    MILLER,7934,CLERK,1022,10
此时创建控制文件时，控制文件中就必须制定FILLER，不然列中的值可能不对应，创建控制文件如下
  1. --控制文件
    [oracle@cancer ~]$ vim ldr_case7.ctl
    LOAD DATA
    INFILE ldr_case7.dat
    TRUNCATE INTO TABLE BONUS
    FIELDS TERMINATED BY ","
    (ENAME,XCOL FILLER,JOB,SAL)
执行sqlldr命令，并查看结果
  1. --sqlldr命令
    [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case7.ctl
    Commit point reached - logical record count 14

    --查看结果
    [oracle@cancer ~]$ sqlplus scott/tiger;
    SQL> select * from bonus;
    ENAME    JOB       SAL        COMM
    -------- --------- ---------- ----------
    SMITH    CLERK     1020
    ALLEN    SALESMAN  1930
    WARD     SALESMAN  1580
    JONES    MANAGER   3195
    MARTIN   SALESMAN  1580
    BLAKE    MANAGER   3180
    CLARK    MANAGER   2172
    SCOTT    ANALYST   3220
    KING     PRESIDENT 4722
    TURNER   SALESMAN  1830
    ADAMS    CLERK     1320

    ENAME    JOB       SAL        COMM
    -------- --------- ---------- ----------
    JAMES    CLERK     1280
    FORD     ANALYST   3220
    MILLER   CLERK     1022

    14 rows selected.


本文内容参考<涂抹Oracle-三思笔记>一书，该书是基于Windows，本文引用了该书的脚本和结论的整理在Linux亲自测试通过，并对一些小问题进行了处理

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

转载于:http://blog.itpub.net/29812844/viewspace-1988869/

cuanhuai4935
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Sql Loader (sqlldr) 的用法及错误处理
enchanterblue的专栏
05-02 1万+
direct=true 错误处理方法 （关于 Oracle 的数据导入导出及 Sql Loader (sqlldr) 的用法2009-06-01 12:231，E:\>sqlldr system/dhj123@danghj control=input.ctl log=dhjSQL*Loader: Release 10.2.0.1.0 - Production on 星期一 6月 1 11:38:1
SQL*LOADER错误总结
萧十一郎2022
03-01 5025
SQL*LOADER错误总结 2015-12-09 22:51 by 潇湘隐者,4900阅读,0评论,收藏,编辑 在使用SQL*LOADER装载数据时，由于平面文件的多样化和数据格式问题总会遇到形形色色的一些小问题，下面是工作中累积、整理记录的遇到的一些形形色色错误。希望能对大家有些用处。（今天突然看到自己以前整理的这些资料，于是稍稍整理、归纳成这篇博客，后面如果碰到其他案...
【SQL*Loader】SQL*Loader实验之（5）--固定长字符串数据处理时position的3种写法
cmy19760818的博客
06-16 902
【实验内容】这个实验拿实验（4）的数据文件来稍作修改进行这个实验。 要加载的数据没有分隔符，而是固定长字符串时，可以在SQL*Loader的控制文件中通过position关键字用来指定列的开始和结束位置。position通常有...
python调用sqlldr_加载非常大的日志文件时出现Oracle SQL加载程序错误
weixin_39883286的博客
12-06 751
我有一个python脚本，它遍历目录中的日志文件，并使用oraclesqlloader将日志文件加载到oracle数据库中。脚本正常工作，甚至sqlloader。。在但是在加载了大约20万条记录后，加载失败，但出现了这个异常。。在Record 11457: Rejected - Error on table USAGE_DATA.ORA-12571: TNS:packet writer failu...
Microsoft SQL Server 2008出现错误：926
凌晨de太阳
11-04 1万+
最近因为自己的误操作，致使sqlserver2008出现926的错误。从网上查找了一些解决方法，请教了一些技术大神，成功解决了这个问题。简单地做一些总结，希望能帮助到遇到同样问题的”猿“友们。 从网上查找的解决步骤： 1 停止sqlserver服务,备份msdb数据库 2 将数据库设置为单用户模式   启动sqlserver服务,将数据库设置为单用户模式   ALTER DATA
SQL LOADER错误小结
12-15
在使用SQL*LOADER装载数据时，由于平面文件的多样化和数据格式问题总会遇到形形色色的一些小问题，下面是工作中累积、整理记录的遇到的一些形形色色错误。希望能对大家有些用处。（今天突然看到自己以前整理的这些资料，于是稍稍整理、归纳成这篇博客，后面如果碰到其他案例，会陆陆续续补充在此篇文章。） ERROR 1：SQL*LOADER装载数据成功，但是发现某些字段的中文为乱码，这个是因为编码缘故造成乱码。可在控制文件中添加字符集编码参数解决问题， 例如：CHARACTERSET ‘ZHS16GBK’ 或 CHARACTERSET ‘UTF8’，根据数据库实际情况设置数据库字符集。 LOAD DA
Linux中sql*loader-350,SqlLoader
weixin_32075603的博客
05-17 4523
Sqlloader的步骤1) Oracle 数据库端必须已经建好了需要导入的数据表的结构2) 存在数据源文件3) 手工编辑一个XXX.CTL 的控制文件4) 命令行加载数据Sqlldr命令具体信息如下图Sqlldr运行的一个具体例子sqlldr userid=user1/123456control=bcp1.ctl log=log/bcp1.log bad=bad/bcp1.bad discard...
使用SQL＊Loader将dBASE数据转储到ORACLE数据库.pdf
最新发布
01-04
总结来说，SQL*Loader是将非Oracle格式数据，如dBASE，导入Oracle数据库的强大工具。它具有广泛的格式支持和强大的错误处理能力，使得数据迁移过程变得简单易行。通过合理的规划和使用，可以极大地提高数据迁移的...
SQL*Loader的使用总结(一)
热门推荐
不忘初心、方得始终
01-27 3万+
SQL*Loader是oracle提供的可以从多种平面文件中向数据库中加载数据的工具，使用SQLLDR工具可以在很短的时间内向数据库中加载大量的数据，像把制作好的excel表格导入数据库，可以说非常方便，相关的数据加载和卸载工具还有外部表，IMP/EXP，数据泵等，其实呢~ 关于SQL*Loader的学习多数时间是花在了琢磨sqlldr控制文件的写法上，下面来总结我的SQL*Loader学习过程和
SQL*Loader使用指南
wotiger的专栏
12-10 1471
Oracle 的SQL*LOADER可以将外部数据加载到数据库表中。下面是SQL*LOADER的基本特点： 1）能装入不同数据类型文件及多个数据文件的数据2）可装入固定格式，自由定界以及可度长格式的数据3）可以装入二进制，压缩十进制数据4）一次可对多个表装入数据5）连接多个物理记录装到一个记录中6）对一单记录分解再装入到表中7）可以用 数对制定列生成唯一的KEY
Oracle SqlLoader使用
一天不进步，就是退步.
09-14 1万+
在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法： 1. A 表的记录导出为一条条分号隔开的 insert 语句，然后执行插入到 B 表中 2. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where ...，或 insert into B select * from A@
SQL*Loader的使用总结(四)
不忘初心、方得始终
01-28 2万+
SQL*Loader对不同文件及格式的处理方法 1.大字段(LOB类型)的导入 LOB作为大字段数据类型，是Oracle新增的数据类型，用来替代long和long raw类型，一般sqlldr操作中不会涉及大字段类型的操作，如果你遇到了这种需求，一般分以下两种情况处理： 1）数据保存在数据文件中 以Manager表为例，修改Remark字段为lob类型 SQL> alter table
sqlldr详解2
cuixie2370的博客
06-08 183
8 ***** 载入每行的行号load datainfile *into table treplace( seqno RECNUM //载入每行的行号text Position(1:1024))BEGINDATAfsdfasj ...
sql*loader
weixin_39259749的博客
10-30 5360
简介 SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式。 传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。 直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。 利用直接路径加载，你能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个
SQL*Loader报错的排查解决方案
08-23 669
“在使用SQL*Loader过程中是否碰到过ORA-03135: 连接失去联系的错误？是否碰到过ORA-03137: TTC 协议内部错误 的错误？该篇文章也许可以解决你的问题。” 本文为原创内容，首发微信公众号：唯一的小彬哥 某日甲方爸爸直接联系到我反馈SQL*Loader读文件报错已经持续很久。现象是每次读多个文件都会有一两个文件报错，但是对报错的文件重新读取又可以正常处理。 排查SQL*Loader导入日志 接到甲方爸爸的反馈后第一时间向客户要了...
direct=true 错误处理方法 （关于 Oracle 的数据导入导出及 Sql Loader (sqlldr) 的用法
lizhongren的专栏
04-19 8840
1， E:\>sqlldr system/dhj123@danghj control=input.ctl log=dhj SQL*Loader: Release 10.2.0.1.0 - Production on 星期一 6月 1 11:38:10 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL*
Oracle SQL*Loader：高效加载外部数据详解
总结来说，Oracle的SQL*Loader是数据导入的重要工具，通过合理的控制文件配置，可以高效、灵活地处理大规模的数据导入任务。了解其工作原理和参数设置，对于数据库管理员和数据分析师来说是至关重要的技能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值