Calling a stored function for insert, delete or update

[banactp]

Continuing a related conversation from here.

A stored function is selected in a query just like a table column and DataStage doesn't change how any of that works.

If the function performs an insert, delete or update, it cannot be used in a select statement just like a column.

So if anyone knows how to invoke a function that both performs an upsert and returns a numerical value, please reply. I can only get it to work if I hard-wire the function parameters inside the stage with constants or job parameters - I can't seem to be able to pass them in from each incoming row of data like I want to.

What the function in my case essentially does is accept some input parameters, if they match an existing row on a table, it updates one or more rows with the parameter values and returns the value of a column from one of the existing rows. If no match is found, a sequence is incremented, the input parameters are inserted, and the sequence value is returned. I want to take that return value in either case and use it downstream in my DataStage job.

And no, I can't change the function, it's not mine, I've just been told I have to use it...

[chulett]

If the function performs an insert, delete or update, it cannot be used in a select statement just like a column.

Of course, however now you've moved outside the scope of the original conversation / issue. Never mind the fact that this should have been asked as a new topic, so I've split it out for you.

[chulett]

Out of curiousity, have you tried using the Stored Procedure stage? Not sure what stage is "the stage" mentioned above that you are using, hence the question. You'd need to set it to a type of "Transform" rather than Source or Target for it to process single rows like that.

[rameshrr3]

You can import a stored function using a stored procedure import. And Call it using a stored procedure stage. Works for us ( Oracle 11g ) . For convinience im capturing the return code of this function .