| handling errors in batch file [message #472543] |
Sat, 21 August 2010 17:13  |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
i have the similar problem . let me explain my issue.
i get flat file and i have set up a control M job so that it runs at a particular time.
initially my control file was as below:
LOAD DATA
INFILE 'DFILE\abcd.dat'
BADFILE 'BD\abcd.bad'
REPLACE
INTO TABLE abcd_table
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50) char)
this was working fine. control M did not send FAIL message.
but later i had to change the ctl file due to requirement. i had to add a when clause.
my code after modification is:
INFILE 'DFILE\abcd.dat'
BADFILE 'BD\abcd.bad'
REPLACE
INTO TABLE abcd_table
when A<>'10'
(A position(01:09) CHAR,
B position(11:12) CHAR,
C position(14:33) CHAR,
D position(37:50)
now the control M is sending an erro message after it runs the job.
error is Return Code: 5. thats all it gives.
i think it is due to errorlevel 1.
in log file it says zero records inserted due to data error. then what is causing control M to send fail message??
sqlloader is loading all the required records correctly. i dont understand what is wrong
thanks jillu
[Updated on: Sun, 22 August 2010 01:32] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: handling errors in batch file [message #472548 is a reply to message #472544] |
Sat, 21 August 2010 23:51  |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
the requirement is to load all records from flat file into table ABCD_table except records which has A=10.
the sqlldr is loading all records except A=10. which is a perfect thing. but control M is returning failure message because sqlloader is returning errorlevel 1.
now control M is a job schedular. i thought control M is famous that is why i didnot mention what it is in my question.
so if sqlloader returns error then control M sends an email with failure message.
iam using oracle 10g. sqlloader version is 8.
this is running on windows, not unix.
|
|
|
|
|
|
|
|
| Re: handling errors in batch file [message #472568 is a reply to message #472550] |
Sun, 22 August 2010 10:44 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
to blackswan :
i wrote exactly what i could see there. i have nothing more to tell.
to barbara:
i cant post the log file since its security issue. might be version is not compatible. even all the records are loaded perfectly then there is no point in getting error.
even log file does say : 0 records loaded due to data error.
|
|
|
|
| Re: handling errors in batch file [message #472571 is a reply to message #472568] |
Sun, 22 August 2010 12:26 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
All the records are loaded, but the SQL*Loader log file says that 0 rows were loaded? The only way that I can picture that happening is if you did something that caused the SQL*Loader command to run twice, so that all the records were loaded in the first run and all rows were rejected in the second run due to a primary key violation or some such thing.
You should first get the 10g version of SQL*Loader and try that. If that does not solve the problem, then try running the same thing that you run from control M from the dos command prompt and see if you still have the problem, in order to narrow down whether the problem is with SQL*Loader or control M.
If you still have a problem, then you should try to create a small test case that reproduces your problem without using any actual data, column names, or table names that would violate your security, and post that, including create table statement, data file, control file, the command that you use to run it, and log file. Only then can we reproduce the problem on our system to diagnose it. Otherwise, all we can tell you is that you must be doing something wrong. Sometimes it is the pieces that you think are unrelated and do not post that are causing the problem.
|
|
|
|
| Re: handling errors in batch file [message #472652 is a reply to message #472571] |
Mon, 23 August 2010 02:51 |
nastyjillu
Messages: 211
Registered: February 2009
|
Senior Member |
|
|
this problem is caused only after adding WHEN clause. i dont understand how the WHEN clause is causing this problem. if the probelm is due to sqlloader version 8 then why was it working perfectly before and why not now.
i will see how i solve this
|
|
|
|
|
|
|
|
| Re: handling errors in batch file [message #472914 is a reply to message #472652] |
Tue, 24 August 2010 13:13 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
nastyjillu wrote on Mon, 23 August 2010 03:51this problem is caused only after adding WHEN clause. i dont understand how the WHEN clause is causing this problem. if the probelm is due to sqlloader version 8 then why was it working perfectly before and why not now.
i will see how i solve this
As posted by Ms. Barbara Boehmer, the SQL*Loader exit codes may return "Warnings". One such warning is "All or some rows discarded" which indicates that due to the WHEN clause not all records were loaded.
Is it clear as mud now?
[Updated on: Tue, 24 August 2010 13:30] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
