SQL error

10 pts.
Tags:
ORA-06550
Oracle error messages
SQL
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
UPDATED: May 28, 2009  1:01 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

try:

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

<i><b>loop</b></i>

c1.NEXTDUEDATE:=add_months(c1.NEXTDUEDATE,1) <i><b>;</b></i>

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:

<pre>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);
<b>EXIT WHEN c1.NEXTDUEDATE > c1.REVIEWDATE</b>
end loop;
END;</pre>

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meake
    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....
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following