Issue parsing parameter from server routine

[chandu123]

I have written a server routine for executing the SQL and returning the result as below (by taking a post from this forum as ref):

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 tested the routine and it works well.

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)

Then I am passing the parameter into a parallel job executed using the Job Activity of the sequence. The parameter in the job is defined as 'string'.

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]

Please let me know if I am missing anything here.

[chulett]

That error has been discussed here quite a bit. Do an exact search for "ParamValue/Limitvalue is not appropriate" for all of the discussions.

[chandu123]

Thanks Craig. I did check the other posts related to this and they said it was the issue with the datatype of the parameter defined in the job. I have defined it as String. Since my routine is returning AUG-16 it should be accepted in the job. This is where I am getting confused and want to know if I am doing anything wrong.

[chulett]

It's not about the datatype, at least not directly. It's about the fact that what is returned by that function is a dynamic array and the Field Mark delimiter at the end of the string is what makes it "not appropriate". Those other posts should have shown you how to remove them / it.

[chandu123]

I replaced @FM from the result and it worked perfectly. Thanks Craig!

EReplace(YourParameterField,@FM,"")