PL/SQL匿名块vs PL/SQL Procedure - ORA-01418:指定的索引不存在

Question

我正在尝试编写简单的PL/SQL过程来在Oracle Schema中重建索引。

代码在作为PL/SQL匿名块编写和执行时运行良好，但在作为PL/SQL过程编写和执行时抛出错误。

请看一看，并回应任何关于如何解决这个小难题的陷阱或建议。

--IDX_REBUILD: Anonymous Block
set timing on;
SET SERVEROUTPUT ON;
DECLARE
    CURSOR Index_Cur IS
        SELECT owner, index_name 
        FROM cjb_indexes 
        WHERE (owner like 'ZIPPY%')
        AND INDEX_TYPE = 'NORMAL' 
        ORDER BY owner, index_name;
    SQL_STMT VARCHAR2(200);
BEGIN
    DBMS_OUTPUT.ENABLE (100000);
    FOR IndexRec IN Index_Cur LOOP
        SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
        DBMS_OUTPUT.PUT_LINE(SQL_STMT);
        EXECUTE IMMEDIATE SQL_STMT;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;
END;
/

....
ALTER INDEX ZIPPY.R157_SDE_ROWID_UK REBUILD
ALTER INDEX ZIPPY.R49_SDE_ROWID_UK REBUILD
ALTER INDEX ZIPPY.R50_SDE_ROWID_UK REBUILD
ALTER INDEX ZIPPY.R51_SDE_ROWID_UK REBUILD
ALTER INDEX ZIPPY.R52_SDE_ROWID_UK REBUILD

    
PL/SQL procedure successfully completed.


--IDX_REBUILD:  PROCEDURE
set timing on;
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE CJB_IDX_REBUILD
IS
    CURSOR Index_Cur IS
        SELECT owner, index_name 
        FROM cjb_indexes 
        WHERE (owner like 'ZIPPY%')
        AND INDEX_TYPE = 'NORMAL' 
        ORDER BY owner, index_name;
    SQL_STMT VARCHAR2(200);
BEGIN
    DBMS_OUTPUT.ENABLE (100000);
    FOR IndexRec IN Index_Cur LOOP
        SQL_STMT := ('ALTER INDEX ' || IndexRec.owner || '.' || IndexRec.index_name || ' REBUILD');
        DBMS_OUTPUT.PUT_LINE(SQL_STMT);
        EXECUTE IMMEDIATE SQL_STMT;
        DBMS_OUTPUT.NEW_LINE;
    END LOOP;
 /

execute CJB_IDX_REBUILD;

Procedure created.

Elapsed: 00:00:00.01
zippy> execute CJB_IDX_REBUILD;
ALTER INDEX ZIPPY.A108_PK REBUILD
BEGIN CJB_IDX_REBUILD; END;

*
ERROR at line 1:
ORA-01418: specified index does not exist
ORA-06512: at "CJB.CJB_IDX_REBUILD", line 15
ORA-06512: at line 1

Answer 1

您以用户身份登录，该用户只能通过角色更改这些索引；但是存储过程不能使用您的角色。过程的所有者可能直接需要ALTER ANY INDEX授权。

Answer 2

从中执行该过程的用户没有权限更改此设置。