PL/SQL Anonymous Block vs PL/SQL Procedure - ORA-01418: specified index does not exist

Question

I am attempting to write simple PL/SQL procedure to rebuild indexes in an Oracle Schema.

The code works fine when written and executed as a PL/SQL anonymous block but throws an error when written and executed as a PL/SQL procedure.

Please have a look and respond with any gotchas or advice on how to solve this little puzzler.

--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

You're logged in as a user which can alter these indexes only via a role; but a stored procedure cannot use your role. The owner of the procedure may need the ALTER ANY INDEX grant directly.