Page 1 of 1

Calling user-defined Oracle function

Posted: Mon May 14, 2007 2:28 pm
by vnspn
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.

Posted: Mon May 14, 2007 2:48 pm
by chulett
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.

Posted: Tue May 15, 2007 7:44 am
by vnspn
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.

Posted: Tue May 15, 2007 7:48 am
by chulett
If you really need to do it that way, seems to me it would have to be an OCI lookup.

Posted: Tue May 15, 2007 7:57 am
by DSguru2B
But would'nt a lookup mean that the function return should have a matching key with the incoming data?