Newbie: how to script a test if index exists before dropping?

[Maim]

What would be the oracle equivalent of the following?

if exists (select * from dbo.sysindexes where name='myindex_idx' and id=Object_id('dbo.mytable')) 
  drop index dbo.mytable.myindex_idx
go

I've tried

if exists(select * from user_objects where object_type = 'INDEX' and object_name = 'mytable_myindex_idx')
  drop index mytable_myindex_idx
end if;

and the result is

SP2-0734: unknown command beginning "if exists(..." - rest of line ignored.
end if
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

from the SQLPlus worksheet.

What would I be doing wrong?

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook

 

[SantaMufasa]

Maim,

Following is a sample code set that
1) creates an a table, "MYTABLE".
2) creates an index, "MYINDEX_IDX", on "MYTABLE".
3) executes a PL/SQL block that tests for the existence of MYINDEX_IDX, then drops it if it exists. (The block also displays its findings and actions.):

create table mytable (x number);
create index myindex_idx on mytable (x);
set serveroutput on
declare
    cnt   number;
begin
    select count(*) into cnt
         from all_indexes
        where index_name = 'MYINDEX_IDX'
          and table_name = 'MYTABLE';
    dbms_output.put_line ('Number of indexes named "MYINDEX_IDX": '||cnt);
    if cnt = 1 then
        execute immediate 'drop index myindex_idx';
        dbms_output.put_line ('1 index dropped named "MYINDEX_IDX".');
    end if;
end;
/

Number of indexes named "MYINDEX_IDX": 1
1 index dropped named "MYINDEX_IDX".

Let us know if this sample provides the insight you need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

Another idea is to just drop it. The worse thing you may get is catchable error:

begin

  execute immediate 'drop index myindex_idx';
exception
  when others then null;
end;

Regards, Dima

http://seminihin.narod.ru

 

[Maim]

Thanks guys, I tried both. Both essentially do the same so I just used the exception as it's less scripting

-----------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rich Cook