Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 it an
SQL
ORA-01002: fetch out of sequence
error is fired. I have read before about it and I found that the problem is because I am using a fetch statement in. But till now I did not discover how to resolve it. Any helps are appreciated, thank you.
Below is my Procedure:

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


What I have tried:

I removed section LOOP ... END LOOP cursor has opened normally, any hints?
Posted
Updated 11-Dec-17 23:33pm
v2

1 solution

If I understand the problem correctly, it looks like you're looping the result set in the procedure so when you try to continue fetching data on the calling side, you've already reached the end of the result set.

If you need to loop the data inside the procedure and to return the data to the caller, use a different cursor for both tasks.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900