wrong output- 2 explicit cursors

[moenia]

Hello, my problem deals with the use of 2 explicit cursors

the code is displaying the wrong output.
can you help me figure it out what im doing wrong?
If you need the tables they are attached.

thank you for your time!

code:
_______________________________________________________
DECLARE

current_item_id NUMBER(6);

CURSOR item_cursor IS
SELECT course.call_id c_callID, course.course_name c_cname, course.course_id c_cID,
course_section.c_sec_id csec_secID, course_section.course_id csec_cID, course_section.term_id csec_termID
FROM course, course_section
WHERE course.course_id = course_section.course_id
And course_section.term_id = 6;


ITEM_ROW item_cursor%ROWTYPE;


Cursor Inventory_Cursor IS
SELECT distinct student.s_first student_f, student.s_last student_l, student.s_id student_id,
enrollment.s_id stu_en_id, enrollment.c_sec_id course_sec_ID,
course_section.c_sec_id c_section_ID, course_section.term_id c_sec_termID, course_section.course_id courseID
FROM student, enrollment, course_section
Where course_section.course_id = current_item_id
and course_section.term_id = 6
and course_section.c_sec_id = enrollment.c_sec_id;

INVENTORY_ROW Inventory_Cursor%ROWTYPE;

BEGIN

FOR ITEM_ROW IN ITEM_CURSOR LOOP

DBMS_OUTPUT.PUT_LINE('=============================');
DBMS_OUTPUT.PUT_LINE(ITEM_ROW.c_cname);
DBMS_OUTPUT.PUT_LINE('=============================');

current_item_id := ITEM_ROW.c_cID;

For INVENTORY_ROW IN Inventory_Cursor Loop

DBMS_OUTPUT.PUT_LINE (inventory_row.student_f);


End Loop;
END LOOP;
END;
--------------------O U T P U T-----------------

=============================
Intro. to Info. Systems
=============================
Amanda
Amanda
Amanda
Brian
Brian
Brian
Daniel
Daniel
Daniel
Michael
Michael
Michael
Ruben
Ruben
Ruben
Sarah
Sarah
Sarah
=============================
Systems Analysis
=============================
Amanda
Amanda
Amanda
Brian
Brian
Brian
Daniel
Daniel
Daniel
Michael
Michael
Michael
Ruben
Ruben
Ruben
Sarah
Sarah
Sarah
=============================
Database Management
=============================
Amanda
Amanda
Brian
Brian
Daniel
Daniel
Michael
Michael
Ruben
Ruben
Sarah
Sarah

THE GOOD OUTPUT SHOULD LOOK LIKE THIS:

=============================
Intro. to Info. Systems
=============================
Amanda
Ruben
Michael
=============================
Systems Analysis
=============================
Daniel
Amanda
Michael
=============================
Database Management
=============================
Daniel
Ruben

[ddrozdov]

DECLARE

current_item_id NUMBER(6);

CURSOR item_cursor IS
SELECT
c.call_id c_callID, c.course_name c_cname, c.course_id c_cID,
cs.c_sec_id csec_secID, cs.course_id csec_cID, cs.term_id csec_termID
FROM
course c,
course_section cs
WHERE
c.course_id = cs.course_id and
cs.term_id = 6;


ITEM_ROW item_cursor%ROWTYPE;


Cursor Inventory_Cursor IS
SELECT distinct
s.s_first student_f, s.s_last student_l, s.s_id student_id,
e.s_id stu_en_id, e.c_sec_id course_sec_ID,
cs.c_sec_id c_section_ID, cs.term_id c_sec_termID, cs.course_id courseID
FROM
student s, enrollment e, course_section cs
Where
cs.course_id = current_item_id and
cs.term_id = 6 and
cs.c_sec_id = e.c_sec_id and
s.s_id = e.s_id;

INVENTORY_ROW Inventory_Cursor%ROWTYPE;

BEGIN

FOR ITEM_ROW IN ITEM_CURSOR LOOP

DBMS_OUTPUT.PUT_LINE('=============================');
DBMS_OUTPUT.PUT_LINE(ITEM_ROW.c_cname);
DBMS_OUTPUT.PUT_LINE('=============================');

current_item_id := ITEM_ROW.c_cID;

For INVENTORY_ROW IN Inventory_Cursor Loop

DBMS_OUTPUT.PUT_LINE (inventory_row.student_f);


End Loop;
END LOOP;
END;

[moenia]

i got it, Thank you so much!
now i can go back to sleep, with a headache..

[slimdave]

Explicit cursors. Yuck.

[ddrozdov]

Why? I like them.
I would have parametrized the second cursor in the example above.

[slimdave]

• Slower than implicit cursors
• More code to write
• Coder is more likely to make an error in the code
• They split the logic into multiple places in the code

DECLARE
   current_item_id NUMBER(6);
BEGIN
   FOR ITEM_ROW IN 
      'SELECT
          c.call_id c_callID, c.course_name c_cname, c.course_id c_cID,
          cs.c_sec_id csec_secID, cs.course_id csec_cID, cs.term_id csec_termID
       FROM
          course c,
          course_section cs
       WHERE
          c.course_id = cs.course_id and
          cs.term_id = 6'
   LOOP
   
      DBMS_OUTPUT.PUT_LINE('=============================');
      DBMS_OUTPUT.PUT_LINE(ITEM_ROW.c_cname);
      DBMS_OUTPUT.PUT_LINE('=============================');
   
      current_item_id := ITEM_ROW.c_cID;
      
      FOR INVENTORY_ROW IN 
         'SELECT distinct
             s.s_first student_f, s.s_last student_l, s.s_id student_id,
             e.s_id stu_en_id, e.c_sec_id course_sec_ID,
             cs.c_sec_id c_section_ID, cs.term_id c_sec_termID, cs.course_id courseID
          FROM
             student s, enrollment e, course_section cs
          WHERE
             cs.course_id = current_item_id and
             cs.term_id = 6 and
             cs.c_sec_id = e.c_sec_id and
             s.s_id = e.s_id'
      LOOP
         DBMS_OUTPUT.PUT_LINE (inventory_row.student_f);
      END LOOP;
   END LOOP;
END;

Could actually code this as a single cursor, I'd think.

[ddrozdov]

• Give me reference for this one
• A little, but split logic is actually a plus.
• I am not sure about that.
• Split logic is a plus. Top down development....

Single cursor is a good idea.
You don't need current_item_id variable
just use ITEM_ROW.c_cID
How do you reference %ROWCOUNT in this one?
Otherwise good code

BEGIN
   FOR ITEM_ROW IN 
      (SELECT
          c.call_id c_callID, c.course_name c_cname, c.course_id c_cID,
          cs.c_sec_id csec_secID, cs.course_id csec_cID, cs.term_id csec_termID
       FROM
          course c,
          course_section cs
       WHERE
          c.course_id = cs.course_id and
          cs.term_id = 6)
   LOOP
   
      DBMS_OUTPUT.PUT_LINE('=============================');
      DBMS_OUTPUT.PUT_LINE(ITEM_ROW.c_cname);
      DBMS_OUTPUT.PUT_LINE('=============================');
      
      FOR INVENTORY_ROW IN 
         (SELECT distinct
             s.s_first student_f, s.s_last student_l, s.s_id student_id,
             e.s_id stu_en_id, e.c_sec_id course_sec_ID,
             cs.c_sec_id c_section_ID, cs.term_id c_sec_termID, cs.course_id courseID
          FROM
             student s, enrollment e, course_section cs
          WHERE
             cs.course_id = ITEM_ROW.c_cID and
             cs.term_id = 6 and
             cs.c_sec_id = e.c_sec_id and
             s.s_id = e.s_id)
      LOOP
         DBMS_OUTPUT.PUT_LINE (inventory_row.student_f);
      END LOOP;
   END LOOP;
END;

[slimdave]

Here's a performance reference ... http://asktom.oracle.com/pls/ask/f?p...:1544606261686

I don't see splitting the query away from the processing of the result set as a plus, because I keep having to page up/page down to understand what is happening, and i prefer to see both the query and the in-loop processing together.

More code = more opportunity for error.

[ddrozdov]

Dave, I read the posting. And I read about this in Tom Kyte's Effective Oracle by Design.

The implicit cursors are "select into's". The select statement in the "for rec in (select..." are not implicit cursors. The select statements in the loop definition are TKyte's coding preference. He likes the select statement to be right there in the definition of the loop. I like it too in some situations for example I use this when I write scripts (anonymous blocks) where I know the select statements are not going to be reused in any other place. If I am to reuse the sql code I will either declare it as an explicit cursor or wrap it into the function that returns an array with all data fetched into it and then I can reference all kind of array methods (COUNT, PRIOR, NEXT, EXISTS, LAST).
In other words, your change in code is not an example of implicit vs. explicit cursors, it's where the select statement goes.

By the way, how do you reference %ROWCOUNT when you put a select statement directly in the for loop definition?
d

[padders]

For the record Effective Oracle Design, page 607, subtitle 'Use Implicit Cursors for Result Sets with a Limited Number of Rows' gives the example of an implicit cursor as...

For x in ( select ... from ... where ...)
Loop
    Process...
End Loop;

And the text clearly states that...

'the implicit cursor performs at least as well as, and in most cases better than, the explicit cursor (with similar performance characteristics to the SELECT INTO example in the previous section'

Although it doesn't appear to say how you get the %ROWCOUNT ;-)

Personally for a few rows I don't care much between explicit and implicit. The problem I have is that this 'nested cursor' approach does not scale to large datasets where performance generally comes from set based processing rather than single-row or multiple row cursor lookups.