help with cursor

[nirasha]

I stink at PL/SQL and need your help badly.

Does this look right? I just want to check for the rows and display them. I need to put them later in a function. Any help would appreciated?

declare chldcur cat%row_type

begin

curc1 is
SELECT C.id, c.name
FROM cat c
WHERE child_count != (select count(child_Cat_id)
FROM cat_rel r
WHERE r.parent_Cat_id=c.id
GROUP BY parent_Cat_id);

if curc1%ISOPEN
then
close curc1;
end if;

open curc1;
fetch curc1 into chldcur;

while curc1%FOUND loop
dbms_output.put_line('id= 'chdcur.id);
dbms_output.put_line('name= 'chdcur.name);
fetch curc1 into chldcur;
end loop

close cur1
end;

or can I just say return curc1 instead of the dbms output lines?

Also I used cat%rowtype since I saw it in some example.. Is this correct, whct should I define it as?

Thanks so much,
Nirasha

[pwoneill]

Hrm all you want to do is print rows if they exist? Can't you just spool the sql query in SQL*Plus?

But either way, if all you want to do is load a query and print what is inside, this is a little easier.
<font face="courier">
declare
&nbsp;&nbsp;cursor c1 is
&nbsp;&nbsp;&nbsp;&nbsp;select c.id, c.name
&nbsp;&nbsp;&nbsp;&nbsp;from catc
&nbsp;&nbsp;&nbsp;&nbsp;where child_count != (select count(child_Cat_id)
&nbsp;&nbsp;&nbsp;&nbsp;where r.parent_Cat_id = c.id
&nbsp;&nbsp;&nbsp;&nbsp;group by parent_Cat_id;
begin
&nbsp;&nbsp;for rec in c1 loop
&nbsp;&nbsp;&nbsp;&nbsp;dbms_output.put_line ('id = ' || rec.id);
&nbsp;&nbsp;&nbsp;&nbsp;dbms_output.put_line ('name = ' || rec.name);
&nbsp;&nbsp;end loop;
end;