Using a plsql function

Nareshkotti · Post by **Nareshkotti** » Thu Nov 03, 2011 9:27 am

Hi All,

I need to use a function which will return one value.

for Ex: Sum Function which adds two input values and returns a value.

How can i use this function in datastage job?

Please explain in brief if someone knows this b'coz i never used scripts and functions in datastage.

Thanks in Advance!

ray.wurlod · Post by **ray.wurlod** » Thu Nov 03, 2011 12:42 pm

The obvious answer is to use the function within a user-defined SELECT statement.

Nareshkotti · Post by **Nareshkotti** » Fri Nov 04, 2011 10:01 am

ray.wurlod wrote:The obvious answer is to use the function within a user-defined SELECT statement. ...

Thanks for the reply ray.

yes we can call using the select statement, but i want to know how to call it from any of the other stages.

And how to invoke the procedures from datastage jobs please help me out if you have any idea.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 04, 2011 3:03 pm

It is not possible by any other means. A plsql function must be invoked from within an SQL statement. Therefore any stage that needs to use it must run some SQL. The stages that connect to Oracle are the only ones that can run Oracle SQL. (One exception: you could set up a command that runs sqlplus from the command line to execute an SQL statement, but that's introducing an extra layer of software (the operating system shell) for no good reason.)