DSXchange

Hi freinds,

For Sever Routine -
My problem is how to store return value in parameter and how to call value in output parameter
I have store the value returning from simple SQL and calling output argument in server routine. Is possible with sql or I have to right the PLSQL code for the returning value for simple query like (select colmn1 from table)

Thanks
Vinod

Server routines are written in DataStage BASIC, not in PL/SQL or any other scripting language. The return value is returned through a variable whose name is Ans. It is also possible to use user-definable system variables such as @USER.RETURN.CODE and @USER0 through @USER4 to return other values. Technically it is also possible to return values through the input arguments, since routines' arguments are called by reference, but this is not regarded as a good practice because it introduces the possibility of very hard to diagnose side effects.

To invoke PL/SQL commands from a server routine, you use the DSExecute subroutine, which captures both the output and the exit status. The output includes line terminators, which are converted into field marks before being loaded into the third argument variable of DSExecute.

How to decalre user define.

i need result in column and return value in output parameter

You don't. You can capture the result and run a DataStage job to put it in a column.

How about you provide a written specification of what you want to do? Ignore PL/SQL, ignore routines - do it in English.

Alternately you can write a whole lot of functions that employ the ODBC API (called BCI functions in DataStage BASIC), details of which you can search for here. Be aware that this is not a legitimate use for your DataDirect ODBC license, so you will need to purchase one of those also.