- 论坛徽章:
- 0
|
我有一张表,表结构如下
create table TMP_NEW_OLD
(
CALLINGNUMBER VARCHAR2(50),
CALLEDNUMBER VARCHAR2(50),
ANSWERTIME VARCHAR2(50),
CALLDURATION NUMBER,
PARTITIONID NUMBER
)
现在要把一个文件的数据使用sqlloader插入到该表中,文件中只有4列.表中的PARTITIONID字段的取值来自文件中第3列(ANSWERTIME格式'yymmddhi24MM')的月份值.文件结构如下:
011888 13305015510 2008080107173400 5560
015705921106 13313751805 2008080116393600 680
015705922002 13313751805 2008080111085500 510
015705922002 13313776166 2008080110463400 290
015705922052 13328225178 2008080112123200 940 |
我用下面的sqlloader控制文件去插入数据库,系统居然报
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CALLINGNUMBER FIRST * WHT CHARACTER
CALLEDNUMBER NEXT * WHT CHARACTER
ANSWERTIME NEXT * WHT CHARACTER
CALLDURATION NEXT * WHT CHARACTER
PARTITIONID NEXT 1 CHARACTER
SQL string for column : "to_number(substr(:ANSWERTIME,3,2))"
Record 1: Rejected - Error on table TMP_NEW_OLD, column PARTITIONID.
Column not found before end of logical record (use TRAILING NULLCOLS)这样的错;
这是我的控制文件
Load data
infile 'temp.dat'
Append
Into table tmp_new_old
(
CALLINGNUMBER Terminated by '\t',
CALLEDNUMBER Terminated by '\t',
ANSWERTIME Terminated by '\t',
CALLDURATION Terminated by '\t',
PARTITIONID Integer external "to_number(substr(:ANSWERTIME,3,2))"
) |
|
|