Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Error handling - Output fields not sent over to next step

  1. #1
    Join Date
    Oct 2011
    Posts
    13

    Default Error handling - Output fields not sent over to next step

    Name:  DB proc call.jpg
Views: 183
Size:  19.2 KBName:  flow.jpg
Views: 183
Size:  16.0 KBHi all,
    I'm facing a problem with the "error handling of step" type. The scenario is pretty simple: I have a "Call DB Procedure" step which calls a Oracle proc passing some input parameters and output parameters (which will get any eventual errors coming from the proc).

    The problem is that the output parameters (fields) are not sent over to the next step WHEN I MAKE THE NEXT STEP A "error handling of step" type. If I make it a default type/hop, the output fields are correctly available in the very next step.

    I attached two screenshots to make it easier to understand. If I look at the "Input fields" from the "Blocking step 2" step, I see the fields p_error and p_errorcode. But if I look at the input fields from the "Set variables" step, I will not see them, and that's exactly where I need them.

    Have anyone else gone through this? Any work around to handle this issue? Any help will be HUGELY appreciated! THANKS!

  2. #2
    Join Date
    Oct 2011
    Posts
    13

    Default

    I'm sorry, I forgot to note the version I'm running: 4.2.0-stable

  3. #3
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hey pabhb,

    in this case you should probably use a simple filter on your errorcode output param coming from the DB Procedure Call step. The step's error handling is only invoked when something goes wrong while calling the procedure. In that case Kettle does not use the fields generated by the DB proc step, since it assumes that the failed DB Procedure was not even called successfully.

    Cheers
    Slawo

  4. #4
    Join Date
    Oct 2011
    Posts
    13

    Default

    Quote Originally Posted by slawomir.chodnicki View Post
    Hey pabhb,

    in this case you should probably use a simple filter on your errorcode output param coming from the DB Procedure Call step. The step's error handling is only invoked when something goes wrong while calling the procedure. In that case Kettle does not use the fields generated by the DB proc step, since it assumes that the failed DB Procedure was not even called successfully.

    Cheers
    Slawo
    Slawo - thank you very much for the reply, that makes perfect sense to me. But in my case the DB procedure is called successfully and I need the output parameters to know which Oracle exception is being raised.

    I'm sorry, but what do you mean by "simple filter on your errorcode output param"? I guess I don't know how to do it. Is it something that would allow me to retrieve the output error param beyond the error handling step? Thanks!

  5. #5
    Join Date
    Sep 2009
    Posts
    810

    Default

    Hi pabhb,

    I am assuming that you are calling an Oracle procedure that, technically speaking, always succeeds. It captures internal errors into two ouput parameters. Kettle does not know that the output parameters indicate error conditions to you, and it treats them just like any other output params. Downstream from the DB Procedure call step you could use a filter step that would check the values of p_errorcode for example (I am assuming this will be null or 0 when there is no error) and divert error rows to another processing branch to handle them as appropriate.

    Cheers
    Slawo

  6. #6
    Join Date
    Oct 2011
    Posts
    13

    Default

    Quote Originally Posted by slawomir.chodnicki View Post
    Hi pabhb,

    I am assuming that you are calling an Oracle procedure that, technically speaking, always succeeds. It captures internal errors into two ouput parameters. Kettle does not know that the output parameters indicate error conditions to you, and it treats them just like any other output params. Downstream from the DB Procedure call step you could use a filter step that would check the values of p_errorcode for example (I am assuming this will be null or 0 when there is no error) and divert error rows to another processing branch to handle them as appropriate.

    Cheers
    Slawo
    Thanks again Slawo. I have created a "filter rows" step to handle the error after the DB procedure step, but the problem for me on this approach is that when an error is raised on my DB proc call, for some reason the filter rows step is not even being reached, so I have no way to handle the error unless I use a "error handling" step.... but as we know in this case the output params will not be sent over. I guess I'm in a dead end?

  7. #7
    Join Date
    Sep 2009
    Posts
    810

    Default

    hey pabhb,

    have you tried to right click the DB proc call step, "define error handling", specifiy the various fields, and see if any useful information is forwarded to the error step (I'm talking about enabling the error handling feature again)?

    Cheers
    Slawo

  8. #8
    Join Date
    Oct 2011
    Posts
    13

    Default

    Hey Slawo,

    That worked! I can now see the output fields coming from my call DB step! Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.