SQl LOADER无法加载数据

mradul 发表于 Dev

姆拉杜尔

我正在尝试使用sql loader adn加载数据时出现错误

Rows not loaded due to data errors.

这是控制文件

 LOAD DATA
 INFILE './data/link_data.csv'
 APPEND
 INTO TABLE STG_LINK_DATA
 when (dhcp_token != '') AND (dhcp_token != 'NULL') AND (dhcp_token != 'null')
  and (cmts_to_add != '') AND (cmts_to_add != 'NULL') AND (cmts_to_add != 'null')
  FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS 
(dhcp_token "RTRIM(LTRIM(:dhcp_token))",cmts_to_add "RTRIM(LTRIM(:cmts_to_add))")

表是-

CREATE TABLE stg_link_data (dhcp_token VARCHAR2(100), cmts_to_add VARCHAR2(4000));

我正在使用的数据就像---

 dhcp-2-1|belc-cmts-01-1,belc-cmts-02-1,belc-cmts-03-1,belc-cmts-04-1,belc-cmts-05-1,belc-cmts-08-2,belc-cmts-09-2,bele-cmts-01-1,bele-cmts-02-1,bele-cmts-03-1,bele-cmts-04-1,bele-cmts-05-1,bele-cmts-06-1,bele-cmts-10-2,bele-cmts-11-2,belf-cmts-01-1,belf-cmts-02-1,belf-cmts-03-1,belf-cmts-07-1,belf-cmts-09-1,belf-cmts-09-2,belf-cmts-10-1,belf-cmts-10-2,glen-cmts-01-1,glen-cmts-02-1
 dhcp-2-1|glen-cmts-03-1,glen-cmts-04-1,glen-cmts-04-2,ldry-cmts-01-1,ldry-cmts-03-1,ldry-cmts-03-2,lisb-cmts-01-1,lisb-cmts-02-1,lisb-cmts-03-1,lisb-cmts-03-2
 dhcp-10-5|ando-cmts-01-1,ando-cmts-02-1,ando-cmts-03-1,ando-cmts-04-1,ando-cmts-07-2,bour-cmts-01-1,bour-cmts-02-1,bour-cmts-03-1,bour-cmts-04-1,bour-cmts-05-1,bour-cmts-05-2,cwma-cmts-01-1,cwma-cmts-02-1,cwma-cmts-03-1,cwma-cmts-04-1,cwma-cmts-05-1,cwma-cmts-06-1,cwma-cmts-10-2,cwma-cmts-11-2,fare-cmts-03-1,fare-cmts-04-1,fare-cmts-06-1,fare-cmts-06-2,fare-cmts-07-1,fare-cmts-07-2
dhcp-10-5|gors-cmts-01-1,gors-cmts-02-1,gors-cmts-02-2,hava-cmts-01-1,hava-cmts-02-1,hava-cmts-02-2,lock-cmts-01-1,lock-cmts-02-1,lock-cmts-03-1,lock-cmts-03-2,neat-cmts-01-1,neat-cmts-02-1,neat-cmts-04-1,neat-cmts-04-2,ptal-cmts-01-1,ptal-cmts-02-1,ptal-cmts-03-1,ptal-cmts-04-1,ptal-cmts-04-2,ptal-cmts-05-2,rdng-cmts-10-1,rdng-cmts-11-1,rdng-cmts-12-1,rdng-cmts-13-1,rdng-cmts-14-1
 dhcp-10-5|rdng-cmts-15-1,rdng-cmts-16-1,rdng-cmts-17-1,rdng-cmts-18-1,rdng-cmts-19-1,rdng-cmts-20-1,rdng-cmts-20-2,rdng-cmts-21-1,rdng-cmts-21-2,rdng-cmts-22-1,rdng-cmts-22-2,rdng-cmts-23-1,rdng-cmts-23-2,rdng-cmts-24-1,rdng-cmts-24-2,rdng-cmts-25-2,rdng-cmts-26-2,sals-cmts-01-1,sals-cmts-03-1,sals-cmts-03-2,sket-cmts-01-1,sket-cmts-02-1,sket-cmts-03-1,sket-cmts-03-2,sotn-cmts-01-1
dhcp-10-5|sotn-cmts-02-1,sotn-cmts-03-1,sotn-cmts-04-1,sotn-cmts-05-1,sotn-cmts-06-1,sotn-cmts-07-1,sotn-cmts-14-2,sotn-cmts-15-2,sotn-cmts-16-2,sotn-cmts-17-2,swan-cmts-01-1,swan-cmts-02-1,swan-cmts-04-1,swan-cmts-04-2,swin-cmts-01-1,swin-cmts-02-1,swin-cmts-03-1,swin-cmts-04-1,swin-cmts-05-1,swin-cmts-06-1,swin-cmts-18-2,swin-cmts-19-2,swin-cmts-20-2
dhcp-9-2|bigg-cmts-01-1,bigg-cmts-02-1,bigg-cmts-03-1,bigg-cmts-03-2,hitc-cmts-01-1,hitc-cmts-02-1,hitc-cmts-03-1,hitc-cmts-04-1,hitc-cmts-05-1,hitc-cmts-06-1,hitc-cmts-06-2,hitc-cmts-07-1,hitc-cmts-07-2,kemp-cmts-01-1,kemp-cmts-02-1,kemp-cmts-03-1,kemp-cmts-04-1,kemp-cmts-05-1,kemp-cmts-06-1,kemp-cmts-06-2,kemp-cmts-07-1,kemp-cmts-07-2,stev-cmts-01-1,stev-cmts-02-1,stev-cmts-03-1
dhcp-9-2|stev-cmts-04-1,stev-cmts-05-1,stev-cmts-06-1,stev-cmts-06-2,stev-cmts-07-1,stev-cmts-07-2

我使用的sqlldr是-

sqlldr $LOGIN_NM CONTROL=load_link_data.ctl LOG=log/${FILE_LINK}.log BAD=log/${FILE_LINK}.bad DISCARD=bad/${FILE_LINK}.reject ERRORS=100000 DIRECT=TRUE PARALLEL=TRUE

我没有得到这个，因为我可以看到错误是-

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DHCP_TOKEN                          FIRST     *   |  O(") CHARACTER            
SQL string for column : "RTRIM(LTRIM(:dhcp_token))"
CMTS_TO_ADD                          NEXT     *   |  O(") CHARACTER            
SQL string for column : "RTRIM(LTRIM(:cmts_to_add))"

Record 1: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.

如果查看控制文件，则它拒绝空白字段和其他字段的记录，但是这里没有空白字段，我可能没有sql loader的这种行为

请帮忙！！

向我询问更多信息

另外，当我从控制文件中省略WHEN子句时，它给了我数据错误，表示列长度cmts_to_add超过了长度，而我给出了全长（4000字节），并且字符串最大，最大。长400

姆拉杜尔

解决了！！

新的CTL文件检查cmts_to_add列中所做的更改，只需将TRIM（）替换为CHAR（）即可将SQL LOADER的默认限制从256字节增加到4000字节:)

LOAD DATA
INFILE './data/link_data.csv'
APPEND
INTO TABLE STG_LINK_DATA
when (dhcp_token != '') AND (dhcp_token != 'NULL') AND (dhcp_token != 'null')
and (cmts_to_add != '') AND (cmts_to_add != 'NULL') AND (cmts_to_add != 'null')
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS 
(dhcp_token "RTRIM(LTRIM(:dhcp_token))",cmts_to_add CHAR(4000))

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。