ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 测试TOM=SQLLDR函数使用1

测试TOM=SQLLDR函数使用1

原创 Linux操作系统 作者:oracle_db 时间:2012-05-14 13:31:59 0 删除 编辑
一个列可以是对一个或者多个其它列引用函数的结果,类似拼接字符串那种意思,只不过这里是列孙是字符

测试
会话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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    313883