Home » RDBMS Server » Server Utilities » handling errors in batch file
handling errors in batch file [message #472543] Sat, 21 August 2010 17:13 Go to next message
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 #472544 is a reply to message #472543] Sat, 21 August 2010 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 22508
Registered: January 2009
Senior Member
>i dont understand what is wrong
Neither do I because I don't understand what is considered correct/desired & what is in error.

Why do you not use EXTERNAL TABLE?

> i have set up a control M job
What exactly is a CONTROL M job?

ALWAYS
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version;
Re: handling errors in batch file [message #472548 is a reply to message #472544] Sat, 21 August 2010 23:51 Go to previous messageGo to next message
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 #472549 is a reply to message #472548] Sun, 22 August 2010 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 22508
Registered: January 2009
Senior Member
You claim a problem.
Since you decide to not answer my question, I'll withdraw & allow others to solve this mystery.
If you desire a quick solution, tell us how to reproduce your situation.
Re: handling errors in batch file [message #472550 is a reply to message #472548] Sun, 22 August 2010 00:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
If you are using Oracle 10g, then you should be using the corresponding version of SQL*Loader, not 8. Your problem may be due to some incompatibility. The SQL*Loader exit codes for the current version are here:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10701/ldr_params.htm#i1005019

You should check and post your SQL*Loader log file to see if there is something you are missing. Other than that, your problem may be with how control M interprets the exit code. If that is the problem, then a possible workaround may be to use a when clause to load the rows where A='10' into a separate table, which can then be truncated.


Re: handling errors in batch file [message #472568 is a reply to message #472550] Sun, 22 August 2010 10:44 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
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 Go to previous messageGo to next message
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 #472755 is a reply to message #472652] Mon, 23 August 2010 13:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Are you providing the actual complete situation? Did you only add one when clause or multiple when clauses? If you added more than one when clause without resetting the first position, then that would explain it. You have been told what to do and what to post to get help. If you refuse to do that, then nobody can help you.
Re: handling errors in batch file [message #472800 is a reply to message #472755] Tue, 24 August 2010 00:15 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i added only one WHEN condition. yaa i have been asked. but i can post only this much.

i have my restrictions.
Re: handling errors in batch file [message #472914 is a reply to message #472652] Tue, 24 August 2010 13:13 Go to previous message
LKBrwn_DBA
Messages: 425
Registered: July 2003
Location: WPB, FL
Senior Member
nastyjillu wrote on Mon, 23 August 2010 03:51
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


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

Previous Topic: How to concatenate 2 fields
Next Topic: SQL Loader error
Goto Forum:
  


Current Time: Sat Jul 26 08:44:33 CDT 2014

Total time taken to generate the page: 0.07931 seconds