Select not executing when trying to declare a cursor

[cfcProgrammer]

Hi Everyone,

Looking for help once again!!!

Here is the code I have in a stored procedure. I am NOT getting an error,however, the select statement that builds the cursor is not executing.... I can not figure out why???? Can anyone see where my logic has gone wrong??

BEGIN
	
	DECLARE CURSOR T1 IS
	 
	SELECT EXEMPT_AMT INTO V_AMT 
	FROM REQUEST R, PAY_STUB PS, INCOME I
	WHERE R.REQUEST_ID = PS.REQUEST_ID
	AND PS.INCOME_ID = I.INCOME_ID 
	AND CUR_STATUS_ID IN (2253,2563,1671,2566,7327)
	AND I.INCOME_SOURCE_ID = 182
	AND R.CASE_ID =P_CASE_ID
	AND EXEMPT_AMT <> 0
	AND  TO_CHAR(R.REQUEST_DATE,'YY')  = TO_CHAR(SYSDATE,'YY');
BEGIN

FOR T1_REC IN T1 LOOP
	V_TOT := V_TOT + V_AMT;
END LOOP;

END; --BEGIN FOR LOOP

IF V_TOT >= 500 THEN
   V_TOTAL := 500;
ELSE 
   
   V_TOTAL := NVL(V_INC_SRC_EXEMPT_AMT,0) - (NVL(V_TOTAL1,0) + NVL(V_TOTAL2,0) + NVL(V_TOTAL3,0) + V_TOT);
END IF;

I thought it may be because of the begin around the for loop but if I remove it then I get a compile error stating that it is expecting a begin... ???

Thanks for help that anyone can give...
cfc

 

[SantaMufasa]

CFC,

First, I believe that you have not disclosed all of your code...Where have you DECLAREd/defined V_TOTAL, V_TOT, V_AMT, V_INC_SRC_EXEMPT_AMT?

Also, your indentation implies one thing, yet your coding dictates a very different thing...Here is how PL/SQL interprets your block:

BEGIN
    ...
    DECLARE
        ...
    BEGIN
        FOR
            ...
        END LOOP;
    END;
    IF
        ...
    ELSE
        ...
    END IF;
<missing END statement>

The above block of code certainly would not pass syntax muster. So, when you post the rest of the code, we can advise you better on why the block does what it does.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SimonSellick]

Also, you might not need your first BEGIN - the compiler is asking for a BEGIN before your FOR loop because the DECLARE begins a new, nested, block which is expected to contain executable statements.

If this is a function or proc, the DECLARE is implied and must not be present. The declarations are then FOLLOWED by the BEGIN that introduces the executable code.

HTH

 

[cfcProgrammer]

CREATE OR REPLACE PROCEDURE AM5820_DED_AMTP   (   P_CASE_ID NUMBER,
	   P_INCOME_SOURCE_ID NUMBER,
	   P_CURRENT_REQUEST_ID NUMBER,
	   P_TOTAL OUT NUMBER,
	   P_AMT IN NUMBER,
	   P_CREATED_BY_ID	IN NUMBER,
	   P_ERROR_CODE        OUT NUMBER, -- RETURN
	   P_ERROR_DESC        OUT VARCHAR2 -- RETURN
) IS
   INTRETVAL   NUMBER;
   SAVE_OK     BOOLEAN := FALSE;
   V_AMT NUMBER:= 0;
   V_TOT NUMBER:= 0;
   V_TOTAL     NUMBER := 0;
   V_TOTAL1 NUMBER := 0;
   V_TOTAL2 NUMBER := 0;
   V_INC_SRC_EXEMPT_AMT NUMBER := 0;

BEGIN

    P_ERROR_CODE := 0; -- => EVERYTHING OK
    P_ERROR_DESC := 'OK'; -- => EVERYTHING OK
	 BEGIN

 --find the income_source.exempt amount
 SELECT EXEMPT_AMT INTO V_INC_SRC_EXEMPT_AMT
 FROM INCOME_SOURCE
 WHERE INCOME_SOURCE_ID = P_INCOME_SOURCE_ID;

SELECT NVL(SUM(PS.EXEMPT_AMT),0)
R.REQUEST_ID ,PS.EXEMPT_AMT ,PS.VERIFIED_IND ,I.INCOME_SOURCE_ID ,R.REQUEST_DATE,R.CUR_STATUS_ID
INTO V_TOTAL1
FROM REQUEST R
 ,PAY_STUB PS
,INCOME I
,TABLE_MAINTAIN TM
 WHERE
 PS.REQUEST_ID = R.REQUEST_ID(+)
 AND  R.CASE_ID = P_CASE_ID --CASE_ID
 AND PS.INCOME_ID = I.INCOME_ID
AND R.REQUEST_ID <> P_CURRENT_REQUEST_ID 	 	  AND PS.VERIFIED_IND = 1
AND I.INCOME_SOURCE_ID = P_INCOME_SOURCE_ID  	 	  AND TO_CHAR(R.REQUEST_DATE,'YY')  = TO_CHAR(SYSDATE,'YY')
AND R.CUR_STATUS_ID = TM.TABLE_MAINTAIN_ID
AND (R.CUR_STATUS_ID IN (2557,1478,2560,1509)
OR INSTR(UPPER(TM.SHORT_DESC), 'APPROVED') = 1
OR INSTR(UPPER(TM.SHORT_DESC), 'SUSPENDED') = 1
OR INSTR(UPPER(TM.SHORT_DESC), 'INELIGIBLE') = 1);
	
SELECT NVL(SUM(PS.EXEMPT_AMT),0)
R.REQUEST_ID ,PS.EXEMPT_AMT ,PS.VERIFIED_IND ,I.INCOME_SOURCE_ID ,R.REQUEST_DATE,R.CUR_STATUS_ID
INTO V_TOTAL2
FROM REQUEST R
 ,PAY_STUB PS
,INCOME I
WHERE
 PS.REQUEST_ID = R.REQUEST_ID(+)
 AND PS.INCOME_ID = I.INCOME_ID
 AND R.REQUEST_ID = P_CURRENT_REQUEST_ID--CURRENT REQUEST_ID
AND PS.VERIFIED_IND = 1
AND I.INCOME_SOURCE_ID = P_INCOME_SOURCE_ID; --CURRENT INCOME SOURCE
	
	BEGIN
	
	DECLARE CURSOR T1 IS
	 
SELECT EXEMPT_AMT INTO V_AMT 
FROM REQUEST R, PAY_STUB PS,INCOME I
WHERE R.REQUEST_ID = PS.REQUEST_ID
AND PS.INCOME_ID = I.INCOME_ID 
AND CUR_STATUS_ID IN (2253,2563,1671,2566,7327)
AND I.INCOME_SOURCE_ID = 182
AND R.CASE_ID = 10978
AND EXEMPT_AMT <> 0
AND  TO_CHAR(R.REQUEST_DATE,'YY') = TO_CHAR(SYSDATE,'YY');

BEGIN
	FOR T1_REC IN T1 LOOP
		V_TOT := V_TOT + V_AMT;
	END LOOP;
END;

IF V_TOT >= 500 THEN
   V_TOTAL := 500;
ELSE 
   V_TOTAL := NVL(V_INC_SRC_EXEMPT_AMT,0) - (NVL(V_TOTAL1,0) + NVL(V_TOTAL2,0) + V_TOT);
END IF;

GOTO PROCEED1; 

EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO PROCEED;
END; -- begin
 
<<PROCEED>>

v_total := NVL(V_INC_SRC_EXEMPT_AMT,0) - (V_TOTAL1 + V_TOTAL2);
		
<<PROCEED1>>

IF V_TOTAL > P_AMT THEN
	V_TOTAL := P_AMT;
END IF ; 

IF v_total < 0 THEN
 	 v_total := 0;
END IF;

END;
   P_TOTAL := V_TOTAL;

END;
/

 

[cfcProgrammer]

I appreciate any help that you can provide.. Thank you again

 

[BJCooperIT]

Perhaps

      select EXEMPT_AMT
      into   V_INC_SRC_EXEMPT_AMT
      from   INCOME_SOURCE
      where  INCOME_SOURCE_ID = P_INCOME_SOURCE_ID;

is returning an exception due to retrieving no rows or more than one row?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[cfcProgrammer]

No, there is no exception error being raised. I declared a cursor to handle more than one row returning and I have an exception handler for when there are no rows returned.. what is happening and what my issue is... is that the select statement that builds my cursor is not executing.. therefore v_tot is always going to be zero... I don't understand why this block of code is not executing...

 

[Dagon]

Bear in mind that if you are using a cursor, the NO_DATA_FOUND exception clause will not be called. If no rows are found, the loop will just be executed 0 times.

 

[Dagon]

Your goto statements serve no real purpose. An exception clause will ONLY be called if there is an error. A cursor returning no rows is not an error.

 

[Dagon]

I don't understand the cursor for loop. You are doing:

FOR T1_REC IN T1 LOOP
V_TOT := V_TOT + V_AMT;
END LOOP;

but V_AMT isn't set by the cursor. It will just be zero. You need:

FOR T1_REC IN T1 LOOP
V_TOT := V_TOT + T1.EXEMPT_AMT ;
END LOOP;

Also, your cursor couldn't possibly compile:

DECLARE CURSOR T1 IS

SELECT EXEMPT_AMT INTO V_AMT
FROM REQUEST R, PAY_STUB PS,INCOME

INTO cannot be used in a cursor.

 

[cfcProgrammer]

oh dear... Thank you for your help.. as you must already be aware I am new to PL/SQL...

I will take your advice and modify the procedure to accordingly.

cfc

 

[Dagon]

Sorry, I meant:

FOR T1_REC IN T1 LOOP
V_TOT := V_TOT + T1_REC.EXEMPT_AMT ;
END LOOP;