Calling user-defined Oracle function

vnspn · Post by **vnspn** » Mon May 14, 2007 2:28 pm

Hi,

We have a requirement to call a user-defined Oracle function to do some logic. Hope we can call such user-defined function through an OCI stage.

Based on the return value of the user-defined function, we would need to route the record for further downstream processing. For example, if the function return 'Y', then we need to pass it on to one output link, else if the function returns 'N', then we have to pass those records to another link. Please let us know is this possible to accomplish.

Thanks.

chulett · Post by **chulett** » Mon May 14, 2007 2:48 pm

You 'call' an Oracle function in DataStage the same way you would in normal SQL - just select it as part of your query. The stage can generate it for you if you put the function (etc) in the Derivation for the column.

vnspn · Post by **vnspn** » Tue May 15, 2007 7:44 am

Thanks Craig. So, you say that the function needs to be called from an SQL.

In our case, we would have to call a Oracle function somewhere in the middle of a Job and route the output based on the return value of the function. What could be the best possible solution for this? Is it that we would need to split the Job into two at the point where we need to call the function, so that we call call that function at the start of the second Job.

chulett · Post by **chulett** » Tue May 15, 2007 7:48 am

If you really need to do it that way, seems to me it would have to be an OCI lookup.

DSguru2B · Post by **DSguru2B** » Tue May 15, 2007 7:57 am

But would'nt a lookup mean that the function return should have a matching key with the incoming data?