One case of DDL executed in PL/SQL

   
Please help me in resolving the error below

Code:
=====
DECLARE --count NUMBER;
tname NVARCHAR2(255);
indexname NVARCHAR2(255);
tablename NVARCHAR2(255);

--tname:= 'TABLE_PART_ACTION';


CURSOR indexCursor IS
SELECT index_name,table_name
FROM user_indexes where table_name='TABLE_PART_ACTION';

BEGIN

OPEN indexCursor;
LOOP
FETCH indexCursor INTO indexname, table_name;
EXIT WHEN indexCursor%NOTFOUND;


--select count INTO count from user_indexes where table_name=tname;

IF (indexname!='SYS_C0014096') then
dbms_output.put_line('Testing......');
DROP index indexname;
END IF;
END LOOP;
CLOSE indexCursor;
END;
/

Error:
=====
ORA-06550: line 25, column 6:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

begin case declare else elsif end exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe

===============

Solution

dineshmurs,

there's a couple of problems with your pl/sql block:

1.) As shammat already stated, you have to use "EXECUTE IMMEDIATE" for DDL statements in PL/SQL

2.) As it-iss stated, SYS_ named indexes should be avoided (they are not guaranteed to have the same name after i.e. exporting/importing a table)

3.) The "IF indexname != <whatever>" should be moved into the query for performance reasons

4.) In the query you should exclude LOB-indexes to avoid ORA-22864 errors

5.) Looping over cursors is one of the most efficient performance killers in PL/SQL.
The reason is, that every loop cycle generates 2 context switches (PLSQL->SQL and back).
Usually you will gain factors in performance by looping over a PL/SQL table instead.

6.) You should code this as stored procedure to avoid n SQL*Net roundtrips when deleting n indexes


My take on that (ignoring my own advise #6) would be:

Code:

-- tested on 11.2.0.1.0
DECLARE
  type tIndArr is table of user_indexes&#37;rowtype index by pls_integer;

  cursor indexCursor IS
       SELECT  *
       FROM    user_indexes
       WHERE   table_name    = 'TEST'
       AND     index_name   != 'PK_TEST'  -- index implementing the primary key, like your SYS_C0014096
       AND     index_type   != 'LOB';     -- avoid ORA-22864 on LOB indexes

       vIndArr  tIndArr;
       vCmdStr  varchar2(4000);

BEGIN
   OPEN  indexCursor;
   FETCH indexCursor bulk collect into vIndArr;
   CLOSE indexCursor;
   if vIndArr.COUNT > 0 then
      for i in vIndArr.FIRST..vIndArr.LAST loop
         vCmdStr := 'DROP INDEX ' || vIndArr(i).index_name;
         dbms_output.put_line('About to execute: ' || vCmdStr);
         execute immediate vCmdStr;
      END LOOP;
   end if;
END;

FROM

http://www.dbforums.com/oracle/1655591-getting-problem-while-executing-procedure.html