Calling Function From STP stage

[pradkumar]

Hi

I would like to call a function which is in Oracle.
I know one way of doing it (Call it as select in OCI stage)
But the function which I am having does not execute with SELECT stmt and gives it error num as "1".
But when I execute this function normally, it is doing good.

So Now I decided to call it as a procedure using STP stage.
How to accomplish this?

Thanks
Pradeep

[kumar_s]

Create a stored procedure and import in the Stored Procedure stage and call it. Else you can call using OCI stage as well. {call sp(input,output)};

[DSguru2B]

Call to the stored procedure can be made in the before/after sql tab.

[ray.wurlod]

A function is not the same things as a stored procedure.

The only way you have to use a Stored Procedure stage to invoke a function is to create a stored procedure which in turn calls the function. Unnecessary overhead.

Your time would be better spent diagnosing why the function does not seem to work in a SELECT statement, which is where it is intended to be used. What other warning messages are logged?

[pradkumar]

Thx all


Actually my Oracle team will develop functions and give it to me to implement the job calling the func/proc.
I tried executing the SQL code in TOAD too with SELECT stmt.
It is supposed to give errornum = 0 if it gets executed succesfully. But it is giving 1. So I figured out that it is not the problem of DS.

But those people wants me to call the function without using "SELECT" stmts.

This is where I was stucked.

[kumar_s]

So stored procedure which calls the function will be the preffered approach.

[chulett]

You can't "call the function" without using a SELECT statement - that's how they work, they must be selected.

And wrapping the select of the function into a stored procedure just seems... silly. But hey, if that's what your Oracle team wants you to do, knock yourself out.