Reputation: 1
Cursor returning no data inside a loop, but the data is definitely there
i have a very strange issue going on with nested cursor loops. i've pasted the code below. The overall function of the code is to fetch each row from cursor cur1 and use some info from cur1 to run a loop on another cursor cur2. fairly straight forward - just like a nested 2-tier for loop. The issue I run into is that when the first (any?) iteration through cur2 produces NOTFOUND, the subsequent/other iterations also produce NOTFOUND even though i explicitly run the cursor select statement manually and see data. I've also modified the data so that the first iteration DOES have data as a test, and everything works fine...
create or replace PROCEDURE EvalEqu
(prim_indg_ref_in NUMBER,
stock_ref_in NUMBER)
IS
SD_REF STOCK_DETAILS.STOCK_DETAIL_REF%TYPE; -- Variable used to hold stock_details_ref Stock_details
CAL_REF CALCULATIONS.CALC_REF%TYPE; -- Variable used to hold calc_ref value
CALCSTRG CALCULATIONS.CALCULATION_STATEMENT%TYPE; -- Variable used to hold calculation string from Calculations
CALCSTRGTEMP CALCULATIONS.CALCULATION_STATEMENT%TYPE; -- Variable used to hold calculation as parm values are substitued in
CALCVALTEMP STOCK_DETAILS.DATA%TYPE; -- Variable used hold andcheck calculated value sig figs
CALCVAL STOCK_DETAILS.DATA%TYPE; -- Variable used to populate calc value on Stock_details once calcualted
PARM CALC_COA_VALS.PARM_NAME%TYPE; -- Variable used to hold parm names used for substitution
PARMVAL STOCK_COA_DATA.DATA%TYPE; -- Variable used to hold CoA value from form that replaces PARM value
--Define cursor to hold all calculations associated to a stock record
cursor stock_calc is select SD.STOCK_DETAIL_REF, CALC.CALC_REF, CALC.CALCULATION_STATEMENT
from stock_details sd,
calculations calc
where SD.LABEL_REF = calc.label_ref
and CALC.Prim_Ing_Ref = prim_indg_ref_in
and SD.STOCK_REF = stock_ref_in;
--Define cursor to hold all parm names and their respective CoA values from form
cursor calc_parms is select CCV.PARM_NAME, SCD.DATA
from calc_coa_vals ccv, stock_coa_data scd
where CCV.COA_VAL_REF=SCD.COA_VAL_REF
and SCD.STOCK_REF=stock_ref_in
and CCV.CALC_REF=CAL_REF;
BEGIN
-- Open calculation cursor
IF NOT stock_calc%ISOPEN then
OPEN stock_calc;
END IF;
-- Loop through each calcualtion for Stock to calculate values
LOOP
FETCH stock_calc INTO SD_REF, CAL_REF, CALCSTRG;
EXIT WHEN stock_calc%NOTFOUND;
-- Open parm cursor to loop through each parm used in calculation
IF NOT calc_parms%ISOPEN then
OPEN calc_parms;
END IF;
FETCH calc_parms into PARM, PARMVAL;
IF calc_parms%NOTFOUND
THEN
-- No parameters are associated with this calculation
-- It is a simple integer and can be returned as such
CALCVAL := CALCSTRG;
ELSE
WHILE calc_parms%FOUND
LOOP
CALCSTRGTEMP := REPLACE(CALCSTRG, PARM, PARMVAL);
CALCSTRG := CALCSTRGTEMP;
FETCH calc_parms into PARM, PARMVAL;
END LOOP;
CLOSE calc_parms;
-- evaluate the equation
EXECUTE IMMEDIATE 'SELECT to_char(('||CALCSTRG||'),''999999999990.099999999999999'') from dual ' INTO CALCVALTEMP;
IF instr(to_char(to_number(CALCVALTEMP)), '.') = 0 -- whole number or 0, no padding
THEN
CALCVAL := to_char(to_number(CALCVALTEMP));
ELSE
IF ABS(to_number(CALCVALTEMP)) < 1
THEN
IF instr(CALCVALTEMP, '-') = 0 -- Indicates positive number
THEN
EXECUTE IMMEDIATE 'SELECT LPAD(RPAD(to_CHAR(trunc(to_number('||CALCVALTEMP||'),12-(instr('||CALCVALTEMP||',''.'')-1))), 12,''0''), 13, ''0'') from dual ' INTO CALCVAL;
ELSE -- Negative number
EXECUTE IMMEDIATE 'SELECT LPAD(RPAD(to_CHAR(trunc(ABS(to_number('||CALCVALTEMP||')),12-(instr(ABS(to_number('||CALCVALTEMP||')),''.'')-2))), 12,''0''), 14, ''-0'') from dual ' INTO CALCVAL;
END IF;
ELSE
IF instr(CALCVALTEMP, '-') = 0 -- Indicates positive number
THEN
EXECUTE IMMEDIATE 'SELECT RPAD(to_CHAR(trunc(to_number('||CALCVALTEMP||'),12-(instr('||CALCVALTEMP||',''.'')-1))), 13,''0'') from dual ' INTO CALCVAL;
ELSE -- Negative number
EXECUTE IMMEDIATE 'SELECT RPAD(to_CHAR(trunc(to_number('||CALCVALTEMP||'),12-(instr('||CALCVALTEMP||',''.'')-2))), 14,''0'') from dual ' INTO CALCVAL;
END IF;
END IF;
END IF;
END IF;
-- Update stock_details record with newly calculated value
UPDATE stock_details
SET DATA = CALCVAL
WHERE STOCK_DETAIL_REF = SD_REF;
END LOOP;
CLOSE stock_calc;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Data for cursor 1: stock_ref=124000
STOCK_DETAIL_REF STOCK_REF LABEL_REF DATA
--------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------------------------------
217924 124000 1000 1.0
217925 124000 2602 (A1*2)
217926 124000 2603 (A1*3)
217927 124000 2604 (A1*4)
Prim_Ing_Ref = 1234
CALC_REF Prim_Ing_Ref LABEL_REF CALCULATION_STATEMENT
--------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
846 1234 666 1.0
847 1234 667 1.0
848 1234 690 1.0
849 1234 1000 1.0
850 1234 2602 (A1*2)
851 1234 2603 (A1*3)
852 1234 2604 (A1*4)
Result from cursor1:
STOCK_DETAIL_REF CALC_REF CALCULATION_STATEMENT
--------------------------------------- --------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
217924 849 1.0
217925 850 (A1*2)
217926 851 (A1*3)
217927 852 (A1*4)
Cursor 2 looks for values for the parameters in the equations and substitutes them for the requested values and evaluates the equations. Cursor 2 seems to returned nothing for ALL entries in Cursor 1 if ANY NOTFOUND occurs. so for the 1.0 value, there are no parameters., but this causes the rest of the cursor loops to fail
Cursor 2 data:
STOCK_REF CALC_REF PARM_NAME DATA
--------------------------------------- --------------------------------------- --------- ------------------------------------------------------------
124000 850 A1 25
124000 851 A1 25
124000 852 A1 25
these are for the 3 equations shown above in CALCULATION_STATEMENT. however, as said, the routine fails, just for this specific instance where static numeric values are intermixed with equations (i.e. the cursor2 returns NOTFOUND for at least one item in cursor1). manually running the cursor's select statement retrieves the data just fine. I can only assume that the implementation of the cursor is incorrect somehow.
Upvotes: 0
Views: 434
Answers (1)
Reputation: 9778
you need to pass the value of c1 into c2 as a parameter. Have a look at the example here:
PL/SQL referencing another cursor in a cursor
BTW, I assume this line:
FETCH stock_calc INTO ...
should be:
FETCH cur1 INTO ...
Update Following Comments
(Please tick this if you felt I have helped with the answer)
To re-iterate what was said in the comments:
you need to CLOSE calc_parms within IF calc_parms%NOTFOUND THEN... at the moment you only close it in the ELSE statement.
Also, if performance is an issue, you should look at looping through calc_parms before you loop through stock_calc. As calc_parms is not being changed by anything happening in stock_calc, PARM and PARMVAL have exactly the same values in every loop through stock_calc.
Upvotes: 0
Related Questions
- Cursor procedure skipping all rows and fetching last row data for each iteration
- Cursor Not looping all the Records
- MySQL Cursors fetching null value
- Cursor Fetch Returns Null
- CURSOR not looping more than once
- error in loop of procedure using a cursor
- MySQL procedure loop stops after one iteration
- no_data_found error when running a cursor loop
- NO DATA FOUND in loop cursor
- Cursor not looping correctly