Home » RDBMS Server » Server Utilities » Strange SQL Loader problem
Strange SQL Loader problem [message #192542] Tue, 12 September 2006 16:13 Go to next message
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 #192543 is a reply to message #192542] Tue, 12 September 2006 16:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone explain me what's going on here.
Yes, you are posting to the wrong forum.
Re: Strange SQL Loader problem [message #192547 is a reply to message #192542] Tue, 12 September 2006 18:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try removing >/dev/null 2>/dev/null
and review the contents of $Log
Re: Strange SQL Loader problem [message #193008 is a reply to message #192542] Thu, 14 September 2006 09:36 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

Could you post the control files what is contains.

Thanks,

Re: Strange SQL Loader problem [message #193055 is a reply to message #193008] Thu, 14 September 2006 13:02 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Need help in importing data from csv
Next Topic: when to use SQL loader
Goto Forum:
  


Current Time: Sat Dec 10 05:05:02 CST 2016

Total time taken to generate the page: 0.06478 seconds