Page 1 of 1
Error while using Stored procedure
Posted: Mon Feb 13, 2012 7:41 am
by chandra.shekhar@tcs.com
Hi,
How/Where can I pass the job parameters as one of my input in a SP stage? My procedure has two inputs and one output. I want those inputs to come from job paramters. I have tried lot of things and searched here too but got no luck. I am getting the following error
Code: Select all
StrProc_LD_INGT,0: Error: The parameter (%1) is not associated with a column
StrProc_LD_INGT,0: Error occurred in call to ORPHCallActivePluginInitialize().
Appreciate your inputs.
Posted: Mon Feb 13, 2012 8:05 am
by HendrikB
Hi,
maybe this sample about calling a DB2 procedure via stored procedure stage helps:
CALL #$DB2SCHEMA#.PROC_NAME(#PARAM1#, '"#PARAM2#"','"#PARAM3#"','#PARAM4#','#PARAM5#',?,?,?,?,?,?,?,?);
Values for input variables provided by #Parmaters#, output variables represented by "?"
Posted: Mon Feb 13, 2012 8:14 am
by chulett
It would be helpful to know what database you're talking about and if you allowed the stage to generate the syntax or if you are typing it in manually. For the latter try the former.
Posted: Mon Feb 13, 2012 8:17 am
by chandra.shekhar@tcs.com
Thanks for your input.
Yes I am calling a DB2 procedure via stored procedure stage.
Can you tell me where I should keep this query?
Also please tell me that why you kept double quotes for some input arguments and single quotes for other?
Does it depend upon the data type of my input argument?
Posted: Mon Feb 13, 2012 8:24 am
by chandra.shekhar@tcs.com
@Craig.
I am having DB2 database.
Actually I have run this procedure successfully for no input parameters. But now, due to some other requirement I have to include two input parameters(which are basically TIMESTAMPs) and it has to come from job parameters. The output parameter is a INTEGER which will give me the number of rows processed. I am doing this for my daily incremental load where my first input parameter would be start time and second would be end time.
Posted: Mon Feb 13, 2012 8:28 am
by HendrikB
Also please tell me that why you kept double quotes for some input arguments and single quotes for other?
Does it depend upon the data type of my input argument?
Yes it depends, for calling that sample stored procedure I had to use ...
single quote for date
double quotes for string
no quote for integer
Posted: Mon Feb 13, 2012 8:31 am
by chandra.shekhar@tcs.com
@HendrikB
Yes, as I told I am using TIMESTAMPs so I am using single quotes for my input parameters but still it is giving me the same error.
You didnt tell where hould I keep this query?
Posted: Mon Feb 13, 2012 9:54 am
by HendrikB
Actually I have run this procedure successfully for no input parameters
So I thought that there is no need to tell you the place where to keep the query ...
Place it here: Stage tab > Syntax > Procedure call syntax
Did you specify your output variable / column in Parameter tab?
The error message you got might relate to a missing column mapping for your output variable ...