Hi All,
Today I saw a very strange thing with the sqlldr. There was a difference in original control file used and the control file details in the log file. As the control file is bit large, so I am placing only the concerned part.
Control File:
OFFICEGAID4 POSITION(205:214) CHAR NULLIF OFFICEGAID4=BLANKS "TRIM(:OFFICEGAID4)",
AGENTID4 POSITION(215:222) CHAR NULLIF AGENTID4=BLANKS "TRIM(:AGENTID4)",
SHAREPCT4 POSITION(225:227) CHAR NULLIF SHAREPCT4=BLANKS "TRIM(:SHAREPCT4)",
AGTPROFCD4 POSITION(230:232) CHAR NULLIF AGTPROFCD4=BLANKS "TRIM(:AGTPROFCD4)",
AGTMKTDIV4 POSITION(233:234) CHAR NULLIF AGTMKTDIV4=BLANKS "TRIM(:AGTMKTDIV4)",
AGTSEQNUM4 POSITION(235:236) CHAR NULLIF AGTSEQNUM4='00' "TRIM(:AGTSEQNUM4)",
AGTMKTORG4 POSITION(237:238) CHAR NULLIF AGTMKTORG4=BLANKS "TRIM(:AGTMKTORG4)",
Log File:
OFFICEGAID4 205:214 10 CHARACTER
NULL if OFFICEGAID4 = BLANKS
SQL string for column : "TRIM(:OFFICEGAID4)"
AGENTID4 215:222 8 CHARACTER
NULL if AGENTID4 = BLANKS
SQL string for column : "TRIM(:AGENTID4)"
SHAREPCT4 225:227 3 CHARACTER
NULL if SHAREPCT4 = BLANKS
SQL string for column : "TRIM(:SHAREPCT4)"
AGTPROFCD4 230:232 3 CHARACTER
NULL if AGTPROFCD4 = BLANKS
SQL string for column : "TRIM(:AGTPROFCD4)"
AGTMKTDIV4 232:234 3 CHARACTER
NULL if AGTMKTDIV4 = BLANKS
SQL string for column : "TRIM(:AGTMKTDIV4)"
AGTSEQNUM4 235:236 2 CHARACTER
NULL if AGTSEQNUM4 = 0X3030(character '00')
SQL string for column : "TRIM(:AGTSEQNUM4)"
AGTMKTORG4 237:238 2 CHARACTER
NULL if AGTMKTORG4 = BLANKS
SQL string for column : "TRIM(:AGTMKTORG4)"
As you can see that the actual position was 233:234 but at run time it got changed to 232:234. Because of this Prod cycle is failing. As I have faced this issue for the first time, I am unable to proceed to how to fix this issue. The sqlldr is being called from a Shell Script.
DB version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.
Thanks,
Sudhanshu