DSXchange

Posted: **Thu Dec 27, 2012 5:11 am**

Hi All,
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;

Posted: **Thu Dec 27, 2012 5:15 am**

Can you please explore more on your problem?

1. what SP is this? Oracle/Sybase/Teradata?
2. How you are calling it?
3. Is your procedure working fine, when running as individual?
4. It creates problem only when you are running inside ETL?

Posted: **Thu Dec 27, 2012 5:30 am**

Hi The issue is resolved.
It is an Oracle SP.I had to provide input link to a Transform type to pass values but I assumed that we can pass the values from the Parameter Marker/literal field.It is now working fine.I was a bit quick in posting the topic and simultaneously tried a different way from what I had been doing and got it working.Thanks for your time..

DSXchange

Function with in a stored procedure

Function with in a stored procedure