Как вывести исключения в консоль полученные в FORALL ... SAVE EXCEPTIONS?

Question

Мне нужно вывести строки в консоль, где в kol_vo больше 15. Всё работает кроме исключений.

В таблице vitrina_dannih есть ограничение на запись, записываются значения, где kol_vo меньше 15. Идея была такая, пока проверяются данные на время (с 0 до 4 утра) и записываются в vitrina_dannih, происходят ошибки, что в столбцах kol_vo есть записи больше 15, запоминает-сохраняет, и выводит стоки на экран.

Возможно, что-то не так сделала:

DECLARE
  TYPE kol_vo_list_t IS TABLE OF VARCHAR2(32767);
  kol_vo_with_errors kol_vo_list_t := kol_vo_list_t();
  
  CURSOR allrows_cur IS
    SELECT * FROM prodal where EXTRACT(HOUR FROM prodal.p_data) < 4;

  TYPE prodal_aat IS TABLE OF prodal%ROWTYPE INDEX BY PLS_INTEGER;
  l_prodal prodal_aat;
  
  bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_errors, -02290);
  
BEGIN
  OPEN allrows_cur;
  FETCH allrows_cur BULK COLLECT
    INTO l_prodal LIMIT 2000;
  CLOSE allrows_cur;

  FORALL l_index IN l_prodal.FIRST .. l_prodal.LAST SAVE EXCEPTIONS
    INSERT INTO vitrina_dannih
      (vd_prodavec, vd_tovar_id, vd_kol_vo, vd_data_pr, vd_data_vloj)
    VALUES
      (l_prodal(l_index).kto_prodal,
       l_prodal(l_index).tovar_id,
       l_prodal(l_index).kol_vo,
       l_prodal(l_index).p_data,
       sysdate);
EXCEPTION
  WHEN bulk_errors THEN
    DBMS_OUTPUT.put_line('Вставлено ' || SQL%ROWCOUNT || ' строк.');
    FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Error ' || indx || ' occurred during ' ||
                           'iteration ' || SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX ||
                           ' updating name to ' ||
                           kol_vo_with_errors(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX));
      DBMS_OUTPUT.PUT_LINE('Oracle error is ' ||
                           SQLERRM(SQL%BULK_EXCEPTIONS(indx).ERROR_CODE));
    END LOOP;
END;

Подскажите, как вывести исключения в консоль?

PS: В PLSQL Developer 14 set serveroutput on, не нужен. Выяснилась проблема, если закомментить весь EXCEPTION кроме SAVE EXCEPTIONS, то все работает, а если и его, то ошибка. Как вывести SAVE EXCEPTIONS на консоль?

Answer 1

Давайте посмотрим на простом воспроизводимом примере.

если закомментить весь EXCEPTION кроме SAVE EXCEPTIONS, то все работает [...]

create table tab (name varchar2 (8) primary key, val int check (val<15))
/
declare
    type tabrows is table of tab%rowtype;
    newrows tabrows;
    cursor cur is     
        select 'aaa',  5 from dual union all 
        select 'bbb', 10 from dual union all 
        select 'ccc', 15 from dual;
begin   
    open cur;
    fetch cur bulk collect into newrows;

    forall i in indices of newrows save exceptions 
        insert into tab values (newrows(i).name,newrows(i).val); 
end;
/
ORA-24381: error(s) in array DML

select * from tab
/
NAME     VAL
-------- ---
aaa        5
bbb       10

Как видите, не всё работает. Хотя безошибочные записи вставились, но выполнение заканчилось исключением.

Его не стоило игнорировать, в нём причина ошибки в вопросе - попытка обработать исключения с кодом -02290 вместо -24381, то есть блок EXCEPTION вообще не выполняется.

Кроме того, как вывести SAVE EXCEPTIONS на консоль?, исключения следует обрабатывать, а не выводить на консоль. Поэтому, целесообразней сразу записывать исключения из ассоциативной коллекции SQL%BULK_EXCEPTIONS в какую-либо таблицу.

Вот полностью работоспособный пример (на фиддле):

create table errlog (tabname varchar2 (8), key varchar2 (96), errm varchar2 (256))
/
declare
    type tabrows is table of tab%rowtype;
    newrows tabrows;
    cursor cur is     
        select 'aaa',  5 from dual union all 
        select 'bbb', 10 from dual union all 
        select 'ccc', 15 from dual;
begin   
    open cur;
    fetch cur bulk collect into newrows;
    declare
        dmlerr exception;
        pragma exception_init (dmlerr, -24381);
        errkey varchar2 (8);
        errmsg varchar2 (256);
    begin
        forall i in indices of newrows save exceptions 
            insert into tab values (newrows(i).name,newrows(i).val); 
    exception when dmlerr then null;
        for i in 1..sql%bulk_exceptions.count loop
            errkey := newrows(sql%bulk_exceptions(i).error_index).name; 
            errmsg := sqlerrm (-(sql%bulk_exceptions(i).error_code));
            insert into errlog values ('tab', errkey, errmsg);
        end loop;
    end;
end;
/
PL/SQL procedure successfully completed.

Как посмотреть результат в консоли:

select * from tab
/
NAME     VAL
-------- ---
aaa        5
bbb       10

select * from errlog
/
TABNAME  KEY      ERRM                                            
-------- -------- ------------------------------------------------
tab      ccc      ORA-02290: check constraint (.) violated

Answer 2

Это тестовое задание для прохождения собеседования. Решил по своему, было бы интересно, как бы его решил профессионал. Если кому-то интересно, могу скинуть текст задания.

Вот мое решение:


    DECLARE
        TYPE sell_list IS TABLE OF SELLERS%ROWTYPE;
        lvl_sell_lst sell_list := sell_list();      
    BEGIN   
        SELECT *
        BULK COLLECT INTO lvl_sell_lst 
          FROM sellers 
         WHERE to_char(sell_time, 'hh24:mi:ss') between to_char('00:00:00') AND to_char('04:00:00');
         
        DECLARE
            forallexcp EXCEPTION;
            PRAGMA exception_init (forallexcp , -24381); 
        BEGIN
         FORALL i IN lvl_sell_lst.first..lvl_sell_lst.last SAVE EXCEPTIONS    
            INSERT INTO suspicious_data(seller_name, product_amount, sell_time)
            values(lvl_sell_lst(i).seller_name,
                   lvl_sell_lst(i).product_amount, 
                   lvl_sell_lst(i).sell_time);
        EXCEPTION WHEN forallexcp THEN
            dbms_output.put_line ('Вставляемые значения не удовлетворяют названному проверочному ограничению.');
        END;            
    END;

БЛОГ НА HUSL

Как вывести исключения в консоль полученные в FORALL ... SAVE EXCEPTIONS?

Ответы (2 шт):