2

I have multiple when clauses in my control file, the data that i am loading in half of them satisfies the when clauses and gets inserted into the desired table. The other half arent (which i expect) but i was expecting the data that doesnt meet the when conditions to be placed into a discard file but there is none created.

Any ideas?

LOAD DATA
INFILE '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/CSSO_CCRBSCREDENTIALS_COMSUMER23062010160322.txt'
BADFILE '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/CSSO_CCRBSCREDENTIALS_COMSUMER23062010160322.bad'
DISCARDFILE '/u04/app/vpht_app/flat_files/icr_load/marc/sqlldr_load/CSSO_CCRBSCREDENTIALS_COMSUMER23062010160322.dsc'
INSERT

INTO TABLE "DCVPAPP"."RBS_CC_CUSTOMERINFO"
INSERT
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(CC_USER_NAME POSITION(24:73),
ACCOUNTID POSITION(1:12),
CUSTOMERID POSITION(14:22))

INTO TABLE "DCVPAPP"."RBS_CC_SECURITYDETAILS"
WHEN (481:481) = 'N' AND (477:479) ='0'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
CC_USER_NAME POSITION(24:73),
RBSPIN POSITION(75:274),
RBSPASSWORD POSITION(276:475),
fill1 filler,
fill2 filler,
fill3 filler,
fill4 filler,
FAILCODECOUNT POSITION(477:479),
FAILPASSWORDCOUNT POSITION(477:479)
)

INTO TABLE "DCVPAPP"."RBS_CC_SECURITYDETAILS"
WHEN (481:481) = 'N' AND (477:479) ='1'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
CC_USER_NAME POSITION(24:73),
RBSPIN POSITION(75:274),
RBSPASSWORD POSITION(276:475),
fill1 filler,
fill2 filler,
fill3 filler,
fill4 filler,
FAILCODECOUNT POSITION(477:479),
FAILPASSWORDCOUNT POSITION(477:479)
)

My table structure is:

Create table RBS_CC_CUSTOMERINFO
(
CC_USER_NAME VARCHAR2(50),
ACCOUNTID VARCHAR2(12) NOT NULL,
CUSTOMERID VARCHAR2(9) NOT NULL,
CUST_MIGRATION_STATUS VARCHAR2(1) DEFAULT 'N' NOT NULL,
CONSTRAINT pk_01 PRIMARY KEY (CC_USER_NAME)
);

Create table RBS_CC_SECURITYDETAILS
(
CC_USER_NAME VARCHAR2(50),
RBSPIN VARCHAR2(200) NOT NULL,
RBSPASSWORD VARCHAR2(200) NOT NULL,
FAILCODECOUNT NUMBER (9) NOT NULL,
FAILPASSWORDCOUNT NUMBER (9) NOT NULL,
CONSTRAINT pk_secur
FOREIGN KEY (CC_USER_NAME)
REFERENCES RBS_CC_CUSTOMERINFO(CC_USER_NAME)
)

and my sample data is below( These have been right padded since these are fixed fields) the last record should be discarded and placed in side the discard file since it doesnt meet any of the when clause conditions, but no discard file is created. I have tried it with one when clause and the discard file is created,seems using more than one table the discard file isnt created.

ACC000000001,CUSTID213,MARC_VAF ,1234 ,pet ,0 ,N,N,FULL
ACC000000002,CUSTID214,TOBY_123 ,1352 ,bailey ,1 ,Y,N,FULL
ACC000000003,CUSTID215,KEVIN_VAF81 ,YY33OF ,water ,2 ,Y,N,FULL
ACC000000015,CUSTID227,SAM_EGD ,CARRY42 ,some password ,-3 ,Y,N,FULL

Thanks

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104
user123
  • 21
  • 1
  • 1
  • 2

1 Answers1

0

I used SQL*Loader on your sample data, and found the following in the log file that SQL*Loader left behind:

Table "DCVPAPP"."RBS_CC_CUSTOMERINFO":
  4 Rows successfully loaded.      
  0 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.

Table "DCVPAPP"."RBS_CC_SECURITYDETAILS":
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  4 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Table "DCVPAPP"."RBS_CC_SECURITYDETAILS":
  0 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  4 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

In the first block, all the data was loaded because there were no WHEN clauses to fail. With the other two, all rows failed the WHEN clauses. Since the first block loaded all four rows, there was nothing to write to the discard file, so SQL*Loader didn't create one.

The WHEN clauses in the second two blocks seem to reference parts of the data a long way off the end of your sample data. They both appear to use data from positions 477 onwards, whereas the longest line in your sample data is only 68 characters long. Since each field has at most one trailing space, I'll assume that your sample data has somehow got mangled and that there should be many more spaces than as above.

Anyway, I commented out the section of your controlfile that inserts into RBS_CC_CUSTOMERINFO, emptied the tables and reran SQL*Loader. This time, all four rows were written to the discard file.

If you want data that matches neither of the two WHEN clauses to be written to a discard file, how about splitting the controlfile into two separate controlfiles, one which loads the data into RBS_CC_CUSTOMERINFO using the first block, and one which loads the data into RBS_CC_SECURITYDETAILS using the other two blocks?

Luke Woodward
  • 63,336
  • 16
  • 89
  • 104