I need to delete an index ONLY if it exists and NOT return 'not found 2

[Gumboman]

I have a need to determine if an index exists, delete it if it does, and then create a new index with the same name.&nbsp;&nbsp;Due to the install script being used at our site, I cannot allow an 'index not found' to be returned (that will kill my install).&nbsp;&nbsp;existing code is in the following form:<br><br>&nbsp;&nbsp;drop index XXXXX;<br>&nbsp;&nbsp;&nbsp;create bitmap index XXXXX ~~~~~~;<br><br>I need to &quot;wrapper&quot; the drop index statement to process only if the index exists.<br><br>Any help is greatly appreciated.

 

[MikeLacey]

Hi,<br><br>Can you put DDL (create etc) statements in a PL/SQL Block?<br><br>If you can you could write some PL/SQL to look up the index in the data dictionary (can't remember the table name and I'm not somewhere I can look at the moment, but I will if you need me to) and only create it if it doesn't already exist.<br><br>Mike<br> <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[Gumboman]

Mike,<br><br>Thanks to you, sir.&nbsp;&nbsp;Mislabeled as a 'programmer' - and I still have very little Oracle knowledge.&nbsp;&nbsp;Any rough outline is appreciated.&nbsp;&nbsp;I can work with whatever you can provide as a starting shell for design.<br><br>Terry

 

[carp]

You can't use DDL directly from PL/SQL.&nbsp;&nbsp;However, you can use the DBMS_SQL package to do this.&nbsp;&nbsp;Long ago, I got tired of trying to remember exactly how this works, so I wrote the following procedure (which only works for real simple commands!):<br><br>PROCEDURE dynamic_statement(in_command VARCHAR2)<br>AS<br>v_cursor_handle INTEGER;<br>v_feedback INTEGER;<br><br>BEGIN<br><br>v_cursor_handle := DBMS_SQL.OPEN_CURSOR;<br>DBMS_SQL.PARSE(v_cursor_handle, in_command,DBMS_SQL.NATIVE);<br>v_feedback := DBMS_SQL.EXECUTE(v_cursor_handle);<br><br>END;<br><br>Now if I want to create an index from within a PL/SQL block, all I need to do is:<br><br>BEGIN<br>.<br>.<br>.<br>DYNAMIC_STATEMENT('CREATE INDEX abc on my_table(col1)');<br>.<br>.<br>.<br>END;<br>

 

[MikeLacey]

Mr Carp - you're a star. I'm going to nick (english slang for &quot;steal&quot that bit of code and use it all over the place.<br><br>Mike<br> <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[Gumboman]

CARP,<br><br>You are a gem.&nbsp;&nbsp;Thank you sir (or Madam).&nbsp;&nbsp;Your assistance is greatly appreciated and, like Mike, I plan to use this puppy to death.<br><br>Terry

 

[carp]

Mr. Lacey -<br>How on earth can you nick that which is freely given?<br>Glad to help.