Question

请教SQL LOAD 中的“OPTIONALLY ENCLOSED BY”

Answer 1

　　1、准备工作：创建需要导入数据的表结构，如果已有相关的表，此步省略！
　　SQL> create table test
　　2 (
　　3 host VARCHAR2(30),
　　4 user_name VARCHAR2(30),
　　5 ip_address VARCHAR2(15),
　　6 pass VARCHAR2(4) default 'no' not null,7 judge NUMBER default 0 not null,8 endtime DATE
　　9 );
　　表已创建。
　　2、编写sqlload导入数据的控制文件，这里测试的控制文件如下，可以根据自己需要添加相关的控制参数，测试的话复制保存为txt文件即可！
　　LOAD DATA
　　INFILE 'd:\data.txt'
　　INTO TABLE test
　　TRUNCATE
　　fields terminated by ','
　　trailing nullcols
　　(HOST,USER_NAME,IP_AddrESS,PASS,JUDge,endTIME)控制文件还有其他参数，根据自己需求调整和测试：
　　附部分控制参数：具体用法以官方文档为准
　　OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行LOAD DATA
　　INFILE "users_data.csv" --指定外部数据文件，可以是不同格式的数据文件，如csv、txt都支持可以写多个 INFILE "another_data_file.csv" 指定多个数据文件truncate --操作类型，用 truncate table 来清除表中原有记录，根据情况而定是否需要清楚原有表中数据INTO TABLE users --要插入记录的表Fields terminated by "," --数据中每行记录用 "," 分隔Optionally enclosed by '"' --数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时trailing nullcols --表的字段没有对应的值时允许为空(
　　virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号user_id number, --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示user_name,
　　login_times,
　　last_login DATE "-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换)
　　insert --为缺省方式，在数据装载开始时要求表为空append --在表中追加新记录
　　replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录3、创建需要导入的数据，注意数据格式必须和表结构严格对应，否则导入失败！测试数据如下：有部分数据最后字段为空，所以控制文件中需要加trailing nullcols 参数！
　　ttt,SCOTT,192.168.1.111,yes,1,
　　,JACK,192.168.1.20,no,1,
　　,TOM,192.168.1.20,no,1,
　　WEB1,HAHA,192.168.1.1,no,1,
　　XXX,ROBIN,111.111.111.111,no,1,08-AUG-08
　　DB2,LUCY,192.168.10.10,no,1,
　　ORACLE,LILY,222.222.222.222,no,1,
　　WORKGROUP,DENNIS,133.133.133.133,no,0,08-AUG-08DCR,CANDY,192.168.100.10,no,1,
　　T3,FLY,192.168.10.33,no,1,
　　T1,LINDA,192.168.10.200,no,1,08-AUG-08
　　T2,LILEI,192.168.100.31,no,1,08-AUG-08
　　4、导入数据-导入时也有相关的参数进行控制
　　附部分导入参数：各参数可以再命令行下输入sqlldr查看C:\Documents and Settings\Administrator>sqlldrSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:13:24 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
　　用法: SQLLDR keyword=value [,keyword=value,...]
　　有效的关键字:
　　userid -- ORACLE 用户名/口令
　　control -- 控制文件名
　　log -- 日志文件名
　　bad -- 错误文件名
　　data -- 数据文件名
　　discard -- 废弃文件名
　　discardmax -- 允许废弃的文件的数目 (全部默认)skip -- 要跳过的逻辑记录的数目 (默认 0)
　　load -- 要加载的逻辑记录的数目 (全部默认)errors -- 允许的错误的数目 (默认 50)rows -- 常规路径绑定数组中或直接路径保存数据间的行数(默认: 常规路径 64, 所有直接路径)
　　bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000)silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)direct -- 使用直接路径 (默认 FALSE)parfile -- 参数文件: 包含参数说明的文件的名称parallel -- 执行并行加载 (默认 FALSE)file -- 要从以下对象中分配区的文件
　　skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE)skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE)readsize -- 读取缓冲区的大小 (默认 1048576)external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)
　　columnarrayrows -- 直接路径列数组的行数 (默认 5000)streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)multithreading -- 在直接路径中使用多线程
　　resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE)resumable_name -- 有助于标识可恢复语句的文本字符串resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200)date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE)如下命令：control=指定控制文件和路径 log=导入日志文件保存 bad=错误信息 data=数据文件开始导入：
　　C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txtSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:06:52 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
　　达到提交点 - 逻辑记录计数 12
　　导入成功后查看结果：
　　C:\Documents and Settings\Administrator>sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:07:05 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.
　　连接到:
　　Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select * from test;
　　HOST USER_NAME IP_ADDRESS PASS JUDGE ENDTIME---------- ---------- ------------------------------ -------- ---------- --------------ttt SCOTT 192.168.1.111 yes 1 JACK 192.168.1.20 no 1 TOM 192.168.1.20 no 1WEB1 HAHA 192.168.1.1 no 1DB2 LUCY 192.168.10.10 no 1ORACLE LILY 222.222.222.222 no 1DCR CANDY 192.168.100.10 no 1T3 FLY 192.168.10.33 no 1已选择8行。
　　结果显然与数据文件不一致，最后一个字段有值的数据没有导入！这不是最后想要的结果！但可以确定已经有数据导入，表示表对象和控制文件没问题！
　　应该是数据文件的格式问题！注意数据文件最后一个字段是日期型数据。查看是不是数据库现有日期类型不支持数据文件的表示格式SQL> select sysdate from dual;
　　SYSDATE
　　--------------
　　27-2月 -13
　　显然和我们数据文件的日期显示不一致，且是中文，这里可以把数据文件的最后字段的数据改成和数据库一样的格式，也可以对数据库的格式和语言进行更改，因为测试系统是windows才会出现这个错误，在linux可以避免！
　　如果是linux 可以尝试以下步骤解决：
　　alter system set nls_date_format='DD-MON-RR';alter system set nls_language= american scope = spfile;------------得重启数据库这里由于是windows，这里就不更改语言和重启数据库，直接对数据文件进行更改，修改后的数据文件如下ttt,SCOTT,192.168.1.111,yes,1,
　　,JACK,192.168.1.20,no,1,
　　,TOM,192.168.1.20,no,1,
　　WEB1,HAHA,192.168.1.1,no,1,
　　XXX,ROBIN,111.111.111.111,no,1,08-5月 -08DB2,LUCY,192.168.10.10,no,1,
　　ORACLE,LILY,222.222.222.222,no,1,
　　WORKGROUP,DENNIS,133.133.133.133,no,0,08-5月 -08DCR,CANDY,192.168.100.10,no,1,
　　T3,FLY,192.168.10.33,no,1,
　　T1,LINDA,192.168.10.200,no,1,08-5月 -08
　　T2,LILEI,192.168.100.31,no,1,08-5月 -08
　　再导入一次，导入后进行查看结果
　　C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txtSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:48:44 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
　　达到提交点 - 逻辑记录计数 12
　　C:\Documents and Settings\Administrator>sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:49:21 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.
　　连接到:
　　Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> col host for a10
　　SQL> col user_name for a15
　　SQL> select * from test;
　　HOST USER_NAME IP_ADDRESS PASS JUDGE ENDTIME---------- --------------- ------------------------------ -------- ---------- --------------ttt SCOTT 192.168.1.111 yes 1 JACK 192.168.1.20 no 1 TOM 192.168.1.20 no 1WEB1 HAHA 192.168.1.1 no 1XXX ROBIN 111.111.111.111 no 1 08-5月 -08DB2 LUCY 192.168.10.10 no 1ORACLE LILY 222.222.222.222 no 1WORKGROUP DENNIS 133.133.133.133 no 0 08-5月 -08DCR CANDY 192.168.100.10 no 1T3 FLY 192.168.10.33 no 1T1 LINDA 192.168.10.200 no 1 08-5月 -08T2 LILEI 192.168.100.31 no 1 08-5月 -08已选择12行。
　　恢复正常：数据和数据文件完全一样！到此整个数据导入完成！主要注意的地方还是数据文件的建立，确认分隔以及和表的结构对应！