一个列可以是对一个或者多个其它列引用函数的结果,类似拼接字符串那种意思,只不过这里是列孙是字符
测试
会话1:创建新的控制文件
[oracle@oraclelinux ~]$ vi dept_load11.ctl
load data
infile *
into table dept_load
replace
fields terminated by ','
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
last_updated date 'dd/mm/yyyy',
entire_line ":deptno||:dname||:loc||:last_updated"
)
begindata
10,sales,virginia,1/5/2000
20,accounting,virginia,21/6/1999
30,consulting,virginia,5/1/2000
40,finance,virginia,15/3/2001
会话1:加载数据
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load11.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:37:30 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
会话1:查看错误日志:
[oracle@oraclelinux ~]$ cat dept_load11.log
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:37:30 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: dept_load11.ctl
Data File: dept_load11.ctl
Bad File: dept_load11.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DEPT_LOAD, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
SQL string for column : "upper(:dname)"
LOC NEXT * , CHARACTER
SQL string for column : "upper(:loc)"
LAST_UPDATED NEXT * , DATE dd/mm/yyyy
ENTIRE_LINE NEXT * , CHARACTER
SQL string for column : ":deptno||:dname||:loc||:last_updated"
Record 1: Rejected - Error on table DEPT_LOAD, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)==没等处理完所有列,记录中就没有数据了
Record 2: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table DEPT_LOAD, column ENTIRE_LINE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table DEPT_LOAD, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)
Table DEPT_LOAD:
0 Rows successfully loaded.
6 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: 82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 6
Total logical records discarded: 0
Run began on Mon May 14 13:37:30 2012
Run ended on Mon May 14 13:37:30 2012
Elapsed time was: 00:00:00.28
CPU time was: 00:00:00.08
[oracle@oraclelinux ~]$
会话1:修改控制文件
因为数据没处理完,就没记录,所以入果输入记录中,不存在某一列的数据,SQLLDR就给该列一个空值,通过
TRALING NULLCOLS 会导致绑定变量:ENTIRE_LINE会成为NULL.
[oracle@oraclelinux ~]$ cat dept_load12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_LOAD
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy',
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,finance,virginia,15/3/2001
[oracle@oraclelinux ~]$
会话1:加载数据
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load12.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 13:48:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 4
[oracle@oraclelinux ~]$ exit
exit
SQL> select * from dept_load;
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA
---------- -------------- ------------- ----------------------------- ---------
10 SALES VIRGINIA 10SalesVirginia1/5/2000 01-MAY-00
20 ACCOUNTING VIRGINIA 20AccountingVirginia21/6/1999 21-JUN-99
30 CONSULTING VIRGINIA 30ConsultingVirginia5/1/2000 05-JAN-00
40 FINANCE VIRGINIA 40financevirginia15/3/2001 15-MAR-01
SQL>
为什么会是这个效果?SQLLDR在查看控制文件中的列,并根据这些列建立绑定变量。
以没有任何函数的情况为例子,SQLLDR构建INSERT过程如下
INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )
VALUES ( :DEPTNO, :DNAME, :LOC, :LAST_UPDATED, :ENTIRE_LINE );
|| ||
|| ||
****** 解析输入量 *********
||
** *****将值给绑定变量,执行语句****
有函数的情况
INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( :DEPTNO, upper(:dname), upper(:loc), :last_updated,
:deptno||:dname||:loc||:last_updated );=====》解析=====》输入绑定到语句,并执行
上面的测试情况属于第2种有函数的情况
注:SQL能做的事,SQLLDR都可以结合做。
测试结束
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-723504/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-723504/