Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Myxa_beer
Member

Откуда:
Сообщений: 59
На базе существуют 2 типа.

CREATE OR REPLACE Type TYP_CRO_Controls_OBJ
As
  Object (Id    Number(1),
          Type  Number(1),
          Text  Varchar2(250))

И
CREATE OR REPLACE Type TYP_CRO_Controls_Tab
As
  Table Of TYP_CRO_Controls_Obj


Далее есть функция

Create Or Replace Function Test_Func(p_Qrs_Cursor Out Sys_Refcursor)
  Return Number
Is
  v_ret Number(10)            := 1;
  v_tab TYP_CRO_Controls_Tab  := TYP_CRO_Controls_Tab();
  p_Contractid  Number(10) := 0;
  p_Histid      Number(10)  := 0;
Begin
  Savepoint trun_Test_Func;
  
  /*Любая DML команда*/
  Delete From t_1_1;
  
  /*Инициализация и запуск контролей*/
  P$cro_Controlss.CrO_Init(p_IsAll        => 0,
                           p_IsAll_Value  => 0,
                           p_FltrList     => typ_cro_controls_tab(typ_cro_controls_obj(Id   => 3,
                                                                                       Type => 1,
                                                                                       Text => Null),
                                                                  typ_cro_controls_obj(Id   => 4,
                                                                                       Type => 1,
                                                                                       Text => Null)));
  
  /*Ошибки контролей складируются в v_tab*/
  v_tab := P$cro_Controlss.CrO_Check( p_CID => p_Contractid,
                                      p_HID => p_Histid);
                
  If v_tab.Count > Cr_Variables.c_Status_0
  Then
    Goto finish;
  End If;
  
  <<finish>>
  
  If v_tab.Count() > 0
  Then
    Open p_Qrs_Cursor For
    Select  err.Type,
            err.Text
    From Table(v_tab) err;
  End If;
  
  If v_ret != 1
  Then
    Rollback To trun_Test_Func;
  End If;
  
  Return v_ret;
End Test_Func;


Собственно проблема. Когда в коллекции ошибок v_tab существуют записи и отрабатывает Rollback и DML(insert, delete, update) команда обработала хотя бы 1 строку, после вызова функции нельзя открыть курсор p_Qrs_Cursor. Ошибка - Number:1002 ORA-01002: fetch out of sequence. Однако, если DML команда не обработала ни 1 строки, при прочих равных условиях, после вызова функции p_Qrs_Cursor открывается и можно посмотреть набор ошибок. Подскажите, что не так?

Заранее благодарен.
15 авг 14, 18:21    [16447907]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
ten
Member

Откуда: Екатеринбург
Сообщений: 1672
Myxa_beer,
imho, после отката, если есть что откатывать, то сервер честно выполняет rollback и инвалидирует ref cursor не пытаясь определить изменены реально данные в курсоре или нет. Если откатывать нечего, то инвалидации курсора не происходит. Упрощенный Ваш пример:
+
SQL> 
SQL> drop table my_tab;

Таблица удалена.

SQL> create table my_tab as select * from dual;

Таблица создана.

SQL> 
SQL> var g_cur refcursor;
SQL> 
SQL> begin
  2    Savepoint SP1;
  3    delete from my_tab;
  4    open :g_cur for select * from dual;
  5    rollback to SP1;
  6  end;
  7  /

Процедура PL/SQL успешно завершена.

SQL> print g_cur;
ERROR:
ORA-01002: выборка из последовательности 

строки не выбраны

SQL> 
SQL> spool off

Как вариант обхода ошибки - можно заполнять возвращаемый курсор в самом конце процедуры после всех rollback и т.п.
18 авг 14, 11:27    [16454715]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Elic
Member

Откуда:
Сообщений: 29976
STFF видимость отменённого будущего из прошлого
18 авг 14, 11:33    [16454747]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Myxa_beer
Member

Откуда:
Сообщений: 59
ten, Elic, спасибо. Однако я ожидал, что v_tab локальная коллекция. И что на неё, так же как и на локальные переменные не должен был влиять откат транзакции.
18 авг 14, 12:14    [16454981]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Elic
Member

Откуда:
Сообщений: 29976
Myxa_beer
Однако я ожидал, что v_tab локальная коллекция. И что на неё, так же как и на локальные переменные не должен был влиять откат транзакции.
У тебя прежде всего запрос, у которого есть момент согласованности.
И как раз в твоём случае лечится легко: правильной последовательностью open...for vs rollback.

P.S. Goto finish мало того, что моветон, так и всунут весьма и весьма к месту
18 авг 14, 12:29    [16455077]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Myxa_beer
Member

Откуда:
Сообщений: 59
Elic, это я для краткости.
18 авг 14, 12:55    [16455366]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
stax..
Guest
Elic
STFF видимость отменённого будущего из прошлого

вот уже и будущее наступило

а я так и не понял зачем слетать по ORA-01002, если дуал не менялась

.....
stax
18 авг 14, 14:57    [16456588]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
stax..
Elic
STFF видимость отменённого будущего из прошлого

вот уже и будущее наступило

а я так и не понял зачем слетать по ORA-01002, если дуал не менялась

.....
stax
Про какой случай с dual речь?
А то в Оракле что-то могут наоптимизировать, что потом будет вызывать некоторое недоумение
create table t1 as select rownum id from all_objects where rownum <= 3;
create table t2 as select rownum id from all_objects where rownum <= 4;

begin
  savepoint a;
  insert into t1 values (4);
  for i in (select * from t1) loop
    update t2 set id = -id where id = i.id;
    if i.id = 2 then
      rollback to a;
    end if;
  end loop;
end;
/

select * from t1;
select * from t2;

10.2, 11.2
SQL> select * from t1;

        ID
----------
         1
         2
         3

SQL> select * from t2;

        ID
----------
         1
         2
        -3
        -4

8i
ORA-01002: fetch out of sequence
7 дек 15, 03:03    [18521810]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Без "оптимизации" фетча по 100 строк поведение "более консистентное".
7 дек 15, 03:14    [18521812]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18483
9i
demo56> create table t1 as select rownum id from all_objects where rownum <= 3;

Table created.

demo56> create table t2 as select rownum id from all_objects where rownum <= 4;

Table created.

demo56> 
demo56> begin
  2    savepoint a;
  3    insert into t1 values (4);
  4    for i in (select * from t1) loop
  5      update t2 set id = -id where id = i.id;
  6      if i.id = 2 then
  7        rollback to a;
  8      end if;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

demo56> 
demo56> select * from t1;

        ID
----------
         1
         2
         3

demo56> select * from t2;

        ID
----------
         1
         2
        -3
         4

demo56> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

А вот почему после завершения в T1 нет 4 строки я не понял
7 дек 15, 03:18    [18521813]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
+ plsql_optimize_level = 0
SQL> create table t1 as select rownum id from all_objects where rownum <= 3;

Table created.

SQL> create table t2 as select rownum id from all_objects where rownum <= 4;

Table created.

SQL>
SQL> alter session set plsql_optimize_level = 0;

Session altered.

SQL> begin
  2    savepoint a;
  3    insert into t1
  4      select max(id) + 1 from t1;
  5    for i in (select * from t1) loop
  6      update t2 set id = -id where id = i.id;
  7      if i.id = 2 then
  8        rollback to a;
  9      end if;
 10    end loop;
 11  end;
 12  /
begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5


SQL>
SQL> select * from t1;

        ID
----------
         1
         2
         3

SQL> select * from t2;

        ID
----------
         1
         2
         3
         4
7 дек 15, 03:24    [18521815]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Вячеслав Любомудров,

Не только rollback отработал но и фетчи (точнее один фетч) после него.
7 дек 15, 03:27    [18521816]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста разобраться с курсором на основе коллекции  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18483
Понедельник
7 дек 15, 03:31    [18521817]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить