oracle drop index if exits

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

BEGIN
   DECLARE
      MY_COUNT   NUMBER DEFAULT 0;
   BEGIN
      SELECT COUNT (*)
        INTO MY_COUNT                                            -- index_name
        FROM user_indexes
       WHERE table_name = 'myTable' AND index_name = 'myIndexName';

      IF MY_COUNT > 0
      THEN
         BEGIN
            EXECUTE IMMEDIATE 'DROP INDEX myIndexName';
         END;
      END IF;
   END;
END;
/

Answer 2

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

Answer 3

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