Error PLS-00201 occur when executing CTX_DDL Packages to create Index Preferences (Doc ID 102273.1)

Last updated on MARCH 21, 2012

Applies to:

Oracle Text - Version: 8.1.7.2 to 11.2.0.3 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 21-Mar-2012***

Symptoms

You attempt to create a text index preference or attribute and receive the following errors:

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


However, creating a text index using the default attributes/preferences and issuing a text query against that index is successful.

One way to reproduce these errors is to issue the following commands from SQL Plus:

SQL> connect "sys as sysdba@tnsalias"

Connected.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME                                                                                     
------------------------------                                                                      
SYSTEM                                                                                              
USERS                                                                                               
RBS                                                                                                 
TEMP                                                                                                
OEM_REPOSITORY                                                                                      
INDX                                                                                                

6 rows selected.

SQL> create user lab identified by lab
      default tablespace users
      temporary tablespace temp
      quota unlimited on users
      quota unlimited on temp;

User created.

SQL> grant create session, create table to lab;

Grant succeeded.

SQL> connect lab/lab@tnsalias

Connected.

SQL> create table test (id integer primary key, text varchar2(25));

Table created.

SQL> insert into test values (1, 'The cat sat on a mat.');

1 row created.

SQL> commit;

Commit complete.

SQL> create index test_indx on test(text) indextype is ctxsys.context;

Index created.

SQL> select * from ctx_user_index_errors;

no rows selected

SQL> select token_text from dr$test_indx$i;

TOKEN_TEXT                                                                                          
----------------------------------------------------------------                                    
cats                                                                                                
mats                                                                                                
CAT                                                                                                 
SAT                                                                                                 
MAT                                                                                                 

SQL> select * from test where contains (text, 'cat') > 0;

       ID TEXT                                                                                      
--------- -------------------------                                                                 
        1 The cat sat on a mat.                                                                     

SQL> execute ctx_ddl.create_preference ('MyUserFilter', 'USER_FILTER');
BEGIN ctx_ddl.create_preference ('MyUserFilter', 'USER_FILTER'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7: 
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared 
ORA-06550: line 1, column 7: 
PL/SQL: Statement ignored 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms