Oracle 11gR2 FORALL SAVE EXCEPTIONS в представлении базы данных не работает

Я хочу воспользоваться преимуществами массовых операций Oracle DML и обработки исключений. Мое требование - выполнить операцию DML в представлении базы данных, где он выполняет некоторые проверки с помощью триггера представления, а затем, наконец, вставляет / обновляет базовую таблицу. Однако Oracle FORALL .. SAVE EXCEPTIONS, похоже, не улавливает ошибку проверки, возникающую в представлении. Это ограничение / ограничение SAVE EXCEPTION, когда оно работает только с таблицей базы данных, но не с просмотром? Документация Oracle, похоже, тоже не упоминает об этом. Ниже приведены мои тестовые коды (на основе модификации Обработка исключений в массовых операциях):

Создать таблицу:

create table exception_test (
  id  number(10) not null
);

Создайте представление на столе:

create or replace view exception_test_v as
select exception_test.id id
    ,sysdate daytime
from exception_test;

Создайте триггер в представлении:

create or  replace trigger iud_exception_test
    instead of insert or update or delete on exception_test_v
    for each row
declare

begin
    if inserting then 

        if nvl(:new.id, 0) = 0 then 
            RAISE_APPLICATION_ERROR(-20815, 'ID must not be null!'); 
        end if;

        insert into exception_test (id) values (:new.id);

    end if;    

  end;
/

Тестовый код DML в представлении базы данных:

declare
  TYPE t_tab IS TABLE OF exception_test_v%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER; 

  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL; 

  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test_v (id)
      VALUES (l_tab(i).id);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;

Ошибка тестового кода в индексе 50 из триггера представления вместо обработки полных 100 вставок и перехвата ошибок в индексах 50 и 51 для последующей проверки.

Мы будем благодарны за любые отзывы об этом!


view oracle bulkinsert plsql exception-handling
person Potoroo    schedule 06.10.2014    source источник
comment
Можете ли вы выполнить одну тестовую вставку в представление и проверить, возникает ли исключение ???   -  person psaraj12    schedule 06.10.2014
comment
Да, используя мой тестовый код выше, исключение возникает ORA-20815 из триггера вместо ORA-24381 из SAVE EXCEPTIONS. Поэтому я не могу перехватывать исключения сразу, потому что операция FORALL немедленно выдаст ошибку.   -  person Potoroo    schedule 07.10.2014

Ответы (2)


arrow_upward
1
arrow_downward

Забудьте сейчас о триггере INSTEAD OF. Давайте сосредоточимся на BULK EXCEPTIONS части. В моем тестовом примере есть таблица с ID в качестве NOT NULL столбца. Взгляд на это. Я буду использовать FORALL INSERT и попытаюсь вставить значения NULL в VIEW через индексы 50 и 51 в коллекции. Ожидается получение EXCEPTION при попытке вставить NULL в VIEW:

SQL> create table exception_test (
  2    ID  NUMBER(10) NOT NULL
  3  );

Table created.

SQL>
SQL>
SQL> create or replace view exception_test_v as
  2  select exception_test.id id
  3      ,SYSDATE DAYTIME
  4  from exception_test;

View created.

SQL>
SQL> declare
  2    TYPE t_tab IS TABLE OF exception_test_v%ROWTYPE;
  3
  4    l_tab          t_tab := t_tab();
  5    l_error_count  NUMBER;
  6
  7    ex_dml_errors EXCEPTION;
  8    PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
  9  BEGIN
 10    -- Fill the collection.
 11    FOR i IN 1 .. 100 LOOP
 12      l_tab.extend;
 13      l_tab(l_tab.last).id := i;
 14    END LOOP;
 15
 16    -- Cause a failure.
 17    l_tab(50).id := NULL;
 18    l_tab(51).id := NULL;
 19
 20    EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';
 21
 22    -- Perform a bulk operation.
 23    BEGIN
 24      FORALL I IN 1 .. L_TAB.COUNT SAVE EXCEPTIONS
 25        INSERT INTO exception_test_v (id)
 26        VALUES (L_TAB(I).ID);
 27    EXCEPTION
 28      WHEN EX_DML_ERRORS THEN
 29      dbms_output.put_line('Inside exception');
 30        l_error_count := SQL%BULK_EXCEPTIONS.count;
 31        DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
 32        FOR I IN 1 .. L_ERROR_COUNT LOOP
 33          DBMS_OUTPUT.put_line('Error: ' || i ||
 34            ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
 35            ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 36        END LOOP;
 37    END;
 38  END;
 39  /
Inside exception
Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from exception_test;

  COUNT(*)
----------
        98

Итак, вы видите, что SAVE EXCEPTIONS имеет две ошибки.

Проблема с вашим тестовым примером заключается в том, что код никогда не попадает в блок EXCEPTION. Вы можете попробовать удалить RAISE_APPLICATION_ERROR и посмотреть. Блок PL / SQL будет выполняться нормально. Ошибка, возникшая из-за инициирующего события, не 24381, поэтому код никогда не переходит в блок исключения.

person Lalit Kumar B    schedule 06.10.2014
comment
Op проверьте XXX_UPDATABLE_COLUMNS (XXX может быть ALL, USER или DBA) для этой таблицы, чтобы узнать, обновляется ли столбец идентификатора. Как я вижу из этого ответа, столбец обновляется, поэтому INSTEAD OF Trigger не требуется docs.oracle.com/cd/E11882_01/appdev.112/e25519/ - person psaraj12; 06.10.2014
comment
Всем спасибо за ответ. Мне нужен триггер вместо триггера, так как мне нужно выполнить серию проверок перед вставкой данных в таблицу. raise_application_error в моем исходном сообщении - это всего лишь образец, который я хочу проверить, чтобы увидеть, SAVE EXCEPTION может его поймать или нет. Но похоже, что SAVE EXCEPTION действительно работает только с ошибками, связанными с прямыми операциями DML (?). Я пытаюсь найти документацию Oracle, чтобы подтвердить свое предположение, но все равно безуспешно. - person Potoroo; 06.10.2014

arrow_upward
0
arrow_downward

Можете ли вы попробовать что-то вроде приведенного ниже, где вы ловите исключение, возникшее в INSTEAD OF TRIGGER

  declare
    TYPE t_tab IS TABLE OF exception_test_v%ROWTYPE;

    l_tab          t_tab := t_tab();
    l_error_count  NUMBER; 

    ex_dml_errors EXCEPTION;
    ex_trigger_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(ex_trigger_errors, -20815);
    PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
  BEGIN
    -- Fill the collection.
    FOR i IN 1 .. 100 LOOP
      l_tab.extend;
      l_tab(l_tab.last).id := i;
    END LOOP;

    -- Cause a failure.
    l_tab(50).id := NULL;
    l_tab(51).id := NULL; 

    EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

    -- Perform a bulk operation.
    BEGIN
      FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
        INSERT INTO exception_test_v (id)
        VALUES (l_tab(i).id);
    EXCEPTION
      WHEN ex_dml_errors THEN
        l_error_count := SQL%BULK_EXCEPTIONS.count;
        DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
        FOR i IN 1 .. l_error_count LOOP
          DBMS_OUTPUT.put_line('Error: ' || i || 
            ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
            ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
        WHEN ex_trigger_errors THEN
        l_error_count := SQL%BULK_EXCEPTIONS.count;
        DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
        FOR i IN 1 .. l_error_count LOOP
          DBMS_OUTPUT.put_line('Error: ' || i || 
            ' Array Index captured in instead of trigger: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
            ' Message captured in instead of trigger: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        END LOOP;
    END;
  END;
person psaraj12    schedule 07.10.2014
comment
Спасибо, я попытался использовать WHEN OTHERS THEN, ошибка обнаружена в индексе 50 во время FORALL INSERT, но затем цикл просто останавливается здесь, не переходя к остальной части INSERTs, даже если указан SAVE EXCEPTIONS. - person Potoroo; 08.10.2014
comment
Более того, если исключение ex_dml_errors -24381 не возникает, то нет смысла использовать SAVE EXCEPTIONS, поскольку ошибки больше не обрабатываются массово. - person Potoroo; 08.10.2014