CODEWITHSUNDEEP
oracle-database
Chibak
Chibak

Reputation: 233

How to fetch values from cursor into variables?

I have problems fetching values from cursor into variables:

create or replace 
procedure projectinfo(num clubs.clubid%type) 
as
--identify variables
p_cln clubs.clubname%type;
p_projn projects.projectname%type;
p_projnum number;
p_taskn tasks.taskname%type;
p_tasknum number;

cursor cur is 
select c.clubname, p.projectname, t.taskname 
from clubs c join projects p on  c.clubid=p.clubid
join tasks t on t.projectid=p.projectid
where c.clubid=num;
--I have checked the above cursor and it's worked fine!!!
begin
p_projnum:=0;
p_tasknum:=0;

open cur;

loop

fetch cur into p_cln,p_projn, p_taskn;
dbms_output.put_line(p_cln|| ' ' ||p_projn|| ' ' || p_taskn);
-- the above command does not print variable values!!!
exit when cur%notfound;

p_projnum:=p_projnum+1;

dbms_output.put_line(' ************************ ');
dbms_output.put_line(' club name : ' ||p_cln);
dbms_output.put_line( ' project ' ||p_projnum|| ' ' || p_projn);

loop   
p_tasknum:=p_tasknum+1;
dbms_output.put_line('Task: ' ||p_tasknum|| ' '  || p_taskn);

fetch cur into p_cln,p_projn, p_taskn;
exit when cur%notfound;
end loop;
end loop;
close cur;
end projectinfo;

I have checked my cursor and itdoes contain all values that I need. My programm compiles FINE but does not print any output!!!

Upvotes: 4

Views: 3125

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

What tool are you using to run your procedure? By default, most tools will not allocate a buffer for dbms_output to write to and will not display anything written to dbms_output. That's why you'd never depend on dbms_output for any real code.

If you are using SQL*Plus, you need to enable serveroutput before executing your procedure

SQL> set serveroutput on;
SQL> exec projectinfo( <<some number>> );

If you are using a GUI, the GUI will almost certainly have a way to enable dbms_output. But that will be very different for different applications. In SQL Developer, for example, you'd need to ensure that the DBMS Output window is visible, click the green "plus sign" icon, and choose your connection to enable dbms_output.

Upvotes: 6

Related Questions