Re: Create index only if index not exists in PL/SQL procedure

On 14 Apr 2004 04:34:40 -0700, chse30_at_hotmail.com (Christian Svensson) wrote:

>Greetings all,
>
>In my procedure I do:
>
>1, Drop index X
>2, Create table Y as select...
>3, Create index X for table Y
>
>Sometimes the job(2) fails and the index X will not be created. So I
>want some simple checking in my PL/SQL code when it drops the index at
>step 1.
>
>Of course I can do a select from user_indexes where index_name =
>'bla...' and then do an exists on this.
>
>But I think I have seen a much easier way to do this in just one line.
>But cant remember.
>
>Any help are much appreciated.
>
>Thanks.
>
>Christian

Hi Chistian,

Attempting to drop an non-existent index willl raise an exception (-1418). So all you got to do is catch that exception and (presumably) ignore it:

create or replace procedure Fooproc
is

   IndexDoesNotExist exception;
   pragma exception_init( IndexDoesNotExist,-1418);

begin

  begin
    execute immediate 'drop index fooidx';     exception
    when IndexDoesNotExist then
    dbms_output.put_line('Error : Index does not exists');   end;   

 --< Create table Y as select..>
 --<Create index X for table Y>      

end Fooproc;
/

• Kenneth Koenraadt