How to check if index exists before dropping it 4

[sjh]

Hi,
Is there a way to check if an index exists before dropping it? I would like to avoide getting the following error.

drop index myIndex

ORA-01418: specified index does not exist

Please help!

Thank you!

 

[SantaMufasa]

SJH,

Yes, here is a procedure that, I believe, does what you want. The procedure, "DROP_OBJ", lets you drop any object for which you have permission to drop, but it also suppresses any error message if the object does not exist (as you requested). First is a test drive, then second is the procedure definition:

Section 1 -- Sample invocation of procedure:

SQL> create table sjh (x number);
SQL> create index sjh_idx on sjh(x);
SQL> select index_name from dba_indexes where table_name = 'SJH';

INDEX_NAME
------------------------------
SJH_IDX
SQL> exec drop_obj('index','sjh_idx')
SQL> set feedback on
SQL> select index_name from dba_indexes where table_name = 'SJH';

no rows selected

SQL> set feedback off
SQL> exec drop_obj('index','sjh_idx')
SQL>

Notice that no error message resulted upon second attempt to drop the index.

Section 2 -- Procedure, "DROP_OBJ", definition:

******************************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM ******************************************************************************
create or replace procedure drop_obj (obj_type varchar2, obj_name varchar2) is
	SQL_stm		varchar2(100);
	bad_SQL		exception;
		pragma	exception_init(bad_SQL,-933);
	-- applies to attemting to drop 'TABLE PARTITION','INDEX PARTITION',
	no_table_view	exception;
		pragma	exception_init(no_table_view,-942);
	no_cluster	exception;
		pragma	exception_init(no_cluster,-943);
	bad_drop	exception;
		pragma	exception_init(bad_drop,-950);
	-- applies to attempting to drop 'CONSUMER GROUP','EVALUATION CONTEXT',
	--	'LOB','QUEUE','RESOURCE PLAN'
	no_index	exception;
		pragma	exception_init(no_index,-1418);
	no_synonym	exception;
		pragma	exception_init(no_synonym,-1434);
	no_dblink	exception;
		pragma	exception_init(no_dblink,-2024);
	no_sequence	exception;
		pragma	exception_init(no_sequence,-2289);
	no_object	exception;
		pragma	exception_init(no_object,-4043);
	-- applies to attempting to drop 'FUNCTION','LIBRARY','PACKAGE BODY',
	--	'PACKAGE','PROCEDURE','TYPE BODY','TYPE'
	no_trigger	exception;
		pragma	exception_init(no_trigger,-4080);
	no_operator	exception;
		pragma	exception_init(no_operator,-29807);
begin
	SQL_stm	:= 'drop '||obj_type||' '||obj_name;
	execute immediate SQL_Stm;
exception
	when no_table_view or no_cluster or no_index or no_synonym or no_dblink
		or no_sequence or no_object or no_trigger or no_operator then
		null;
	when bad_sql or bad_drop then
		raise;
	when others then
		raise;
end;
/

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:09 (25Jul04) UTC (aka "GMT" and "Zulu"), 19:09 (24Jul04) Mountain Time)

 

[BJCooperIT]

Not only a good script, but a wonderful example of error handling. Star on me.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Systems Project Analyst/Custom Forms & PL/SQL - Oracle/Windows

 

[sjh]

Thanks for your procedure Mufasa.

Is there a way to check the existing index without using a procedure? Maybe using EXISTS keyword?

Thanks!

 

[SantaMufasa]

SJH,

That is a wonderful idea. I have long wished for Data-Definition Language (DDL) with conditional expressions. Unfortunately, Oracle's SQL committee hasn't agreed yet. So, there currently is no "DROP INDEX <index_name> WHERE EXISTS...". That's why I had to build a procedure to do what you wanted.

If you don't want to use the "exec..." construct, we could certainly modify the procedure to become a user-defined function which you could call from a SELECT statement, DROPs the object, and returns a found/not found result code.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:53 (26Jul04) UTC (aka "GMT" and "Zulu"), 11:53 (26Jul04) Mountain Time)

 

[sjh]

Thank you Mufasa for your reply! I didn't know if conditional expressions were possible in Oracle. Thank you again for clarifying.

Susie