can we execute oracle queries through any Datastage function

[parvathi]

Hi all,
can we execute oracle queries through any Datastage function like how we execute unix commands.
As when i want the output of a single select query
'select * from tablename' i put this in a sqlfile in my unix directory and execite this file through the unix commands in the routine and assign to a variable in the datastage.

Can it be done in a simpler way?


Thanks

[chulett]

Depends what "and assign to a variable in the datastage" means. Simplest way is probably in a job, but it all hinges on what you mean - how you plan on using this information you're selecting.

[kumar_s]

Hi Parvathi,
Why cant you use the OCI stage and output to a sequential stage for this. Which is for the same reason. If you the SQL that you use is not compatable in OCI stage, you can alternatively use Shell script directly to call the .sql through ExecuteCommand Activity or in any Before/After subroutine. But it doesnt make much difference from your previous approach.

[ray.wurlod]

There is no single function out of the box. But it's certainly possible to write a generic "Run SQL" function using BCI functions; I wrote one aeons ago. But, because you are UNIX-based, and presumably using the Data Direct ODBC drivers shipped with DataStage, you will need a licensed ODBC driver to adopt this approach.