azsat
asked on
SQLLDR EXIT CODE FOLLOWING DISCARDS
Hi,
I have a sqlldr statement and an associated control file which works successfully loading
100 rows and discarding two as per the WHEN clause on my LOAD DATA statement in my control file. I also have a log file and discard file where the corect information is logged.
However, when a DISCARD is performed the sqlldr utility is completing with a non zero return code of EX_FAIL, that is 1, if there has been any discards. My discards are delibrate and the desired data has been loaded so I would like to see a successful completion code.
Is there any way around this because the calling script is returning 1 giving the impression that there has been a failure. Please Help!
I have a sqlldr statement and an associated control file which works successfully loading
100 rows and discarding two as per the WHEN clause on my LOAD DATA statement in my control file. I also have a log file and discard file where the corect information is logged.
However, when a DISCARD is performed the sqlldr utility is completing with a non zero return code of EX_FAIL, that is 1, if there has been any discards. My discards are delibrate and the desired data has been loaded so I would like to see a successful completion code.
Is there any way around this because the calling script is returning 1 giving the impression that there has been a failure. Please Help!
ASKER
Hi,
You are right in saying that I've probably posted the question in the wrong forum.
I did not realise this at the time!
However, I get the impression you are sugesting the I trying setting the ERRORS and DISCARD cmd line parameters to some high values say 9999 , this may I guess then prevent a failure exit code. However we must note that I've only got 2 discards in my file and sqlldr still exits with failure code 1.
This will remain a problem as my situation implies that one cannot do legitimate DISCARDS without getting a failure return code. Any comment?
You are right in saying that I've probably posted the question in the wrong forum.
I did not realise this at the time!
However, I get the impression you are sugesting the I trying setting the ERRORS and DISCARD cmd line parameters to some high values say 9999 , this may I guess then prevent a failure exit code. However we must note that I've only got 2 discards in my file and sqlldr still exits with failure code 1.
This will remain a problem as my situation implies that one cannot do legitimate DISCARDS without getting a failure return code. Any comment?
I think so. I guess you are running from some other program (shell script or java program).
If doing this from java (with a Runtime Process object), you can read the output and error messages from the process, and determine on your own if it is a success or failure.
Alternatively, you could load into a staging table, and check your data enough there would be no discards at all, then call another process to validate/insert from the staging table into your actual tables.
If doing this from java (with a Runtime Process object), you can read the output and error messages from the process, and determine on your own if it is a success or failure.
Alternatively, you could load into a staging table, and check your data enough there would be no discards at all, then call another process to validate/insert from the staging table into your actual tables.
ASKER
Hi
I am indeed calling the sqlldr from a perl script. I've tried the ERRORS =99999 and DISCAEDMAX=99999 and unfortunately, I still get a EXT_FAIL return code.
The log clearly shows that 2 recs where correctly discarded as expected and the remaing records were loaded OK.
I'm a newbie to ORACLE and as such can't think of any other way of tinkering the sqlldr command/control file in order to get Oracle to accept the discards as legitimate.
I get the impression you are somwwhat more experience and would under the circumstances, appreciate any suggested workround.
Regards,
azsat.
I am indeed calling the sqlldr from a perl script. I've tried the ERRORS =99999 and DISCAEDMAX=99999 and unfortunately, I still get a EXT_FAIL return code.
The log clearly shows that 2 recs where correctly discarded as expected and the remaing records were loaded OK.
I'm a newbie to ORACLE and as such can't think of any other way of tinkering the sqlldr command/control file in order to get Oracle to accept the discards as legitimate.
I get the impression you are somwwhat more experience and would under the circumstances, appreciate any suggested workround.
Regards,
azsat.
Well, it looks like sqlldr will return an error code if there are any discards at all. That's way down in the binary, and I don't see how you can change this. But you can change your programs response to the error code. I don't have any sqlldr log files handy, but what I would do in your case, is, if I get a returned error code, open the log file and parse it. There should be a message saying it completed (if it hits discardmax, it will have a failure message). If the success message is there, display the load statistics, (munge them out with a regex - I hear perl's pretty good at that ;). Bottom line for this approach is, don't accept the return as the final answer - keep processing to determine if this is a real failure or merely few discards. Most of us don't want to lose data, so we'd want to know about the discards anyway, which is probably why it returns a 1 instead of a zero.
Other possible solution is to take some intermediate steps to see if you can eliminate the possiblity of discards during the load - either by loading to a temp staging table with no indexing or constraints, or by agressively validating data and formats.
Other possible solution is to take some intermediate steps to see if you can eliminate the possiblity of discards during the load - either by loading to a temp staging table with no indexing or constraints, or by agressively validating data and formats.
ASKER
Thanks for that.
By doing a shift eight on the return code - i'm told i've got to do this in perl (ie $exit_value = $? >> 8;) , I've manged to get the TRUE exit code which is infact 255 - this is being returned from Oracle. Does this throw any more light ??
Regards
azsat.
By doing a shift eight on the return code - i'm told i've got to do this in perl (ie $exit_value = $? >> 8;) , I've manged to get the TRUE exit code which is infact 255 - this is being returned from Oracle. Does this throw any more light ??
Regards
azsat.
ASKER
Sorry that's not 255 (my typo error ) it's a value 2 !!!! which I believe is an ORACLe warning ? hey what can we do with this?
Regards,
Azeem.
Regards,
Azeem.
OK - that makes it easier. A lot of Oracle applications return 1=Error 2=Warning 0=Success
To confirm this is the case for sqlldr, try to load a bad file (like an excel file or something, and check the return code.
To confirm this is the case for sqlldr, try to load a bad file (like an excel file or something, and check the return code.
ASKER
Thanks will do. I'm in London so ' won't be ale to respond with the results before 9:30 GMT.
Regards,
azsat.
Regards,
azsat.
ASKER
Hello,
The retun code of 2 appears to be genuine. A good file returns 0 and a
a corrupted file however, returns 2 as well - in this case everything is rejected and written to a BAD file. I've not been able to produce a failure ie code 3 !!!
So the problem is distiuguishing between a load with legitimate discards that has returned 2 and a total reject load that has produced a BAD file.
I guess the answer is there a bad file is only produced if there are errors otherwise it can only be a clean load or clean load with discards.
I must admit I'm a newbie to perl and would not be able to comfortably write the bits required to parse any outputs logs for messages. However, I guess a bad file will only exist if there are errors and I can easily test for its existence in perl.
I'll await your valued comments before finalising this one up.
The retun code of 2 appears to be genuine. A good file returns 0 and a
a corrupted file however, returns 2 as well - in this case everything is rejected and written to a BAD file. I've not been able to produce a failure ie code 3 !!!
So the problem is distiuguishing between a load with legitimate discards that has returned 2 and a total reject load that has produced a BAD file.
I guess the answer is there a bad file is only produced if there are errors otherwise it can only be a clean load or clean load with discards.
I must admit I'm a newbie to perl and would not be able to comfortably write the bits required to parse any outputs logs for messages. However, I guess a bad file will only exist if there are errors and I can easily test for its existence in perl.
I'll await your valued comments before finalising this one up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are calling from Java (since this isn't in the Oracle forum, and most people here aren't familiar with sqlldr), you can read the stderr stream, as well as stdout, parse them, and have your program decide if this is a failure or not.
HTH,
Bill Vanderveen