title: ORA-01002: fetch out of sequence in oracle
body: <blockquote>
    <p>ORA-01002: fetch out of sequence in oracle when using sys_refcursor.</p>
  </blockquote>
  
  <p>I am expecting list of employees in the cursor. In my real scenario I need to check the count of employees and if count greater than zero I should use the same  select statement. Instead of executing same select statement multiple times I tried the below code snippet. But I am getting Fetch out of sequence error in the output cursor.</p>
  
  <p>My table:</p>
  
  <p><img src="https://i.stack.imgur.com/EjkTy.png" alt="My table"></p>
  
  <pre><code>create or replace procedure sp_temp_1  (
  var_job in VARCHAR2,
  cur_custid out sys_refcursor
  ) 
  AS
  
    /* Declare Ref Cursor */
    --cur_custid  SYS_REFCURSOR;
  
    /* Declare Type using Fields from the employees table. */
    TYPE t_custrec IS RECORD (
      firstname    temp_emp.firstname%TYPE,
      lastname     temp_emp.lastname%TYPE
    );
  
    /* Declare Record based off of Type */
    custrec   t_custrec;
  
  BEGIN
  
    OPEN cur_custid FOR
      SELECT DISTINCT firstname
                     ,lastname
        FROM temp_emp
       WHERE JOB = var_job; /* Data Analyst does not exist forcing Zero Rows returned */
  
    LOOP
      FETCH cur_custid
        INTO custrec;
      EXIT WHEN cur_custid%notfound;
      ---looping happens
    END LOOP;
  
    dbms_output.put_line('Num Rows: ' || cur_custid%rowcount);
  
    IF cur_custid%rowcount = 0
    THEN
    dbms_output.put_line('Zero Rows Condition Met.  Opening Cursor.');
      OPEN cur_custid FOR
        SELECT '0' AS empid FROM dual;
    END IF;
  
  END;
  </code></pre>
  
  <p>I am expecting list of employees , but the <code>sys_refcursor</code> is providing "fetch out of sequence error" in Oracle:</p>
  
  <p><img src="https://i.stack.imgur.com/xxRyo.png" alt="Screen shot of my error"></p>