Home » SQL & PL/SQL » SQL & PL/SQL » facing problem in this pl-sql code using cursor (11g using toad)
icon5.gif  facing problem in this pl-sql code using cursor [message #565720] Thu, 06 September 2012 07:20 Go to next message
maan1789
Messages: 7
Registered: September 2012
Junior Member


"ORA-06550: line 20, column 11:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge"



declare
cursor cpf is
select empno, ename, sal basic, sal*0.45 hra, sal*0.35 da, sal*0.15 pf, deptno
from emp
where sal>4000;
i cpf%rowtype;
vgross number;
begin
open cpf;
loop
fetch cpf into i;
if cpf%found then
vgross:=i.basic+i.hra+i.da-i.pf;
DBMS_OUTPUT.PUT_LINE(i.empno||' '||i.ename||' '||i.basic||' '||i.hra||' '||
i.da||' '||i.pf||' '||i.deptno||' '||i.vgross);
else
exit;
end if;
end loop;
close cpf;

Re: facing problem in this pl-sql code using cursor [message #565723 is a reply to message #565720] Thu, 06 September 2012 07:28 Go to previous messageGo to next message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum. Please read and follow How to use [code] tags and make your code easier to read?
You appear to be missing an end statement.
Re: facing problem in this pl-sql code using cursor [message #565727 is a reply to message #565720] Thu, 06 September 2012 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Welcome to the forum.
Please always copy and paste your SQL*Plus session like:
SQL> declare 
  2   cursor cpf is 
  3    select empno, ename, sal basic, sal*0.45 hra, sal*0.35 da, sal*0.15 pf, deptno
  4    from emp
  5    where sal>4000;
  6   i cpf%rowtype;
  7   vgross number;
  8  begin
  9   open cpf;
 10    loop 
 11    fetch cpf into i;
 12     if  cpf%found then 
 13     vgross:=i.basic+i.hra+i.da-i.pf;
 14     DBMS_OUTPUT.PUT_LINE(i.empno||' '||i.ename||' '||i.basic||' '||i.hra||' '||
 15      i.da||' '||i.pf||' '||i.deptno||' '||i.vgross);
 16      else 
 17      exit;
 18     end if;
 19   end loop; 
 20   close cpf;
 21  
 22  /
declare
*
ERROR at line 1:
ORA-06550: line 21, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe

You miss "end;" at line 21 to end the PL/SQL block.

Please Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: facing problem in this pl-sql code using cursor [message #565731 is a reply to message #565727] Thu, 06 September 2012 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
When you'll done with this error you will face another one:
SQL> declare 
  2   cursor cpf is 
  3    select empno, ename, sal basic, sal*0.45 hra, sal*0.35 da, sal*0.15 pf, deptno
  4    from emp
  5    where sal>4000;
  6   i cpf%rowtype;
  7   vgross number;
  8  begin
  9   open cpf;
 10    loop 
 11    fetch cpf into i;
 12     if  cpf%found then 
 13     vgross:=i.basic+i.hra+i.da-i.pf;
 14     DBMS_OUTPUT.PUT_LINE(i.empno||' '||i.ename||' '||i.basic||' '||i.hra||' '||
 15      i.da||' '||i.pf||' '||i.deptno||' '||i.vgross);
 16      else 
 17      exit;
 18     end if;
 19   end loop; 
 20   close cpf;
 21  end;
 22  /
    i.da||' '||i.pf||' '||i.deptno||' '||i.vgross);
                                           *
ERROR at line 15:
ORA-06550: line 15, column 44:
PLS-00302: component 'VGROSS' must be declared
ORA-06550: line 14, column 4:
PL/SQL: Statement ignored

Your cursor has no "vgross" field.

Regards
Michel
Re: facing problem in this pl-sql code using cursor [message #565813 is a reply to message #565731] Thu, 06 September 2012 15:15 Go to previous messageGo to next message
maan1789
Messages: 7
Registered: September 2012
Junior Member
thanks i got this point.
Re: facing problem in this pl-sql code using cursor [message #565819 is a reply to message #565720] Thu, 06 September 2012 19:15 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

you failed to include the end statment at the end.
Re: facing problem in this pl-sql code using cursor [message #565826 is a reply to message #565819] Thu, 06 September 2012 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Is this not what I said and showed?

Regards
Michel
Re: facing problem in this pl-sql code using cursor [message #565876 is a reply to message #565826] Fri, 07 September 2012 13:06 Go to previous message
maan1789
Messages: 7
Registered: September 2012
Junior Member
yes vgross doesn't belongs to cursor field, I'm using this variable with cursor type variable 'i' in display satatement
it's not allowed.

(........i.da||' '||i.pf||' '||i.deptno||' '||vgross);

it's allowed, I understand this point.
Previous Topic: Need help on DECODE query
Next Topic: ORA-32033: unsupported column aliasing
Goto Forum:
  


Current Time: Fri Feb 27 11:59:11 CST 2026