Error while using Stored procedure

chandra.shekhar@tcs.com · Mon Feb 13, 2012 7:41 am

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.

HendrikB · Post by **HendrikB** » Mon Feb 13, 2012 8:05 am

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 "?"

chulett · Post by **chulett** » Mon Feb 13, 2012 8:14 am

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.

chandra.shekhar@tcs.com · Mon Feb 13, 2012 8:17 am

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?

chandra.shekhar@tcs.com · Mon Feb 13, 2012 8:24 am

@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.

HendrikB · Post by **HendrikB** » Mon Feb 13, 2012 8:28 am

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

chandra.shekhar@tcs.com · Mon Feb 13, 2012 8:31 am

@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?

HendrikB · Post by **HendrikB** » Mon Feb 13, 2012 9:54 am

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 ...