Home » Server Options » Text & interMedia » CTXSYS ERROR (Oracle 10G)
CTXSYS ERROR [message #494095] Fri, 11 February 2011 06:30 Go to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Hi,
I'm facing the following error.. in logs when i run a shell script
and i dont have access to CTXSYS schema.

BEGIN ctx_ddl.sync_index(IDX_NAME => 'TKT_INDEX', MEMORY=> '50M', PARALLEL_DEGREE => 4); END;
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10502: index TXT_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 544
ORA-06512: at line 1
What exactly this is, and how to fix.
Re: CTXSYS ERROR [message #494155 is a reply to message #494095] Fri, 11 February 2011 10:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
What you have posted is an attempted synchronization of an Oracle Text index that has failed, due to either the wrong index name being used or a lack of privileges so that it does not know that the index exists. However, what you posted does not match. The index name in the code that supposedly produced the error does not match the index name in the error message. In order to synchronize the index, you must provide the correct index name and run the code with the privileges of the owner of the index. When posting you should provide a copy and paste of an actual run from SQL*Plus, not a shell script. If you can get the code to run without error from SQL*Plus, but not from a shell script, then you know your problem is due to privileges that you lack when running the shell script that you have in SQL*Plus, and you need to fix that. I have provided a brief reproduction of the error and correction below.

-- test environment:
-- table:
SCOTT@orcl_11gR2> CREATE TABLE txt_table
  2    (txt_column  VARCHAR2 (30))
  3  /

Table created.

-- Oracle Text index:
SCOTT@orcl_11gR2> CREATE INDEX txt_index
  2  ON txt_table (txt_column)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.


-- reproduction of error
-- due to wrong index name:
SCOTT@orcl_11gR2> BEGIN
  2    ctx_ddl.sync_index
  3  	 (IDX_NAME	  => 'TKT_INDEX',
  4  	  MEMORY	  => '50M',
  5  	  PARALLEL_DEGREE => 4);
  6  END;
  7  /
BEGIN
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10502: index TKT_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 847
ORA-06512: at line 2


-- elimination of error
-- by using correct index name:
SCOTT@orcl_11gR2> BEGIN
  2    ctx_ddl.sync_index
  3  	 (IDX_NAME	  => 'TXT_INDEX',
  4  	  MEMORY	  => '50M',
  5  	  PARALLEL_DEGREE => 4);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>


Re: CTXSYS ERROR [message #494169 is a reply to message #494155] Fri, 11 February 2011 11:14 Go to previous messageGo to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Hi Barabra,
Thanks for you comments. I have modified the Index name while posting not to revel real objects Names, and made a typo in error log(extremely sry for this). Actually, Index name is same at both the places. I have checked using user_indexes & the Index name is correct. I think there wont be issue with Name, might be with privileges as you said, coz found that ctxsys.ddl pkg having AUTHID CURRENT_USER, so please suggest whether im correct or not.
Re: CTXSYS ERROR [message #494170 is a reply to message #494169] Fri, 11 February 2011 11:17 Go to previous messageGo to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Hi
one more thing i have missed to say that, i dont have access on CTXSYS objects, that why not able to post the sql output
Thanks
Yj
Re: CTXSYS ERROR [message #494201 is a reply to message #494170] Fri, 11 February 2011 14:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
In order to synchronize the index, you must be the owner of the index or accessing it through something that uses the owner's privileges and either have the ctxapp role granted to you or have execute on ctxsys.ctx_ddl granted to you. Roles do not apply to procedures, so if running it through a procedure, you need execute on ctxsys.ctx_ddl. The same may apply to some tools. You need to indicate what user owns the index, and what user is trying to synchronize the index. Also, as I said before, you need to test from SQL*Plus and post a copy and paste, changing the names if you like. You need to get a simple anonymous pl/sql block run from SQL*Plus in the owner's schema to work first, then only if that works should you progress to trying to run it from another schema via procedure or some tool or some script. Most of these other problems that I have mentioned will produce errors, but not the one that you are getting. The error that you are getting happens when there is no such index. This could be due to differences in case (upper, lower, mixed). When you search in ctx_user_indexes, if the index is not in all upper case, then you need to put it in whatever case it is with double quotes around it, because that is apparently how it was created.


Re: CTXSYS ERROR [message #494226 is a reply to message #494201] Fri, 11 February 2011 23:14 Go to previous message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Yes, coz of insufficient privileges, and i have granted proper role( user defined). Issue fixed Smile
Thank you very much sir...
Previous Topic: Oracle Query with AND clause
Next Topic: Contains Clause is not working on certain scenario (merged 3)
Goto Forum:
  


Current Time: Fri Mar 29 03:22:31 CDT 2024