We have to execute a stored procedure which has 4 arguments out of which 2 are inputs and 2 are outputs.
for e.g USP_GEN_SEQ_NO(param1 IN ,param2 IN,param3 OUT,param4 OUT)
I made the Stored Proc as a Transform and imported the parameters and mapped them to the corresponding columns.For the two input parameters I entered the input values at the Parameter marker/literal field.
The job has run for more than an hour and it seems to be hanged,I aborted the job manually.
We have a statement like below where a function is called with in that stored procedure.Is it causing the problem to run the job continuously or any other property mistake which I am doing?
The stored Procedure returns only one row.
Code: Select all
TIMESTAMPRESETSEQNO =
CASE
WHEN (V_CURRENT_DATE >= TIMESTAMPRESETSEQNO) THEN
STR_TRANSMISSION.USF_DATEDIFF(TIMESTAMPRESETSEQNO,INTERVALPERIOD,INTERVALMEASUREMENT)
ELSE
TIMESTAMPRESETSEQNO
END
WHERE TRANSTYPE = P_TRANSTYPE
AND LOCATIONCODE = CASE
WHEN (ADAPTERTYPE = 'EMAIL') THEN
'EMAIL'
ELSE
P_LOCATIONCODE
END
RETURN LASTSEQNOUSED INTO P_SEQNO;