Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Checking for an index before dropping it
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:
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_namein varchar2 )
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.' );
13 when no_such_index then 14 dbms_sql.close_cursor(cursor_name);15 END;
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.comOracle Service Industries
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Apr 05 2000 - 12:16:08 CDT