CREATE OR REPLACE PROCEDURE optimize_ora_txt_indexes_debug
IS
   CURSOR cur_context_indexes
   IS
        SELECT index_name
          FROM user_indexes
         WHERE index_type = 'DOMAIN'
	   AND ROWNUM<2  
    ORDER BY INDEX_NAME;

   v_user         VARCHAR2 (30);
   v_pod          VARCHAR2 (30);
   v_start_time   TIMESTAMP;
   v_end_time     TIMESTAMP;
   v_elapsed      VARCHAR2 (40);
   v_msg	  VARCHAR2 (1000);
   v_error_code      NUMBER;
   v_error_msg   VARCHAR2 (1000);
   v_sql	VARCHAR2 (1000);

BEGIN
  


   FOR c IN cur_context_indexes
   LOOP
      BEGIN
        v_sql:= 'ctx_ddl.optimize_index (idx_name =>'||chr(39)|| c.index_name||chr(39)||', optlevel => '||chr(39)||'FULL'||chr(39)||')';
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
      EXCEPTION
         WHEN OTHERS
         THEN
            v_error_code := SQLCODE;
            v_error_msg := SQLERRM;
            v_msg :=
                  'Error while optimizing the index '
               || c.index_name
               || ' '
               || TO_CHAR (v_error_code)
               || ' '
               || v_error_msg;

            DBMS_OUTPUT.put_line (v_msg);


      END;
   END LOOP;



EXCEPTION
   WHEN OTHERS
   THEN
      v_error_code := SQLCODE;
      v_error_msg := SQLERRM;
      v_msg :=
            'Error while in the optimize index procedure'
         || ' '
         || TO_CHAR (v_error_code)
         || ' '
         || v_error_msg;

      DBMS_OUTPUT.put_line (v_msg);


END optimize_ora_txt_indexes_debug;
/
Procedure created. --compiles successfully.
--but it gives below error while I run it
SQL>exec optimize_ora_txt_indexes_debug; 
ctx_ddl.optimize_index (idx_name =>'ACCESS_CLNT_IDX04', optlevel => 'FULL')
Error while optimizing the index ACCESS_CLNT_IDX04 -900 ORA-00900: invalid SQL
statement

PL/SQL procedure successfully completed.
--however if the same command if I run from sqlplus it doesn't give any error!!
SQL>exec ctx_ddl.optimize_index (idx_name =>'ACCESS_CLNT_IDX04', optlevel => 'FULL');

PL/SQL procedure successfully completed.

SQL>alter user ctxsys account unlock;

sql>conn ctxsys/<pwd>@<mydb>
Connected.
SQL>grant execute on ctx_ddl to schema1;  (schema1 is my schema where I have these indexes).

Grant succeeded.
--Created the same procedure again
--with same code as posted earlier..
--Still getting same error!!!

SQL>exec optimize_ora_txt_indexes_debug
ctx_ddl.optimize_index (idx_name =>'ACCESS_CLNT_IDX04', optlevel => 'FULL')
Error while optimizing the index 'ACCESS_CLNT_IDX04'-900 ORA-00900: invalid SQL
statement