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.
Format of SQL command in DSExecute
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 22
- Joined: Wed Jul 02, 2008 7:01 am
- Location: London
Re: Format of SQL command in DSExecute
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
*******************************************************************************
***********************************************************************************
* 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers