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
How to define the value in output value in Server Routines
Moderators: chulett, rschirm, roy
How to define the value in output value in Server Routines
vinodkumar.b.p
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.