oracle drop index if exists

indexing, oracle

Solution

DECLARE
   COUNT_INDEXES   INTEGER;
BEGIN
   SELECT COUNT ( * )
     INTO COUNT_INDEXES
     FROM USER_INDEXES
    WHERE INDEX_NAME = 'myIndexName';
   -- Edited by UltraCommit, October 1st, 2019
   -- Accepted answer has a race condition.
   -- The index could have been dropped between the line that checks the count
   -- and the execute immediate
   IF COUNT_INDEXES > 0
   THEN
      EXECUTE IMMEDIATE 'DROP INDEX myIndexName';
   END IF;
END;
/

Problem

How do you drop an index only if it exists? It seems simple but I did found anything on the net. The idea is to drop it only if it exists, because if not, I will have an error and my process stops. I found this to find if the index exists: ``` select index_name from user_indexes where table_name = 'myTable' and index_name='myIndexName' ``` But I don't know how to put it together with ``` DROP INDEX myIndexName ```

Original source