Meake 
10 pts. | May 28 2009 1:01AM GMT
you might want to declare the C1 before the BEGIN and after the condition you wish to satisfy then write a loop to check for condition…
Depends on what you want to the answers to be like….
Q:
SQL error
DECLARE
BEGIN
FOR C1 IN
(SELECT A.BANK,A.BRANCH,A.MODULE,A.SCHEME,A.ACCOUNTNO,A.ACCOUNTISN,NVL(T.NEXTDUEDATE,:TDATE) NEXTDUEDATE,A.REVIEWDATE
FROM ACCOUNTMASTER A,TERMLOANSDATA T
WHERE A.BANK=:INPBANK AND A.BRANCH=DECODE(:INPBRANCH,'ALL',A.BRANCH,:INPBRANCH)
AND A.MODULE=:INPMODULE AND A.SCHEME=DECODE(:INPSCHEME,'ALL',A.SCHEME,:INPSCHEME)
AND A.ACCOUNTNO=DECODE(:INPACCOUNTNO,'ALL',A.ACCOUNTNO,:INPACCOUNTNO)
AND T.BANK=A.BANK AND T.BRANCH=A.BRANCH AND T.MODULE=A.MODULE AND T.SCHEME=A.SCHEME AND T.ACCOUNTNO=A.ACCOUNTNO
AND T.ACCOUNTISN=A.ACCOUNTISN)
loop
while c1.NEXTDUEDATE<=c1.REVIEWDATE
c1.NEXTDUEDATE:=add_months(c1.NEXTDUEDATE,1)
dbms_output.put_line(c1.BANK||' '||c1.BRANCH||' '||c1.MODULE||' '||c1.SCHEME||' '||c1.ACCOUNTNO||' '||c1.ACCOUNTISN||' '||c1.NEXTDUEDATE||' '||c1.REVIEWDATE);
end loop;
END;
ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "C1" when expecting one of the following:
. ( * @ % & - + / at loop mod remainder rem
<an exponent (**)> and or || multiset
The symbol "loop" was substituted for "C1" to continue.
ORA-06550: line 17, column 1:
PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting one of the following:
. ( * % & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ betw
ASKED:
May 26 2009 5:49 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
DECLARE
BEGIN
FOR C1 IN
(SELECT A.BANK,A.BRANCH,A.MODULE,A.SCHEME,A.ACCOUNTNO,A.ACCOUNTISN,NVL(T.NEXTDUEDATE,:TDATE) NEXTDUEDATE,A.REVIEWDATE
FROM ACCOUNTMASTER A,TERMLOANSDATA T
WHERE A.BANK=:INPBANK AND A.BRANCH=DECODE(:INPBRANCH,'ALL',A.BRANCH,:INPBRANCH)
AND A.MODULE=:INPMODULE AND A.SCHEME=DECODE(:INPSCHEME,'ALL',A.SCHEME,:INPSCHEME)
AND A.ACCOUNTNO=DECODE(:INPACCOUNTNO,'ALL',A.ACCOUNTNO,:INPACCOUNTNO)
AND T.BANK=A.BANK AND T.BRANCH=A.BRANCH AND T.MODULE=A.MODULE AND T.SCHEME=A.SCHEME AND T.ACCOUNTNO=A.ACCOUNTNO
AND T.ACCOUNTISN=A.ACCOUNTISN)
while c1.NEXTDUEDATE<=c1.REVIEWDATE
loop
c1.NEXTDUEDATE:=add_months(c1.NEXTDUEDATE,1) ;
dbms_output.put_line(c1.BANK||' '||c1.BRANCH||' '||c1.MODULE||' '||c1.SCHEME||' '||c1.ACCOUNTNO||' '||c1.ACCOUNTISN||' '||c1.NEXTDUEDATE||' '||c1.REVIEWDATE);
end loop;
END;
---------------------------------
We would need to know what is the logic behind this code.
The 'loop' keyword as it was in the original code corresponded to the FOR <CURSOR> loop, so the "while" is probably intended to be a condition to exit tthe cursor loop, and not a nested loop.
If this is the case, then try this:
DECLARE
BEGIN
FOR C1 IN
(SELECT A.BANK,A.BRANCH,A.MODULE,A.SCHEME,A.ACCOUNTNO,A.ACCOUNTISN,NVL(T.NEXTDUEDATE,:TDATE) NEXTDUEDATE,A.REVIEWDATE
FROM ACCOUNTMASTER A,TERMLOANSDATA T
WHERE A.BANK=:INPBANK AND A.BRANCH=DECODE(:INPBRANCH,'ALL',A.BRANCH,:INPBRANCH)
AND A.MODULE=:INPMODULE AND A.SCHEME=DECODE(:INPSCHEME,'ALL',A.SCHEME,:INPSCHEME)
AND A.ACCOUNTNO=DECODE(:INPACCOUNTNO,'ALL',A.ACCOUNTNO,:INPACCOUNTNO)
AND T.BANK=A.BANK AND T.BRANCH=A.BRANCH AND T.MODULE=A.MODULE AND T.SCHEME=A.SCHEME AND T.ACCOUNTNO=A.ACCOUNTNO
AND T.ACCOUNTISN=A.ACCOUNTISN) LOOP
c1.NEXTDUEDATE:=add_months(c1.NEXTDUEDATE,1)
dbms_output.put_line(c1.BANK||' '||c1.BRANCH||' '||c1.MODULE||' '||c1.SCHEME||' '||c1.ACCOUNTNO||' '||c1.ACCOUNTISN||' '||c1.NEXTDUEDATE||' '||c1.REVIEWDATE);
EXIT WHEN c1.NEXTDUEDATE > c1.REVIEWDATE
end loop;
END;
