Are you sure your script is running at all ? Because when I try it
SQL> DECLARE
2 index_count INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_1' AND TABLE_OWNER = 'TO';
5 IF index_count > 0 THEN
6 EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_1';
7 END IF;
8 SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_2' AND TABLE_OWNER = 'TO';
9 IF index_count > 0 THEN
10 EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_2';
11 END IF;
12 END;
13 /
EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_1';
*
ERROR at line 6:
ORA-06550: line 6, column 9:
PLS-00103: Encountered the symbol "INMEDIATE" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":= was inserted before "INMEDIATE" to continue.
ORA-06550: line 10, column 9:
PLS-00103: Encountered the symbol "INMEDIATE" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":= was inserted before "INMEDIATE" to continue.Assuming this was just a typo when you entered the question....when I fix it, all is fine
SQL> create table t as select * from dba_objects;
Table created.
SQL> create index IDX_XX_1 on t( owner );
Index created.
SQL> create index IDX_XX_2 on t( object_name );
Index created.
SQL>
SQL> set echo on
SQL> @x:\temp\asktom_drop_index
SQL> DECLARE
2 index_count INTEGER;
3 BEGIN
4 SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_1' AND TABLE_OWNER = 'MCDONAC';
5 IF index_count > 0 THEN
6 EXECUTE IMMEDIATE 'DROP INDEX MCDONAC.IDX_XX_1';
7 END IF;
8 SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_2' AND TABLE_OWNER = 'MCDONAC';
9 IF index_count > 0 THEN
10 EXECUTE IMMEDIATE 'DROP INDEX MCDONAC.IDX_XX_2';
11 END IF;
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> create index IDX_XX_1 on t( owner );
Index created.
SQL> create index IDX_XX_2 on t( object_name );
Index created.
SQL>