Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursors
Ref Cursors [message #580135] Wed, 20 March 2013 13:14 Go to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
declare 
type ref_cur is ref cursor;
r ref_cur;
enam emp%rowtype;
dnam dept%rowtype;
tab varchar2(10):=&t;
begin
if tab='e' or tab='E' THEN
dbms_output.put_line('The Employee details are'); 
open r for select * from emp;
 loop
fetch r into enam;
exit when r%notfound;
dbms_output.put_line('Employee Name is '||enam.ename||'  Employee sal is '||enam.sal); 
end loop; 
close r;
elsif tab='d' or tab='D' THEN
dbms_output.put_line('The Department details are'); 
open r for select * from dept;
 loop
fetch r into dnam;
exit when r%notfound;
dbms_output.put_line('Department '||dnam.dname||'  Department loc is '||dnam.loc); 
end loop; 
close r;
else
dbms_output.put_line('No Table Found');
end if; 
end;



Error at line 1
ORA-06550: line 6, column 19:
PLS-00201: identifier 'E' must be declared
ORA-06550: line 6, column 5:
PL/SQL: Item ignored
ORA-06550: line 8, column 4:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored

[EDITED by LF: removed useless version info; was "select * from v$version where banner like 'Oracle%';"]

[Updated on: Wed, 20 March 2013 15:43] by Moderator

Report message to a moderator

Re: Ref Cursors [message #580136 is a reply to message #580135] Wed, 20 March 2013 13:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
Meditate ove what happens to line

tab varchar2(10):=&t;


when you enter E when prompted.

SY.
Re: Ref Cursors [message #580137 is a reply to message #580135] Wed, 20 March 2013 13:19 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
show us the full session, not just error messages. how do we know what you ran with? one thing is that varchar2 variables must be enclosed in quotes.
Re: Ref Cursors [message #580138 is a reply to message #580136] Wed, 20 March 2013 13:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2011
Registered: January 2010
Senior Member
Oops, it looks like I misread the code.

SY.

[Updated on: Wed, 20 March 2013 13:23]

Report message to a moderator

Re: Ref Cursors [message #580142 is a reply to message #580135] Wed, 20 March 2013 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tab varchar2(10):=&t;

should be
tab varchar2(10):='&t';


Oracle version:
Quote:
select * from v$version where banner like 'Oracle%';


This is really silly or you just want to make fun of us.

And FORMAT your code which does not mean just put code tags.
If you don't how to do it, learn it using SQL Formatter.

Regards
Michel
Re: Ref Cursors [message #580143 is a reply to message #580142] Wed, 20 March 2013 14:13 Go to previous message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Thanks For the reply.
Previous Topic: Bulk Collect
Next Topic: how we create rank on column without using rownum function and rank function.
Goto Forum:
  


Current Time: Tue Sep 02 15:39:33 CDT 2014

Total time taken to generate the page: 0.06792 seconds