Skip to Main Content
  • Questions

  • DROP Not work to run in sqlplus as file ( @file.sql )

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Waldo.

Asked: August 25, 2022 - 5:30 am UTC

Last updated: August 30, 2022 - 2:32 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

This not work: sqlplus ---> @drop_indexes.sql

DROP_INDEXES.sql
DECLARE
index_count INTEGER;
BEGIN
SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_1' AND TABLE_OWNER = 'TO';
IF index_count > 0 THEN
EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_1';
END IF;
SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_2' AND TABLE_OWNER = 'TO';
IF index_count > 0 THEN
EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_2';
END IF;
............
END;
/

Then I run @CREATE_INDEX.sql to create the indexes, and it says the indexes already exist. That is, the indexes were not dropped when running DROP_INDEXES.sql

However if I run the scripts ( DROP first and CREATE later) directly (not as a file) in SqlDeveloper, it does work.
What can I do, if I need to run both as file script from sqlplus


Thank you very much for the help you can give me.

Waldo Gómez Alvarez - Viña del Mar - Chile


and Connor said...

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>

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library