Re: Cursor help?

From: <fitzjarrell_at_cox.net>
Date: Thu, 15 Nov 2007 11:54:54 -0800 (PST)
Message-ID: <fe31cfcd-8f86-43cc-8d6e-7926367335f4@b32g2000hsa.googlegroups.com>

On Nov 15, 1: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

Take your alias out of the double quotes ...it makes it case sensitive and also makes it require the double quotes when you reference the column:

 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;
 /

The code above should work.

David Fitzjarrell Received on Thu Nov 15 2007 - 13:54:54 CST