DSXchange

Hi,

We have requirement to execute some SQL statements from a routine. The DataStage server is on Windows and the
database is Oracle. The command that we use in the routine to get this done is,

Call DSExecute("NT", Cmd, Output, SysRetCode)

The second argument 'Cmd' is the single line of command. This contains the database login credentials and the
actual SQL statement that needs to be executed. We are not sure of the format in which this command has to be framed. Anyone who have done this before, please let us know the format in which this second argument has to be passed.

Thanks.

The exact format depends upon which sql command line program you use. For the moment, just open up a DOS "CMD" window. If you can enter and execute a one-liner from there then you can do so from DataStage as well.

There really isn't much of a choice for Oracle - you use sqlplus. And for 'some sql statements' you'll need to do that in a "here document" format. A best practice here, IMHO, is to script all of this - the sqlplus connection, the sql commands to execute, redirection of any output, post sql examination of output for errors, etc. Then all your routine needs to execute is the script.

As noted, this really isn't a DataStage problem - get whatever you are doing working outside of DataStage and then make the same call from your routine / job / sequence / whatever.

DB2 allows you to split the SQL over several calls, as it keeps the connected session "alive", whilst sqlplus does not. The internet is alive with examples of how to use sqlplus from the command line.

Please find the below sample routine where you can excute the sql statment from command line prompt using the routine,

***********************************************************************************
* rExecOra( pShellType, pOraDsn, pOraUser, pOraPass, pExecSql )

* Prepare command line
vCommand = "echo ": pExecSql :"; | sqlplus -S ": pOraUser :"/": pOraPass :"@": pOraDsn

*call DSLogInfo(vCommand,"")

* Execute the SQL
Call DSExecute( pShellType, vCommand, vOutput, vReturnCode )

* Check all is well
If vReturnCode Then Call DSLogFatal( 'The following SQL failed to execute: ': pExecSql, 'rExecOra' )

* Return the output
Ans = vOutput
*******************************************************************************

Welcome! Unfortunately, that routine is incomplete. All you are trapping are problems connecting to sqlplus, you'll have no clue if the sql itself didn't run problem free inside the sqlplus session unless you redirect the output and grep it for problems.