SQL*Loader导入大字段

要导入大字段(LOB类型)怎么办


SQL*Loader的大部分方法已经被做成一个HTML,地址.后期将不再更新SQL*Loader导入的系列方法.


LOB作为打字段数据类型,是Oracle新增的数据类型,用来替代long和long raw类型,一般SQLLDR操作中不会涉及大字段类型的操作.

1.数据保存在数据文件中.

这种方式可以按照5.3.10节中提到的方式处理,举个例子:

还是以MANAGER表为例,修改表中的REMARK列为LOB类型,在SQL*Plus命令环境中如下:



SQL> ALTER TABLE MANAGER DROP COLUMN REMARK;

Table altered.

SQL> ALTER TABLE MANAGER ADD REMARK CLOB;

Table altered.

数据文件如下:


10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
1. Ensure the effective local implementation of corporate level HR initiatives and new programs.
2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing
3. Oversee standard recruiting and procedures to ensure the company¡¯s staffing requirements are met in a timely manne
4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5. Develop, implement and oversee the training and development programs to upgrade the skills of the future challenges."|

示例代码保存为数据文件: oracledatat14.data

创建控制文件如下:



LOAD DATA
INFILE oracledata14.data "str '|\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(MGRNO,MNAME,JOB,REMARK char(100000))
BEGINDATA


控制文件保存为: ldr_case16.ctl

注意这里REMARK显式指定char(100000),因为Oracle默认所有输入字段都是char(255),如不显式指定类型和长度,一旦加载列的实际长度超出255,则数据加载就会报错:Field in data file exceeds maximum length.

执行SQLLDR加载数据.

数据成功加载!这种方式最关键的地方是必须保证REMARK列定义的长度大于数据文件中文本块的大小.

2. 数据保存在独立的文件中

这种数据相对于第一种更加常见,相应处理也更简单一些(跳过了换行符的处理),SQLLDR中提供了LOBFILE关键字,直接支持加载文件到LOB类型中.

首先再SQL*Plus命令行环境中连接数据库,创建一个新表:



SQL> CREATE TABLE LOBTBL (
FILEOWNER VARCHAR2(30),
FILENAME VARCHAR2(200),
FILESIZE NUMBER,
FILEDATA CLOB,
CREATE_DATE DATE
);

表中共有5列,分别表示文件属主,文件名,文件大小,文件内容和文件创建时间.

创建数据文件,内容如下:


2009-03-17 09:43 154 JUNSANSI /home/oracle/ldr_case1.ctl
2009-03-17 09:44 189 JUNSANSI /home/oracle/ldr_case1.log
2009-03-17 09:44 2,369 JUNSANSI /home/oracle/ldr_case2.ctl
2009-03-16 16:50 173 JUNSANSI /home/oracle/ldr_case2.log
2009-03-16 16:49 204 JUNSANSI /home/oracle/ldr_case3.ctl
2009-03-16 16:50 1,498 JUNSANSI /home/oracle/ldr_case3.log
2009-03-16 17:41 145 JUNSANSI /home/oracle/ldr_case4.ctl
2009-03-16 17:44 130 JUNSANSI /home/oracle/ldr_case4.log
2009-03-16 17:44 1,743 JUNSANSI /home/oracle/ldr_case5.ctl
2009-03-17 11:01 132 JUNSANSI /home/oracle/ldr_case5.log
2009-03-17 11:02 188 JUNSANSI /home/oracle/ldr_case6.ctl
2009-03-17 11:02 1,730 JUNSANSI /home/oracle/ldr_case6.log

示例代码保存为数据文件: oracledata15.data

创建控制文件如下:



LOAD DATA
INFILE oracledata15.data
TRUNCATE INTO TABLE LOBTBL
(
CREATE_DATE position(1:17) date 'yyyy-mm-dd hh24:mi',
FILESIZE position(*+1:24) "to_number(:FILESIZE,'99,999,999')",
FILEOWNER position(*+1:34),
FILENAME position(*+1) char(200) "substr(:FILENAME,instr(:FILENAME,'\\',-1)+1)",
FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF )
BEGINDATA

示例文件保存为控制文件:ldr_case17.ctl

这个控制文件是之前介绍示例应用的集大成者,又有定长处理,又有函数转换,唯一陌生的就是最后一行:LOBFILE就是前面提到的(FILENAME) TERMINATED BY EOF 这LOBFILE关键字,只需要指定FILENAME列,其他都是固定格式,调用时直接按此指定即可.

某些字段无值导致加载报错怎么办

比如你某天拿到了一个这样的一个数据文件:



SMITH,CLEAK,3904
ALLEN,SALESMAN,
WARD,SALESMAN,3128
KING,PRESIDENT,2523

示例代码『未实际保存』.[ora.data]

Oracle LOGO
image-1840

首先按照前面的例子去处理这些数据:



LOAD DATA
INFILE ora.data
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA


控制文件『未实际保存』.[ora.ctl]

执行后报错,错误如下:



Record 2 : Rejected - Error on table BONUS, column SQL.
Column not found before end of logical record (use TRAILING NULLCOLS)

就本例的错误信息来说,SQLLDR提示已经非常清楚:直到行结束也没发现适当的列值.

这是本例中数据文件的第二行没有提供适当的值(这一点都不稀奇,不管数据量庞大与否,DBA绝对不能期望数据文件完全满足要求,因此在编写控制文件时,也要考虑到对意外情况的处理.)

针对这一错误,SQLLDR甚至连解决方案也一并提供:使用TRAILING NULLCOLS.TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.



SMITH,CLEAK,3904
ALLEN,SALESMAN,
WARD,SALESMAN,3128
KING,PRESIDENT,2523


数据文件保存: oracledata16.data

控制文件如下:



LOAD DATA
INFILE oracledata16.data
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," TRAILING NULLCOLS
//TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.
(ENAME,JOB,SAL)
BEGINDATA


通过这个示例,我们能够得到三点体会:

  1. 执行完操作后一定要验证,就本例来说,从SQLLDR命令的执行看起来一切征程,如果不是到SQL*Plus环境中查看导入的数据,恐怕都不知道有记录未被成功导入.
  2. 一定要注意看日志,SQLLDR虽然算不上智能,但是也不傻,有时候造成错误的原因只是它不知到怎么办好,不过日志文件中一定会留下处理痕迹,不管SQLLDR命令执行是否成功,日志文件总是能告诉我们其执行的更多细节.
  3. 以上全部都是.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据