Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

Maim

Programmer
Joined
Jun 25, 1999
Messages
106
Location
CA
What would be the oracle equivalent of the following?

Code:
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
Code:
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
Code:
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
 
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.):

Code:
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.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.
 
Another idea is to just drop it. The worse thing you may get is catchable error:

Code:
begin

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




Regards, Dima
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top