Описание
У меня есть хранимая процедура 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 - false, а 1 - истина), в котором указывается, были ли эти данные еще обработаны.
Вид ограничивается только отображением строк, которые не были обработаны (представление также присоединяется к некоторым другим таблицам, делает некоторые встроенные запросы и вызовы функций и т.д.). Таким образом, в момент, когда курсор открывается, представление показывает одну или несколько строк, а затем, после того, как курсор открыт, выполняется инструкция обновления, чтобы перевернуть флаг в таблице заголовков, после чего завершается процедура фиксации.
В 10g курсор открывается, он содержит строку, а затем оператор обновления переворачивает флаг и второй раз не выполняет никаких данных.
В 11g курсор never содержит строку, это как если бы курсор не открывался до тех пор, пока не выполнится оператор обновления.
Я обеспокоен тем, что что-то изменилось в 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:
начать первый тест
TEST1
конец первого теста
начать второй тест
TEST1
конец второго теста
Пример того, что будет выводиться на 11g:
начать первый тест
TEST1
конец первого теста
начать второй тест
конец второго теста
Разъяснение
Я не могу удалить COMMIT, потому что в сценарии реального мира процедура вызывается из веб-приложения. Когда поставщик данных на лицевой стороне вызывает процедуру, он будет вызывать неявный COMMIT при любом отключении от базы данных. Поэтому, если я удалю COMMIT в процедуре, то да, анонимная демонстрация блоков будет работать, но сценарий реального мира не будет, потому что COMMIT все равно случится.
Вопрос
Почему 11g ведут себя по-другому? Есть ли что-нибудь, что я могу сделать, кроме повторной записи кода?
