2 Replies Latest reply: Sep 30, 2013 3:15 PM by Herald ten Dam RSS

    ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql

    orausern


      Hi Experts,

      I am on Oracle 11.2.0.3 on Linux and have Oracle Text installed in my database. I want to set up dbms_job schedule to optimize my oracle text indexes. So first I created a pl/sql procedure to optimize the indexes. But it is giving me ORA-900 error, but the same sql if I run from sqlplus it works fine! Can you please help me find what the issue is:

       

      Here is the procedure:

      (By the way CTXAPP role has been granted in the schema where these Oracle Text indexes are created and in which the below procedure to optimize the index is running.)

       

      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;
      /
      
       --the procedure compiles successfully. 
       Now when I run it , I get the 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
      
      
       --When I run the same command from sqlplus as execute statement , it works fine:
      SQL>exec ctx_ddl.optimize_index (idx_name =>'ACCESS_CLNT_IDX04', optlevel => 'FULL');
      PL/SQL procedure successfully completed.
       
       
      
      
      

      So it works from sqlplus but fails from plsql.. I will be very thankful for any pointers to fix the issue.

      Thansk,

      OrauserN