CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-loader
mradul
mradul

Reputation: 560

SQl LOADER fail to load data

I am trying to load data using sql loader adn when i run it gives me error 

Rows not loaded due to data errors.

here is control file 

 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))")

tables is --

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

and data i am using is like ---

 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

and sqlldr i ma using is ---

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

I am not getting this as i can see the error is like --

   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.

if look at the control file then it reject records for blank fields and others but here there are no blank fields i ma not getting this behavior of sql loader

Please help !!

Ask me for more information please

Also when i ommit the WHEN clause from control file then it gives me data error says column length cmts_to_add is exceeding length whereas i have given it full length (4000 bytes) and strings are of max, to max. 400 in length

Upvotes: 2

Views: 3045

Answers (1)

mradul
mradul

Reputation: 560

Solved !!

New CTL file check cmts_to_add column for the change i made just replace TRIM() with CHAR() to increase the default limit of SQL LOADER from 256 bytes to 4000 bytes :)

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))

Upvotes: 1

Related Questions