1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with using cursor

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 15, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Hi,

    I have this problem when i am trying to declare a cursor as given below, can you help me to figure out the problem with this code :

    Code (Text):
    DECLARE CURSOR SCR_CURSOR IS SELECT COUNT(*),SUM(MARKS_OBTAINED),ROLLNO FROM SCORE GROUP BY ROLLNO;
    CNT NUMBER;
    MKOBT NUMBER;
    TOTMARK NUMBER;
    ROLL NUMBER;
    PERCENT NUMBER;
    BEGIN
    OPEN SCR_CURSOR();
    LOOP
    FETCH SCR_CURSOR INTO CNT,MKOBT,ROLL;
    EXIT WHEN SCR_CURSOR%NOTFOUND;
    TOTMARK := CNT*100;
    PERCENT := (MKOBT/TOTMARK*100);
    DBMS_OUTPUT.PUT_LINE(ROLL||' ' || PERCENT);
    CLOSE SCR_CURSOR;
    END LOOP;
    END;
    Error :
    ERROR at line 1:
    ORA-01001: invalid cursor
    ORA-06512: at line 8
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Off the top of my head, columns in a cursor that are expressions must be aliased. A secondary issue is that you were closing your cursor inside the loop rather than after it had completed processing all of the rows. I moved it outside the loop. Mind you, for something like this, a CURSOR FOR LOOP requires less code and fewer variable declarations.

    I don't have the table to test, but I expect the following would work (and the block is much easier to read with a little bit of indentation):

    Code (Text):
    DECLARE
      CURSOR SCR_CURSOR IS
        SELECT COUNT(*) AS CNT,
               SUM(MARKS_OBTAINED) AS MKOBT,
               ROLLNO
        FROM   SCORE
        GROUP BY ROLLNO;

      CNT      NUMBER;
      MKOBT    NUMBER;
      TOTMARK  NUMBER;
      ROLL     NUMBER;
      PERCENT  NUMBER;
    BEGIN
      OPEN SCR_CURSOR();
      LOOP
        FETCH SCR_CURSOR INTO CNT,MKOBT,ROLL;
        EXIT WHEN SCR_CURSOR%NOTFOUND;
        TOTMARK := CNT*100;
        PERCENT := (MKOBT/TOTMARK*100);
        DBMS_OUTPUT.PUT_LINE(ROLL||' ' || PERCENT);
      END LOOP;
      CLOSE SCR_CURSOR;
    END;
    The code with a CURSOR FOR LOOP would look like this:

    Code (Text):

    DECLARE
      CURSOR SCR_CURSOR IS
        SELECT COUNT(*) AS CNT,
               SUM(MARKS_OBTAINED) AS MKOBT,
               ROLLNO
        FROM   SCORE
        GROUP BY ROLLNO;

      TOTMARK  NUMBER;
      PERCENT  NUMBER;
    BEGIN
      FOR v_Lp in SCR_CURSOR LOOP
        TOTMARK := v_Lp.CNT * 100;
        PERCENT := (v_Lp.MKOBT/TOTMARK*100);
        DBMS_OUTPUT.PUT_LINE(v_Lp.ROLLNO||' ' || PERCENT);
      END LOOP;
    END;
     
    mukulverma2408 likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    It was a typo mistake.

    Thanks for the solution.