Calling Function

[pradkumar]

Hi

I am having a function in Oracle with two input arguments. I need to call this function after the successful execution of my stored procedure.
My typical job design is like this:
I designed a job sequence. The first job activity is a normal job and second job activity is having a stored procedure stage in it. This STP stage is calling a SP from oracle.
[Job Activity1-->JobActivity2-->Notification]

So now after the succesful execution of my SP I need to call function.

Can I use the Another STP stage to call function? And how to pass arguments
for that function[/img]

[chulett]

You sure this is a function? Oracle stored functions are not called, they are selected just like any other field.

First suggestion would be to forget about the function and encapsulate the function's logic into a DataStage job and then run that. Otherwise, you'll need an OCI stage to run a query against dual and to include your function in the column list. Then just run any output from it out to whatever makes sense - a sequential file, audit table, dev null, whatever.

[pradkumar]

Yes. This is a function in oracle. We have a function code written
CREATE OR REPLACE FUNCTION Function_name
(table1 IN VARCHAR2,
table2 IN VARCHAR2
)
RETURN number AS

[jhmckeever]

pradkumar,

As chulett said, your Oracle function needs to execute in a query (say against dual) like so ...

Code: Select all

SELECT Function_name( "Arg1", "Arg2")
FROM dual

If you insist on using your function and want to call it from a STP stage you could create a new "wrapped" stored procedure that simply SELECTs your answer from dual (as above) into a variable and returns that value. Then just call the new wrapped procedure in your STP stage.

HTH,
J.

[pradkumar]

So how can I call the function alone in an OCI stage.
Since this is a separate job, I am not understanding whether to do it in before or after or in normal user defined SQL.

And by the way I need to pass two table names as input arguments.

[chulett]

It's just a select statement, how hard is that to implement? You don't even need 'custom' sql for it. Execute the select statement and then send the number that is returned somewhere as noted previously.

Job Parameters.