Re: Checking for an index before dropping it

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Wed, 05 Apr 2000 17:16:08 GMT
Message-ID: <8cfsc6$90a$1@nnrp1.deja.com>

In article <8cfrdl$7r7$1_at_nnrp1.deja.com>, sumanp_at_my-deja.com wrote:
> 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?
>

create a script dropidx.sql:

set heading off
set feedback off
spool tmp
select 'drop index ' || index_name || ';' from user_indexes where index_name = upper('&1');
spool off
set heading on
set feedback on
@tmp.lst

and then you can:

ops$tkyte_at_dev8i> @dropidx no_such_index ops$tkyte_at_dev8i> @dropidx testing

drop index TESTING;

Index dropped.

ops$tkyte_at_dev8i>

Or, create a procedure to do it:

ops$tkyte_at_dev8i> set serveroutput on
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> set echo on
ops$tkyte_at_dev8i> create or replace procedure drop_index( p_index_name

in varchar2 )
2 is

  3      cursor_name     pls_integer default dbms_sql.open_cursor;
  4      ignore          pls_integer;
  5          no_such_index   exception;
  6          pragma exception_init( no_such_index, -1418 );
  7  BEGIN
  8      dbms_sql.parse(cursor_name, 'drop index ' || p_index_name,
dbms_sql.native);
  9      ignore := dbms_sql.execute(cursor_name);
 10      dbms_sql.close_cursor(cursor_name);
 11          dbms_output.put_line( 'Index ' || p_index_name || '

dropped.' );
12 exception

 13          when no_such_index then
 14          dbms_sql.close_cursor(cursor_name);

15 END;
16 /

Procedure created.

ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> exec drop_index( 'no_such_index' );

PL/SQL procedure successfully completed.

ops$tkyte_at_dev8i> exec drop_index( 'testing' ); Index testing dropped.

PL/SQL procedure successfully completed.

> Thanks.
>
> -------------
> Suman Mehta
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 05 2000 - 12:16:08 CDT