Re: Checking for an index before dropping it.

[Quoted] create or replace chkdropidx(owner_name in varchar2, index_name in varchar2) is
[Quoted] cursor checkidx(p_owner in varchar2, p_index_name in varchar2) is select 'x'
from dba_indexes
where owner = p_owner and index_name = p_index_name; dummy char(1);
cur_handle integer;
begin
open checkidx(owner_name, index_name);
fetch checkidx into dummy;
if checkidx%FOUND then

   cur_handle := dbms_sql.open_cursor;
   dbms_sql.parse(cur_handle,'drop index '||owner||'.'||index_name, dbms_sql.native);

   dbms_sql.close_cursor(cur_handle);
end if;
close checkidx;
end;

Since you don't specify version, I wrote this code assuming V7.

Hth,

Sybrand Bakker, Oracle DBA

<sumanp_at_my-deja.com> wrote in message news:8cftsh$ao6$1_at_nnrp1.deja.com...
> I would like to check for an index before dropping it.
> If the index doesn't exist and I try to drop it,
> Oracle gives me an error message.
> I would like to suppress that error message,
> by checking beforehand, if the index on the table
> exists.
>
> How can I accomplish this, using
> SQL *Plus or TOAD?
>
> Thanks.
>
> -------------
> Suman Mehta
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Apr 05 2000 - 20:05:11 CEST