ORA-01002: fetch out of sequence

Question

I created a procedure in oracle database that returns data in a ref-cursor, and I want it to return the rowcount of this cursor also as an output variable. After testing, the P_count variable is filled correctly, but when I tried to open the cursor an 'ORA-01002: fetch out of sequence' error fired. I have read before about it and I found that the problem is because I am using a fetch statement in my code. But till now I did not discover how to resolve it. Any helps are appreciated, thank you. Below is my Procedure:

PROCEDURE IS_CLIENT_LOGGED_IN (
  P_CLIENT_NUM  Varchar2,
  P_CURSOR out  SYS_REFCURSOR ,
  P_COUNT OUT   NUMBER,
  P_ERROR out   Varchar2
) AS
  cur_rec Varchar2(1024);
BEGIN
   BEGIN 
     Open P_CURSOR FOR
       SELECT ID
         FROM tbl_registration
        WHERE tbl_client_id = P_CLIENT_NUM  
          AND tbl_logout_date is null;

     LOOP
      FETCH P_CURSOR INTO cur_rec;  
      EXIT WHEN P_CURSOR%notfound;
      P_COUNT := P_CURSOR%rowcount;--will return row number beginning with 1
     END LOOP;
   EXCEPTION WHEN OTHERS THEN 
     P_ERROR := 'Unable to select Data from tbl_registration' ||SQLERRM;
   END;    
END IS_CLIENT_LOGGED_IN;

Answer 1

Based on your comment the procedure should be like this:

PROCEDURE IS_CLIENT_LOGGED_IN (
  P_CLIENT_NUM  Varchar2,
  P_CURSOR out  SYS_REFCURSOR ,
  P_COUNT OUT   NUMBER,
  P_ERROR out   Varchar2
) AS
  cur_rec Varchar2(1024);
BEGIN
     Open P_CURSOR FOR
       SELECT ID
         FROM tbl_registration
        WHERE tbl_client_id = P_CLIENT_NUM  
          AND tbl_logout_date is null;

     LOOP
      FETCH P_CURSOR INTO cur_rec;  
      EXIT WHEN P_CURSOR%notfound;
      P_COUNT := P_CURSOR%rowcount;--will return row number beginning with 1
     END LOOP;
     CLOSE P_CURSOR;

     Open P_CURSOR FOR
       SELECT ID
         FROM tbl_registration
        WHERE tbl_client_id = P_CLIENT_NUM  
          AND tbl_logout_date is null;

   EXCEPTION WHEN OTHERS THEN 
     P_ERROR := 'Unable to select Data from tbl_registration' ||SQLERRM;
END IS_CLIENT_LOGGED_IN;

Not very efficient, but that's what you asked for. Anyway, I don't see any reason to increment P_COUNT one-by-one.

Make

SELECT COUNT(*) INTO P_COUNT
     FROM tbl_registration
    WHERE tbl_client_id = P_CLIENT_NUM  
      AND tbl_logout_date is null;

Open P_CURSOR FOR
   SELECT ID
     FROM tbl_registration
    WHERE tbl_client_id = P_CLIENT_NUM  
      AND tbl_logout_date is null;

to get the same.