Strange SQL Loader problem

[bheemsen]

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

 

[djbjr]

Post your Control file and a sample from the "bad" file

 

[Dagon]

Is your output from the log file or some sort of screen message ? Normally there is an ORA- message written to the log file.

 

[bheemsen]

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(UPPERCBL_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.

-Bheem

 

[LKBrwnDBA]

Check the log file, it will list each discarded row and the reason why it was skipped.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Dagon]

The rows were being rejected with errors, not discarded. If they were discarded, they would be in the discard file rather than the bad file. I already suggested looking in the log file for errors, but bheemsen claims there are no errors in the log file.

 

[LKBrwnDBA]

Unless SILENT option is selected sql*loader records the errors in the log.

Example from the manual:

SQL*Loader may also display data error messages like the following:

Record 4: Rejected - Error on table EMP
ORA-00001: unique constraint <name> violated


You can suppress these messages by specifying SILENT with one or more values.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[bheemsen]

I think errors=999 is causing the problem.

My question is this parameter error=999 for eack file or for entire SQL loader session. Looks like once this error limit is reached SQL loader is not loading all the other files after that. Is this true for all others too ?

Thanx..
-Bheem

 

[LKBrwnDBA]

Parameter errors=999 is for each file (invocation) of SQL*Loader.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb