Code: Select all
*Write query to a temporary sql file.
cmd= "echo ":Query_File: "> G:\temp\Datastage\Query.sql"
Call DSExecute("NT",cmd,Output,Retval)
*execute the sql file
cmd= "sqlplus -s ":DB_User:"/":DB_Pass:"@":DSN: "< G:\temp\Datastage\Query.sql"
Call DSExecute("NT",cmd,Output,Returnval)
Ans=trim(Field(Output, ' ',2))
I am calling this routine from UserVariables Activity in the Datastage sequence as below:
Code: Select all
ExecuteSQL(DB_User, DB_Pass, DSN, Query_File)
I ran the sequence to execute below SQL:
SELECT TO_CHAR(TO_DATE("'"201608"'","'"YYYYMM"'"),"'"MON-YY"'") FROM DUAL;
It returns below when I execute the routine using Test option in the routine itself: AUG-16
But when I run the sequence, I get below error:
Code: Select all
Controller problem: Error calling DSSetParam(PERIOD_09), code=-4
[ParamValue/Limitvalue is not appropriate]