Link to home
Start Free TrialLog in
Avatar of azsat
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!

 
Avatar of bvanderveen
bvanderveen

I don't think that you can work around this.  Are you reaching a maximum point causing failure.  Don't have my sqlldr notes in front of me, but as I recall, you can set a maximum discards and maximum errors that will trigger a failure (default, I think is all discards, 50 errors.)  If you aren't hitting these, then I'm not sure why you aren't getting a zero result back.

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

Avatar of azsat

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?

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.
Avatar of azsat

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.
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.
Avatar of azsat

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.
Avatar of 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.
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.
Avatar of azsat

ASKER

Thanks will do.  I'm in London so ' won't be ale to respond with the results before 9:30 GMT.

Regards,

azsat.
Avatar of 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.
ASKER CERTIFIED SOLUTION
Avatar of bvanderveen
bvanderveen

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial