Topic
  • 12 replies
  • Latest Post - ‏2005-08-27T00:28:39Z by SystemAdmin
SystemAdmin
SystemAdmin
332 Posts

Pinned topic Trapping Oracle Error Messages

‏2005-06-10T16:49:22Z |
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
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-06-10T19:42:29Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-06-14T17:54:23Z  
    LASTERR doesnt provide me the actual SQLERRM message. Is there a way i can track SQLERRM
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-06-14T19:34:32Z  
    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.

    :-)
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-06-19T20:44:12Z  
    You could always create a routine that executes b:a3a5cd84eeoerr [/b:a3a5cd84ee]and parses its result.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-07-28T18:29:32Z  
    I am new to datastage, does any have already written routine to capture oracle error messages

    Thanks
    Ravi
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-25T13:01:54Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-26T00:06:40Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-26T03:23:52Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-26T04:33:17Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-26T10:03:01Z  
    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.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-26T11:09:25Z  
    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.

    Regards.
  • SystemAdmin
    SystemAdmin
    332 Posts

    Re: Trapping Oracle Error Messages

    ‏2005-08-27T00:28:39Z  
    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.