| Strange SQL Loader problem [message #192542] |
Tue, 12 September 2006 16:13  |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Strange SQL Loader problem.
We are encountering a strange SQL Loader problem.
Here are the details. We are using the following oracle version.
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
OS - Unix IBM AIX
The sqlldr is invoked from within a Unix shell script using the following command.
sqlldr PARFILE=$conn_file data=$Dat control=$Ctl log=$Log bad=$Bad discard=$Dsc errors=999 direct=true >/dev/null 2>/dev/null
We are loading multiple files at the same time into one table.
After loading few files, SQL Loader says "Rows not loaded due to data errors." No other error. Please see below.
74265 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.
39138 Rows successfully loaded.
1000 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.
Errors in Load
0 Rows successfully loaded.
400 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.
Errors in Load
Same with all other files followed after this.
Can anyone explain me what's going on here.
Thanks
-Bheem
|
|
|
|
|
|
|
|
|
|
| Re: Strange SQL Loader problem [message #193055 is a reply to message #193008] |
Thu, 14 September 2006 13:02  |
bheemsen
Messages: 13
Registered: February 2006
|
Junior Member |
|
|
Here is a sample of data file.
616399|6163990404|1|COPPER|26|1.657|
616399|6169940058|1|COPPER|22|0.016|
616399|6169940058|2|COPPER|22|2.868|
616399|6169940616|1|COPPER|24|3.320|
616399|6169940616|2|COPPER|26|1.173|
616399|6167862498|1|COPPER|24|1.408|
616399|6167862498|3|COPPER|26|0.230|
616399|6167862498|2|COPPER|24|3.562|
616399|6169940043|1|COPPER|24|0.038|
Here is a sample of bad file for the above data file.
616399|1000027675|1|COPPER|26|2.466|
616399|1000027676|1|COPPER|26|2.466|
616399|1000027677|1|COPPER|26|2.466|
616399|1000027678|1|COPPER|26|2.466|
616399|1000027679|1|COPPER|26|2.466|
616399|1000027680|1|COPPER|26|2.466|
616399|1000027681|1|COPPER|26|2.466|
616399|1000034014|1|COPPER|26|3.450|
616399|1000034015|1|COPPER|26|3.450|
616399|1000034016|1|COPPER|26|3.450|
Here is my control file.
LOAD DATA
APPEND
INTO TABLE FF_CABLE_PAIR
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(WIRE_CNTR_CD CHAR,
CIRCUIT_ID CHAR,
CBL_PR_ID CHAR,
CBL_PR_TYPE CHAR "TRIM(UPPER(:CBL_PR_TYPE))",
CBL_PR_GAUGE_NUM CHAR,
CBL_PR_LENGTH CHAR)
There are no other messages and table has no primary key. This is happening every once in a while. The output I had provided in my original post is from the log file.
I think the parameter errors=999 is causing the problem here. Once the error limit is reached the SQL loader is not loading all other files. Is this normal and correct ?
-Bheem
|
|
|
|
| Re: Strange SQL Loader problem [message #193285 is a reply to message #193055] |
Fri, 15 September 2006 10:03 |
goudelly
Messages: 52
Registered: August 2006
Location: India
|
Member |
|
|
Hi,
options(direct=true)
LOAD DATA
infile 'd:\abcd.txt'
badfile 'd:\abcd.bad'
discardfile 'd:\abcd.dsc'
into table FF_CABLE_PAIR
fields terminated by "|"
trailing nullcols
(WIRE_CNTR_CD,
CIRCUIT_ID,
CBL_PR_ID,
CBL_PR_TYPE,
CBL_PR_GAUGE_NUM,
CBL_PR_LENGTH)
Try this one first with single text file. (Change the filednames according to your table)
Thanks,
Mohan Reddy G
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
