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