Re: Checking for an index before dropping it.

Sybrand Bakker skrev i meddelandet
<954957965.662.1.pluto.d4ee154e_at_news.demon.nl>...
>create or replace chkdropidx(owner_name in varchar2, index_name in
varchar2)
>is
>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.
Another way of doing these things are to write a SQL that generates an SQL-file and then run that file.
Something like this...forgive me, but I can not parse this since I have no SQL on this computer.

SPOOL DROP_INDEX.SQL
SET FEEDBACK OFF;
SET PAGESIZE 0;
SET HEADING OFF;
SELECT 'DROP INDEX '||INDEX_NAME||';'
FROM
USER_INDEXES;
SPOOL OFF; You might have to edit the produced file (DROP_INDEX.SQL) a litte bit. But the above gives you a lot for free. If you are the DBA you can use the table Sybrand used. The one above gives you only the indexes logged in user have defined.

Lars Received on Wed Apr 05 2000 - 23:22:33 CEST