All Forums UDA
TommyP 2 posts Joined 10/11
12 Oct 2011
Stored Procedure Issue

Hi,

I wrote the Stored Procedure below but when I call it, its getting error: CALL Failed. 7631:  MULTILVLBOM_COSTROLLUP_CURSOR:Fetch/Close attempted on a closed cursor in the stored procedure (MULTILVLBOM_COSTROLLUP_CURSOR).

 

REPLACE PROCEDURE MACH1_PROCS.MULTILVLBOM_COSTROLLUP_CURSOR

( IN IN_USER VARCHAR(50))

BEGIN

DECLARE PLANT VARCHAR(20);

DECLARE PART VARCHAR(50);

DECLARE PROJECT_ID VARCHAR(20);

DECLARE COST_TYPE VARCHAR(5);

DECLARE LEVELS VARCHAR(50);

DECLARE REPORTING_CURRENCY VARCHAR(50);

DECLARE INCUR CURSOR FOR

SELECT A.CMPNT_IDNT_NUM,

A.CMPNT_LVL_ITM_NUM,

A.PRJ_ID,

A.COST_TYP_CD,

1 AS LEVELS,

A.PRJ_DFLT_CCY_CD

FROM MACH1_BVAL.BOM_COST_DATA A

INNER JOIN

(SELECT MAX(PRJ_ID) AS PRJ_ID FROM MACH1_BVAL.EPC_PRJ) B

ON A.PRJ_ID = B.PRJ_ID

GROUP BY 1,2,3,4,5,6;

OPEN INCUR;

START_LABEL: LOOP

FETCH INCUR INTO PLANT,

PART,

PROJECT_ID,

COST_TYPE,

LEVELS,

REPORTING_CURRENCY;

 

IF SQLSTATE = '00000'

THEN

CALL MACH1_PROCS.MULTILVLBOM_COSTROLLUP_SUMRY(

PLANT,

PART,

PROJECT_ID,

COST_TYPE,

LEVELS,

REPORTING_CURRENCY,

IN_USER);

ELSE

LEAVE START_LABEL ;

 

END IF;

END LOOP START_LABEL;

CLOSE INCUR;

END;

 

Any idea why its failing?

Adeel Chaudhry 773 posts Joined 04/08
13 Oct 2011

Hi,

 

Can you try re-structuring it to following format:

 

 

FOR CurrentRow AS SourceCursor CURSOR FOR    

 

SELECT

COL1,COL2

FROM Table1

 

DO

 

Call SP1(CurrentRow.COL1, CurrentRow.COL2);

 

END FOR;

 

HTH!

 

Regards, Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

TommyP 2 posts Joined 10/11
13 Oct 2011

Hi,

I changed the Procedure as follows but now get this error:

CALL Failed. 7628:  MULTILVLBOM_COSTROLLUP_SUMRY:Invalid statement specified inside a FOR cursor statement.

Any ideas what could be wrong or how to get this to work? Thanks

REPLACE PROCEDURE MACH1_PROCS.MULTILVLBOM_COSTROLLUP_CURSORT

( IN IN_USER VARCHAR(50))

BEGIN

DECLARE PLANT VARCHAR(20);

DECLARE PART VARCHAR(50);

DECLARE PROJECT_ID VARCHAR(20);

DECLARE COST_TYPE VARCHAR(5);

DECLARE LEVELS VARCHAR(50);

DECLARE REPORTING_CURRENCY VARCHAR(50);

FOR CurrentRow AS SourceCursor CURSOR FOR

SELECT A.CMPNT_IDNT_NUM PLANT,

A.CMPNT_LVL_ITM_NUM PART,

A.PRJ_ID PROJECT_ID,

A.COST_TYP_CD COST_TYP,

'1' AS LEVELS,

A.PRJ_DFLT_CCY_CD REPORTING_CURRENCY

FROM MACH1_BVAL.BOM_COST_DATA A

INNER JOIN

(SELECT MAX(PRJ_ID) AS PRJ_ID FROM MACH1_BVAL.EPC_PRJ) B

ON A.PRJ_ID = B.PRJ_ID

GROUP BY 1,2,3,4,5,6

DO

CALL MACH1_PROCS.MULTILVLBOM_COSTROLLUP_SUMRY(

CurrentRow.PLANT,

CurrentRow.PART,

CurrentRow.PROJECT_ID,

CurrentRow.COST_TYP,

CurrentRow.LEVELS,

CurrentRow.REPORTING_CURRENCY,

:IN_USER);

 

END FOR;

END;

Adeel Chaudhry 773 posts Joined 04/08
17 Oct 2011

It should work fine, i tired with dummy definition of tables and SP you are calling in a loop.

 

Can you share DDLs for tables and SPs for further analysis?

 

Regards, Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.