Getting ORA-01002:fetch out of sequence error when doing Bulk-collect

Question

In the below example , I have a SP which will return a SYS_REFCURSOR as OUT parameter to the Java service, and in the same SP it will insert the SYS_REFCURSOR result to another table example -temp. When I execute the SP, it gives an error

ORA-01002 : fetch out of sequence error

I do see the data getting inserted but the service is not returning back any REFCURSOR to the service.

create or replace procedure ins_act(l_result        OUT        sys_refcursor)
as 

 --Created a RECORD type to hold the result of the SYS_REFCURSOR
 TYPE RSLT IS RECORD
 (

 l_id1                   varchar2(32),
 l_id2                   varchar2(32),
 l_id3                   varchar2(32),
 l_pid                   varchar2(16),
 l_ac                    varchar2(32),
 l_activity_date         varchar2(32),
 l_file_id               varchar2(64)
 ) ;

 --Created associative array to hold the result
 Type v_reslt is table of RSLT index by PLS_INETEGER;

 --Variable to Record type
 var_reslt   v_reslt;

begin
          -- Procedure to pull the get records 
          OPEN l_result FOR 
             SELECT COL1,COL2,COL3,CoUnt(*).....
             

          -- Bulk fetch
          Loop

          fetch l_result bulk collect into  var_reslt limit 100;         

          --Bulk Insert     
          FORALL I INTO 1..var_reslt.count SAVE EXCEPTIONS
          insert into temp(id1,
                           id2,
                           id3,
                           platform_id,
                           activity_code,
                           update_timestamp,
                           file_id)          
          values 
             (to_char(to_date(var_reslt(i).l_id1,'mm/dd/yyyy'),'mm/dd/yyyy'),
              var_reslt(i).l_id2,
              var_reslt(i).l_id3,
              0,
              var_reslt(i).l_ac,
              var_reslt(i).l_activity_date,
              var_reslt(i).l_file_id);

         exit when l_result%notfound; 

      end loop;

   Commit;

  close l_result;

Exception
 WHEN OTHERS
   THEN    
         --Bulk Exception handling
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
               || ‘: ‘
               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         RAISE;          
end;
/

Answer 1

Several problems with this code.

The first is that you are checking whether the cursor found anything after the FORALL statement. This is wrong because the FORALL statement attempts to process the cursor even when the cursor is %notfound. That might be the source of your ORA-01002 exception.

The second error is a subtle one. Checking for cursor %notfound is a problem with LIMIT clause, because it will evaluates to true when the cursor returns any number of records less than the limit: it will be %notfound even if the cursor returns 99 records. This is a non-intuitive aspect of the PL/SQL implementation. Anyway, it is better to check the number of entries in the collection.

Here is your code fixed for these two issues:

create or replace procedure ins_act(l_result        OUT        sys_refcursor) as 

 TYPE RSLT IS RECORD  (
 l_id1                   varchar2(32),
 l_id2                   varchar2(32),
 l_id3                   varchar2(32),
 l_pid                   varchar2(16),
 l_ac                    varchar2(32),
 l_activity_date         varchar2(32),
 l_file_id               varchar2(64)) ;

 Type v_reslt is table of RSLT index by PLS_INETEGER;
 var_reslt   v_reslt;

begin
          OPEN l_result FOR 
             SELECT COL1,COL2,COL3,CoUnt(*).....
          Loop

          fetch l_result bulk collect into  var_reslt limit 100;          
          
          exit when var_reslt.count() = 0; 

          --Bulk Insert     
          FORALL I INTO 1..var_reslt.count SAVE EXCEPTIONS
          insert into temp(id1,
                           id2,
                           id3,
                           platform_id,
                           activity_code,
                           update_timestamp,
                           file_id)          
          values 
(to_char(to_date(var_reslt(i).l_id1,'mm/dd/yyyy'),'mm/dd/yyyy'),
              var_reslt(i).l_id2,
              var_reslt(i).l_id3,
              0,
              var_reslt(i).l_ac,
              var_reslt(i).l_activity_date,
              var_reslt(i).l_file_id);
      end loop;
   Commit;
   close l_result;

Exception
 WHEN OTHERS
   THEN    
         --Bulk Exception handling
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
               || ‘: ‘
               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         RAISE;          
end;
/

The final error is procedural. A refcursor is a one-time shot. You read it in the program, which means it is exhausted when you pass it back to the java layer. There are a couple of ways to resolve this, depending on what exactly you're trying to achieve. Post more details regarding your logic.