Различное поведение для REF CURSOR между Oracle 10g и 11g при наличии уникального индекса?

Описание

У меня есть хранимая процедура Oracle, которая работает уже около 7 лет как локально на экземплярах разработки, так и на нескольких клиентских тестовых и производственных экземплярах под управлением Oracle 8, затем 9, затем 10 и недавно 11. Он работал стабильно до обновления до Oracle 11g. В основном, процедура открывает указатель ссылки, обновляет таблицу и завершает работу. В 10g курсор будет содержать ожидаемые результаты, но в 11g курсор будет пустым. После обновления до 11g DML или DDL не изменились. Это поведение согласуется с каждым экземпляром 10g или 11g, который я пробовал (10.2.0.3, 10.2.0.4, 11.1.0.7, 11.2.0.1 - все работает в Windows).

Конкретный код намного сложнее, но он требует объяснения проблема в несколько реалистичном обзоре: у меня есть некоторые данные в таблице заголовков и несколько дочерних таблиц, которые будут выведены в PDF. Таблица заголовков имеет логический столбец (NUMBER (1), где 0 - ложь, а 1 - истина), указывающий, были ли эти данные обработаны.

Представление ограничено отображением только тех строк, которые не были обработаны (представление также присоединяется к некоторым другим таблицам, выполняет некоторые встроенные запросы и вызовы функций и т. Д.). Таким образом, в то время, когда курсор открыт, в представлении отображается одна или несколько строк, затем после открытия курсора запускается оператор обновления, чтобы перевернуть флаг в таблице заголовков, выдается фиксация, затем процедура завершается.

На 10g открывается курсор, он содержит строку, затем оператор обновления меняет флаг, и повторный запуск процедуры не даст никаких данных.

В 11g курсор никогда не содержит строку, это как если бы курсор не открывается до тех пор, пока не будет запущен оператор обновления.

Я обеспокоен тем, что что-то могло измениться в 11g (надеюсь, параметр, который можно настроить), который может повлиять на другие процедуры и другие приложения. Что я' Я хотел бы знать, знает ли кто-нибудь, почему поведение двух версий базы данных различается и можно ли решить проблему без изменения кода.

Обновление 1: Мне удалось отследить проблему до уникального ограничения. Кажется, что когда уникальное ограничение присутствует в 11g, проблема воспроизводится в 100% случаев, независимо от того, запускаю ли я реальный код для реальных объектов или из следующего простого примера.

Обновление 2: Мне удалось полностью исключить вид из уравнения. Я обновил простой пример, чтобы показать, что проблема существует даже при запросе непосредственно к таблице.

Простой пример

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = 'TEST1'
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = 'TEST1';

      COMMIT;

   END proc1;

END pkg1;

Демонстрация анонимного блока

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin first test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end first test');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin second test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end second test');

END; 

Пример того, каким будет результат на 10g:
Кажется, что когда уникальное ограничение присутствует в 11g, проблема воспроизводится в 100% случаев, независимо от того, запускаю ли я реальный код для реальных объектов или из следующего простого примера.

Обновление 2: Мне удалось полностью исключить вид из уравнения. Я обновил простой пример, чтобы показать, что проблема существует даже при запросе непосредственно к таблице.

Простой пример

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = 'TEST1'
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = 'TEST1';

      COMMIT;

   END proc1;

END pkg1;

Демонстрация анонимного блока

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin first test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end first test');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin second test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end second test');

END; 

Пример того, каким будет результат на 10g:
Кажется, что когда уникальное ограничение присутствует в 11g, проблема воспроизводится в 100% случаев, независимо от того, запускаю ли я реальный код для реальных объектов или из следующего простого примера.

Обновление 2: Мне удалось полностью исключить вид из уравнения. Я обновил простой пример, чтобы показать, что проблема существует даже при запросе непосредственно к таблице.

Простой пример

CREATE TABLE tbl1
(
  col1  VARCHAR2(10),
  col2  NUMBER(1)
);

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

/* View is no longer required to demonstrate the problem
CREATE OR REPLACE VIEW vw1 (col1, col2) 
AS 
SELECT col1, col2 
  FROM tbl1 
 WHERE col2 = 0;
*/

CREATE OR REPLACE PACKAGE pkg1
AS
   TYPE refWEB_CURSOR IS REF CURSOR;

   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR);

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 
IS
   PROCEDURE proc1 (crs  OUT  refWEB_CURSOR)
   IS
   BEGIN

      OPEN crs FOR
        SELECT col1
          FROM tbl1
         WHERE col1 = 'TEST1'
           AND col2 = 0;

      UPDATE tbl1
         SET col2 = 1
       WHERE col1 = 'TEST1';

      COMMIT;

   END proc1;

END pkg1;

Демонстрация анонимного блока

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin first test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end first test');

END; 

/* After creating this index, the problem is seen */
CREATE UNIQUE INDEX unique_col1 ON tbl1 (col1);

/* Reset data to initial values */
TRUNCATE TABLE tbl1;

INSERT INTO tbl1 (col1, col2) VALUES ('TEST1', 0);

DECLARE 
   crs1  pkg1.refWEB_CURSOR;

   TYPE rectype1 IS RECORD (
      col1  vw1.col1%TYPE
   );

   rec1  rectype1;
BEGIN 
   pkg1.proc1 ( crs1 );

   DBMS_OUTPUT.PUT_LINE('begin second test');

   LOOP
      FETCH crs1
       INTO rec1;

      EXIT WHEN crs1%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(rec1.col1);

   END LOOP;  

   DBMS_OUTPUT.PUT_LINE('end second test');

END; 

Пример того, каким будет результат на 10g:
начать первый тест
ТЕСТ1
завершить первый тест
начать второй тест
ТЕСТ1
конец второго теста

Пример того, каким будет результат на 11g:
начать первый тест
ТЕСТ1
конец первого теста
начать второй тест
конец второго теста

Пояснение

Я не могу удалить COMMIT, потому что в реальном сценарии процедура вызывается из веб-приложения. Когда поставщик данных на внешнем интерфейсе вызывает процедуру, он в любом случае выдаст неявный COMMIT при отключении от базы данных. Так что, если я удалю COMMIT в процедуре, то да, демонстрация анонимного блока будет работать, но в реальном сценарии не будет, потому что COMMIT все равно произойдет.

Вопрос

Почему 11g ведет себя иначе? Могу ли я что-нибудь сделать, кроме как переписать код?