oracle drop index if exists

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

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

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! =)