主页 / user-9689656

Simon's questions

Martin Hope
Simon
Asked: 2024-09-25 23:09:07 +0800 CST
  • 5

大家下午好，

我有一个针对 ORACLE 19 执行的 PLSQL 脚本，它显示了一个我无法理解的错误。

脚本会在重新创建索引之前删除所有现有索引。对象定义在 kind-off 列表中，以防止语句重复

  • 删除不存在的索引是可以的（只需捕获异常并继续）
  • 创建失败的索引应继续创建其余索引，直到最后

我的 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');
    /* first index is wrong because col0 does not exist */
    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('no index ' || 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('ERROR WITH ' || ind_to_create(l_index));
                is_all_index_created := FALSE;
        END;
    END LOOP;

    IF NOT is_all_index_created THEN
        RAISE_APPLICATION_ERROR(-20001, 'PROBLEM CREATING INDEX');
    END IF;

END;
/

exit

当我通过 sqplus 执行脚本时，出现以下错误，我不明白

sqlplus xxxxx/yyyyy@zzzzzz @ubscls_6711.sql

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 17:04:11 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

drop index sst_ind1
drop index sst_ind2
create index sst_ind1 on sst_table(col0)
ERROR WITH create index sst_ind1 on sst_table(col0)
create index sst_ind2 on sst_table(col2)
DECLARE
*
ERROR at line 1:
ORA-20001: PROBLEM CREATING INDEX
ORA-06512: at line 49


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

有人能帮我解决我的问题吗？这可能很简单，但就是找不到原因。

谢谢

oracle