Блог

В приведенном ниже примере у меня есть SP, который вернет SYS_REFCURSOR в качестве параметра OUT службе Java, и в том же SP он вставит результат SYS_REFCURSOR в другой пример таблицы — temp. Когда я выполняю SP, он выдает ошибку

ORA-01002: ошибка извлечения из последовательности

Я вижу, что данные вставляются, но служба не возвращает никакого РЕФКУРСОРА службе.

 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_res<

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_resu<

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

Несколько проблем с этим кодом.

Во-первых, вы проверяете, нашел ли курсор что-либо после оператора FORALL. Это неправильно, потому что оператор FORALL пытается обработать курсор, даже когда курсор находится %notfound . Это может быть источником вашего исключения ORA-01002.

Вторая ошибка является незначительной. Проверка наличия курсора %notfound — это проблема с предложением LIMIT, потому что она будет оцениваться true , когда курсор вернет любое количество записей, меньшее предела: это будет %notfound , даже если курсор вернет 99 записей. Это неинтуитивный аспект реализации PL / SQL. В любом случае, лучше проверить количество записей в коллекции.

Вот ваш код, исправленный для этих двух проблем:

 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_res<

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_resu<

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

Последняя ошибка является процедурной. Рефкурсор — это одноразовый прием. Вы читаете его в программе, что означает, что он исчерпан, когда вы передаете его обратно на уровень java. Есть несколько способов решить эту проблему, в зависимости от того, чего именно вы пытаетесь достичь. Опубликуйте более подробную информацию о вашей логике.