Pinned topic Trapping Oracle Error Messages
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Are there any datastage variables using which i can capture oracle error messages (SQLCODE, SQLERRM) which each row is processed.
Updated on 2005-08-27T00:28:39Z at 2005-08-27T00:28:39Z by SystemAdmin
Re: Trapping Oracle Error Messages2005-06-10T19:42:29ZThis is the accepted answer. This is the accepted answer.Sure. A link to an OCI stage will have associated with it Link Variables which can pass Oracle errors back to the Transformer feeding it. In the Derivation Editor look for b:4d09b28227Link Variables / Outputs...[/b:4d09b28227] and then pop open the section related to your link. You'll find DBMS_CODE, LASTERR, REJECTED, REJECTEDCODE and SQLSTATE there.
You may need to experiment a little to find out which of those are appropriate for your needs.
Re: Trapping Oracle Error Messages2005-06-14T19:34:32ZThis is the accepted answer. This is the accepted answer.You've got what you've got in the link and I don't think you have access to the actual SQL error i:ff5058af74message[/i:ff5058af74], just the error number. About the only place the actual message is stored is in the job's log, I do believe.
Some people have written custom routines or processes to 'farm' that kind of data from all job logs post run - that can turn into a career.
Re: Trapping Oracle Error Messages2005-08-25T13:01:54ZThis is the accepted answer. This is the accepted answer.I try to use Link Variable as descript by Craig, but I've some problem with them. In particular I expect that their value return to transform stage, but this isn't true. For example in case of duplicate key on OCI Oracle Stage the value of SQLSTATE/DBMSCODE/LASTERR on link to this one stage is blank/null...
Is that a trouble of our version (7.5.1) ? I don't understand.
Thank you for help.
Re: Trapping Oracle Error Messages2005-08-26T00:06:40ZThis is the accepted answer. This is the accepted answer.They shouldn't be empty when there is an actual error. Are you checking the correct link?
For example, two stream links from a transformer. One called 'Write' that goes to an OCI stage, another called 'Reject' that goes to a flat file. The constraint on the 'Reject' link would be something like 'Write.REJECTEDCODE' which would evaluate to true when there was a problem with the Oracle data. In the derivations of the 'Reject' link, you should be using 'Write.DBMSCODE' (etc) to see the ORA error.
Is that how you set things up? Just want to make sure you're not using them from the equivalent of your 'Reject' link.
Re: Trapping Oracle Error Messages2005-08-26T03:23:52ZThis is the accepted answer. This is the accepted answer.Hi Craig,
thank you for reply. My job is exactly as you say. There are:
- A sql query incoming to a transformer stage;
- Two links outcoming from transformer stage: one to OCI Stage and one (Rejected) to sequential file.. I'd want to write SQLSTATE or DBMSCODE on a field of the rejected file in case of Oracle error.
Maybe my contraint condition is wrong. If I put on contraint
WRITE -- WRITE.SQLSTATE = 0
REJ -- (check on rejected)
All rows enter in REJ link the code's field empty.
I also try with WRITE.DBMSCODE='ORA-00000', with WRITE.SQLSTATE='00000', with IsNull(WRITE.SQLSTATE) and with WRITE.SQLSTATE=' ', but nothing change. All rows enter in the flat file with none SQLSTATE and DBMSCODE.
What is the correct condition?
How should I define the code's field on the flat file? I use varchar.. Is it correct?
Thank you again.
Re: Trapping Oracle Error Messages2005-08-26T04:33:17ZThis is the accepted answer. This is the accepted answer.Hi Ravi,
please try only REJECTEDCODE. It seems that only this link variable will be setup correctly with the OCI-Plugin. I try also the other one with no success.
Re: Trapping Oracle Error Messages2005-08-26T10:03:01ZThis is the accepted answer. This is the accepted answer.Varchar is usually a pretty safe choice. :-)
I prefer to not check the Reject row box on a reject link which causes it to get b:3bba7813e4all[/b:3bba7813e4] rows for b:3bba7813e4any[/b:3bba7813e4] reason that don't go down b:3bba7813e4any[/b:3bba7813e4] of the 'previous' rows. That's one reason you are seeing the behaviour you are seeing. Your Oracle constraint is incorrect so nothing is written to that link, therefore everything goes down the reject. And because they never saw Oracle, the dbms specific fields are all null or empty.
If that's what you want, that's fine. I prefer to have specific reject links for specific purposes and control what goes down where. That's why I specifically have constraints like 'Go down the reject link only if the write to Oracle failed'.
There's practically a whole chapter in the online help on reject row handling, it would be a good thing to check out and experiment with. For now, as Udo did, I'd suggest sticking with b:3bba7813e4Linkname.REJECTEDCODE[/b:3bba7813e4] as your constraint which evaluates to true only if a row i:3bba7813e4actually goes down the link[/i:3bba7813e4] and then is puked back up by the database.
Re: Trapping Oracle Error Messages2005-08-26T11:09:25ZThis is the accepted answer. This is the accepted answer.Thank you for reply. I solve the problem.
In order to use this variables, the array size of all OCI stage on the line must be set to 1. With this setting DBMSCODE return correct value.
I hope this is a useful information.
Re: Trapping Oracle Error Messages2005-08-27T00:28:39ZThis is the accepted answer. This is the accepted answer.That's i:00a09fdadfmostly[/i:00a09fdadf] true. :-) You can still use them as long as you understand how they work in all situations.
I spent some time playing with Array Sizes and generating OCI errors and what I found was:
a) As you noted, keeping the Array Size at 1 makes i:00a09fdadfall[/i:00a09fdadf] aspects of error handling work as you would expect.
b) Bumping it up above 1 adds 'anomolies' to the process. I found that the correct problems were still identified and the correct problem rows were logged into the job's log. HOWEVER, what went down the reject link - i:00a09fdadfregardless[/i:00a09fdadf] of the actual number of errors in the array - was the b:00a09fdadflast record[/b:00a09fdadf] (and i:00a09fdadfonly[/i:00a09fdadf] the last record) in the array. Even if there was nothing wrong with that particular record, it was the one picked to go on the ride. It did, however, carry the correct error code link variables with it from the last true error record.
This was using DataStage 7.0.1 on an HP/UX system.