How to get values ​​from cursor to variables?

Question

I am having problems retrieving values ​​from the 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 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 checked my cursor and it contains all the values ​​that I need. My program compiles FINE, but does not output any output.

Answer 1

What tool do you use to start your procedure? By default, most tools do not allocate a buffer for dbms_output for writing and do not display anything written in dbms_output . That is why you will 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 use a graphical interface, the graphical interface will almost certainly be able to enable dbms_output . But it will be very different for different applications. For example, in SQL Developer, you need to make sure that the DBMS Output window is visible, click the green plus sign and select your connection to enable dbms_output .