PL/SQL скрипт вызывает ‘ОШИБКА на строке 1 DECLARE’, я не вижу причины.

Добрый день, сообщество,

У меня есть скрипт PLSQL, выполняемый в ORACLE 19, который выдает ошибку, которую я не понимаю.

Скрипт удаляет любой существующий индекс перед его восстановлением. Объекты определены в виде списка, чтобы избежать повторения операторов.

• Удаление несуществующего индекса нормально (просто перехватить исключение и продолжить)
• Создание индекса, который вызывает ошибку, должно продолжаться до завершения создания оставшихся индексов

Мой скрипт PLSQL выглядит так:

set serveroutput on;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

DECLARE
    index_not_exists EXCEPTION;
    table_not_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT (index_not_exists, -1418);
    PRAGMA EXCEPTION_INIT (table_not_exists, -942);

    TYPE ind_list IS TABLE OF VARCHAR(100) NOT NULL;
    ind_to_drop ind_list := ind_list('sst_ind1', 'sst_ind2');
    /* первый индекс неверен, потому что col0 не существует */
    ind_to_create ind_list := ind_list('create index sst_ind1 on sst_table(col0)', 'create index sst_ind2 on sst_table(col2)');

    sql_to_exec varchar(200);
    is_all_index_created boolean := TRUE;

BEGIN
    BEGIN
        EXECUTE IMMEDIATE 'drop table sst_table cascade constraints purge';
    EXCEPTION
        WHEN table_not_exists THEN
        NULL;
    END;

    EXECUTE IMMEDIATE 'create table sst_table (col1 number, col2 number)';
    EXECUTE IMMEDIATE 'create index sst_ind1 on sst_table (col1)';
    EXECUTE IMMEDIATE 'create index sst_ind2 on sst_table (col2)';

    FOR l_index IN ind_to_drop.FIRST..ind_to_drop.LAST LOOP
        BEGIN
            sql_to_exec := 'drop index ' || ind_to_drop(l_index);
            dbms_output.put_line(sql_to_exec);
            EXECUTE IMMEDIATE sql_to_exec;
        EXCEPTION
            WHEN index_not_exists THEN
                dbms_output.put_line('нет индекса ' || ind_to_drop(l_index));
        END;
    END LOOP;

    FOR l_index IN ind_to_create.FIRST..ind_to_create.LAST LOOP
        BEGIN
            dbms_output.put_line(ind_to_create(l_index));
            EXECUTE IMMEDIATE ind_to_create(l_index);
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('ОШИБКА С ' || ind_to_create(l_index));
                is_all_index_created := FALSE;
        END;
    END LOOP;

    IF NOT is_all_index_created THEN
        RAISE_APPLICATION_ERROR(-20001, 'ПРОБЛЕМА СОЗДАНИЯ ИНДЕКСА');
    END IF;

END;
/

exit

Когда я выполняю свой скрипт через sqplus, я получаю НИЖЕУКАЗАННУЮ ОШИБКУ, которую не понимаю

sqlplus xxxxx/yyyyy@zzzzzz @ubscls_6711.sql

SQL*Plus: Выпуск 11.2.0.4.0 Производственный на Ср Сен 25 17:04:11 2024

Авторские права (c) 1982, 2013, Oracle. Все права защищены.

Подключено к:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Производственный

drop index sst_ind1
drop index sst_ind2
create index sst_ind1 on sst_table(col0)
ОШИБКА С create index sst_ind1 on sst_table(col0)
create index sst_ind2 on sst_table(col2)
DECLARE
*
ОШИБКА на строке 1:
ORA-20001: ПРОБЛЕМА СОЗДАНИЯ ИНДЕКСА
ORA-06512: на строке 49

Отключено от Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Производственный

Не могли бы вы помочь мне решить мою проблему. Это вероятно просто, но я не могу найти причину.

Спасибо

Скрипт делает то, что вы сказали, чтобы он сделал… ваш вопрос утверждает, что:

• создание индекса, которое вызывает ошибку, должно продолжаться до завершения создания оставшихся индексов

В коде есть комментарий, который говорит:

первый индекс неверен, потому что col0 не существует

Вывод показывает:

create index sst_ind1 on sst_table(col0)
ОШИБКА С create index sst_ind1 on sst_table(col0)

… поэтому создание этого индекса терпит неудачу, как и ожидалось (скрывая настоящую ошибку, что не полезно, как сказал @Koen); затем он продолжает и показывает:

create index sst_ind2 on sst_table(col2)

… что, очевидно, проходит успешно. Так что оно продолжало создавать оставшиеся индексы.

Вы используете флаг is_all_index_created, чтобы отслеживать любые проблемы. Он устанавливается в false из-за ожидаемого сбоя. Затем в конце вашего кода у вас есть:

    IF NOT is_all_index_created THEN
        RAISE_APPLICATION_ERROR(-20001, 'ПРОБЛЕМА СОЗДАНИЯ ИНДЕКСА');
    END IF;

и это именно та ошибка, которую вы видите:

DECLARE
*
ОШИБКА на строке 1:
ORA-20001: ПРОБЛЕМА СОЗДАНИЯ ИНДЕКСА
ORA-06512: на строке 49

Вы получаете ошибку, потому что ваш код говорит поднять эту ошибку, так что нет ничего, что исправлять, учитывая, что вы хотите/ожидаете, что создание первого индекса потерпит неудачу.

В начале скрипта у вас есть

WHENEVER SQLERROR EXIT SQL.SQLCODE;

так что ваш скрипт выйдет с кодом -20001, что указывает на сбой, а не с нулем, что указывает на успех. Однако вы, возможно, не увидите -20001, он может быть преобразован в меньший диапазон; Скрипты, работающие в Unix, оборачиваются, если код выхода находится вне допустимого диапазона, поэтому вы можете увидеть -33 или 233, к примеру. (Что объясняет, почему это следует избегать, так как некоторые фактические коды ошибок обернутся в ноль и будут выглядеть как успех… безопаснее использовать ... EXIT FAILURE)

Скрипт обрабатывает любые поднятые исключения, но не показывает, в чем ошибка. Обратите внимание на этот блок в скрипте.

        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('ОШИБКА С ' || ind_to_create(l_index));
                is_all_index_created := FALSE;
        END;

Что он делает: Если возникает любая ошибка, то выводится на экран, что… произошла ошибка.

Это не очень полезно. Есть несколько встроенных функций, которые предоставляют вам больше информации. SQLERRM дает код ошибки и описание ошибки, SQLCODE просто показывает код ошибки, dbms_utility.format_error_backtrace показывает полный стек.

Для вашего случая SQLERRM должно быть достаточно.

        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('ОШИБКА С ' || ind_to_create(l_index));
                dbms_output.put_line(SQLERRM);
                is_all_index_created := FALSE;
        END;