oracle drop index if exists

Question

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

Answer 1

Don't check for existence. Try to drop, and capture the exception if necessary...

DECLARE
   index_not_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT (index_not_exists, -1418);
BEGIN
   EXECUTE IMMEDIATE 'drop index foo';
EXCEPTION
   WHEN index_not_exists
   THEN
      NULL;
END;
/

Answer 2

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

Answer 3

In Oracle, you can't mix both DDL and DML. In order to do so, you need to work it around with the EXECUTE IMMEDIATE statement.

So, first check for the existence of the index.

Second, drop the index through the EXECUTE IMMEDIATE statement.

DECLARE v_Exists NUMBER;

BEGIN
    v_Exists := 0;

    SELECT 1 INTO v_Exists
        FROM USER_INDEXES
        WHERE TABLE_NAME LIKE 'myTable'
            AND INDEX_NAME LIKE 'myIndexName'

    IF v_Exists = 1 THEN
        EXECUTE IMMEDIATE "DROP INDEX myIndexName"
    ENDIF;

    EXCEPTION
        WHEN OTHERS THEN
            NULL;
END;

This code is out the top of my head and you may need to fix it up a little, but this gives an idea.

Hope this helps! =)