How to fix "ORA-00950: invalid DROP option"?-CodePudding

ORA-00950: invalid DROP option

Above is the error that I am getting. I have written this procedure to remove all indexes from the emp_1 table. So can anyone please help me to find out the error and to fix this error?

CREATE OR REPLACE NONEDITIONABLE PROCEDURE sp_drop_indexes (     
  p_table_name  IN   VARCHAR2,
  p_errormsg    OUT  VARCHAR2 ) 
IS     
  v_sql VARCHAR2(1000);
  CURSOR get_indexes IS
   SELECT         index_name
   FROM           user_indexes
   WHERE          table_name = 'EMP_1';
BEGIN
   FOR rec IN get_indexes LOOP
     v_sql := 'DROP INDEX' || rec.index_name;
     EXECUTE IMMEDIATE v_sql;
   END LOOP;
EXCEPTION     
WHEN OTHERS THEN
   p_errormsg := sqlerrm; 
END sp_drop_indexes;

CodePudding user response：

You are missing a space after the DROP INDEX (but still inside the quotes) in your code:

v_sql := 'DROP INDEX ' || rec.index_name;

CodePudding user response：

Make sure you include a space after the DROP INDEX keyword:

v_sql := 'DROP INDEX ' || rec.index_name;

CodePudding user response：

You need a space after drop index and, if you can have case-sensitive index names, you need double quotes:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE sp_drop_indexes (     
  p_table_name  IN   VARCHAR2,
  p_errormsg    OUT  VARCHAR2 ) 
IS     
  v_sql VARCHAR2(1000);
  CURSOR get_indexes IS
   SELECT         index_name
   FROM           user_indexes
   WHERE          table_name = 'EMP_1';
BEGIN
   FOR rec IN get_indexes LOOP
     v_sql := 'DROP INDEX "' || rec.index_name || '"';
     EXECUTE IMMEDIATE v_sql;
   END LOOP;
EXCEPTION     
WHEN OTHERS THEN
   p_errormsg := sqlerrm;
END sp_drop_indexes;
/