Home » Server Options » Text & interMedia » ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql (11.2.0.2 on Linux)
ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql [message #597073] Mon, 30 September 2013 13:47 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
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:
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.
Here is the procedure:

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.


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

Thanks,

[Updated on: Mon, 30 September 2013 14:16]

Report message to a moderator

Re: ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql [message #597076 is a reply to message #597073] Mon, 30 September 2013 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.

privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

remove, delete, eliminate & otherwise get rid of any & all EXCEPTION handler code

[Updated on: Mon, 30 September 2013 14:23]

Report message to a moderator

Re: ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql [message #597079 is a reply to message #597076] Mon, 30 September 2013 14:57 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thanks BlackSwan. I unlocked the CTXSYS schema and connected to it and explictly granted execute on CTX_DDL to my user like this:


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


I have now granted the privileges as CTXSYS on CTX_DDL. What else do I need to do? Please suggest, I will be most thankful for the review.

Thank you,

[Updated on: Mon, 30 September 2013 14:59]

Report message to a moderator

Re: ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql [message #597097 is a reply to message #597079] Mon, 30 September 2013 18:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Try using authid current_user. Also, ctx_ddl.optimize_index is not a SQL statement, so you need to put it in a PL/SQL block using BEGIN and END. Please see the bold items below.

CREATE OR REPLACE PROCEDURE optimize_ora_txt_indexes_debug
AUTHID CURRENT_USER
IS
...
v_sql:=
'BEGIN
ctx_ddl.optimize_index (idx_name =>'||chr(39)|| c.index_name||chr(39)||', optlevel => '||chr(39)||'FULL'||chr(39)||');
END;
';
dbms_output.put_line(v_sql);
execute immediate v_sql;
...

Re: ORA-900 invalid sql statement error while calling optimize_index procedure from pl/sql [message #597228 is a reply to message #597097] Tue, 01 October 2013 14:22 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Barbara,

You are right, as allways! Yesterday someone else also pointed this out to me and my critical and burning issue was resolved.

Thanks a million!
Nirav_hyd
Previous Topic: Oracle text and locking issue
Next Topic: Oracle Text and Reserved Words
Goto Forum:
  


Current Time: Thu Mar 28 04:36:00 CDT 2024