ORA-01002: fetch out of sequence In JAVA Programs

User_UH8XJ

Hi I have a stored oracle procedure - which has a select query which is , returning the data to OUT SYS_REFCURSOR. This procedure is called by Java programs and hence OUT SYS_REFCURSOR is returned to Java application. This was working fine for years. BUT I recently modified the stored proc code as I want to see what values are getting returned to Java application. So I fetched the OUT SYS_REFCURSOR values into record type variable. And this small change is failing this program . When I run my proc on DB using TOAD , it runs fine. It does not give any error but it is giving error to Java application. Please advise - what is the issue here

------------------------------------------------------------------------------------------------------

Eg my code is something like this

PROCEDURE UPRFETCH( IN_BOOKDATE        IN VARCHAR2,   OUT_CURSOR        OUT SYS_REFCURSOR)

 IS

  V_rec R_CRUISE_PROMO_PREBOOK_TYPE; --- type is defined in spec

v_cnt number :=0;

 BEGIN

  OPEN OUT_CURSOR FOR

   SELECT 

   DISTINCT A.LINENUMBER,

        A.PROMOTYPECODE,

        A.CHARTEREDSCHEDULED,

       FROM EMP  ;

   LOOP 

  FETCH OUT_CURSOR

  INTO v_rec;

  EXIT WHEN OUT_CURSOR%NOTFOUND;

  v_cnt := v_cnt + 1; 

  dbms_output.put_line('OUT_CURSOR Record no -'||v_cnt);

  dbms_output.put_line('LINENUMBER      '||v_rec.LINENUMBER       );

  dbms_output.put_line('PROMOTYPECODE     '||v_rec.PROMOTYPECODE     );

  dbms_output.put_line('PRICE         '||v_rec.PRICE         );

  dbms_output.put_line('CHARTEREDSCHEDULED   '||v_rec.CHARTEREDSCHEDULED   );

END LOOP;  

CLOSE OUT_CURSOR;

EXCEPTION

      WHEN OTHERS

      THEN

      plsqllog_error('Unhandled Exception'||sqlerrm);

      Raise;

  END UPRFETCH;