Re: Cursor help?

On Nov 15, 7:43 pm, "CK" <c_kettenb..._at_hotmail.com> wrote:
> Oracle newbie question. I have a cursor with a group by clause. I can not
> get the column alias to print. It says "sections" need to be defined.
> Also I want this to only run against the first ten rows in the instructor
> table. I tried it this way and it doesn't work. Any dieas?
>
> SET SERVEROUTPUT ON;
> DECLARE
> CURSOR c_instructor_info IS
> SELECT first_name, last_name ,count(*) "sections"
> FROM instructor i INNER JOIN section s ON i.instructor_id =
> s.instructor_id
> WHERE rownum <= 10
> GROUP BY first_name, last_name;
> rv_instructor c_instructor_info%ROWTYPE;
> BEGIN
> OPEN c_instructor_info;
> LOOP
> FETCH c_instructor_info INTO rv_instructor;
> EXIT WHEN c_instructor_info%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE('Instructor: '||rv_instructor.first_name||'
> '||rv_instructor.last_name||', teaches '||rv_instructor.sections||'
> sections.');
> END LOOP;
> CLOSE c_instructor_info;
> END;
> /
>
> Thanks for any help.
> Cheers,
> ~ck

Can you try using cursor for loop

for x in c_instructor_info loop

   DBMS_OUTPUT.PUT_LINE('Instructor: '||x.first_name||'  '||x.last_name||', teaches '||x.sections||'  sections.');
end loop;

Also use count(*) sections

No =>" "

Thanks

www.ramsora.blogspot.com Received on Thu Nov 15 2007 - 14:07:48 CST