Oracle Text in 23ai

Just a quick heads up for developers and DBAs that are using Text indexes when upgrading to 23ai.

In the docs for Text Indexes, we have always mentioned that you will need to do some grants on the appropriate CTXSYS packages in order to perform some of the metadata operations associated with Text indexes.

In versions of the database before 23ai, an alternative would to be perform such operations with a user that had the EXECUTE ANY PROCEDURE privilege.

For example, here’s a standard 19c database installation and I’ll attempt to create a Text preference using SYSTEM which does not have any explicit privileges on the CTX_DDL package, but does have EXECUTE ANY PROCEDURE.


SQL> conn system/admin@db19
Connected.

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0


SQL>
SQL> select privilege, grantee
  2  from dba_tab_privs
  3  where table_name = 'CTX_DDL';

PRIVILEGE                                GRANTEE
---------------------------------------- ------------------------------
EXECUTE                                  MDSYS
EXECUTE                                  XDB
EXECUTE                                  CTXAPP

SQL>
SQL> select *
  2  from session_roles
  3  order by 1;

ROLE
----------------------------------------
ACCHK_READ
CAPTURE_ADMIN
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
EM_EXPRESS_ALL
EM_EXPRESS_BASIC
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVA_ADMIN
OLAP_DBA
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER

22 rows selected.

SQL>
SQL> select *
  2  from session_privs
  3  where privilege like 'EXEC%';

PRIVILEGE
----------------------------------------
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXECUTE ANY LIBRARY
EXECUTE ANY OPERATOR
EXECUTE ANY INDEXTYPE
EXECUTE ANY EVALUATION CONTEXT
EXECUTE ANY RULE SET
EXECUTE ANY RULE
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY

12 rows selected.

SQL>
SQL> begin
  2   ctxsys.ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  3   ctxsys.ctx_ddl.set_attribute('my_lexer', 'INDEX_TEXT', 'YES');
  4   ctxsys.ctx_ddl.set_attribute('my_lexer', 'INDEX_THEMES', 'NO');
  5   ctxsys.ctx_ddl.drop_preference('my_lexer');
  6  end;
  7  /

PL/SQL procedure successfully completed.

As you can see, it runs fine because SYSTEM has the EXECUTE ANY PROCEDURE privilege.

In 23ai, there has been a subtle change, and the privileges as per the docs above, now need to be explicitly granted. For example, here is the same demo in 23ai, once again with SYSTEM


SQL> conn system/admin@db235
Connected.

SQL> select banner_full from v$version;

BANNER_FULL
-----------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07


SQL>
SQL> select privilege, grantee
  2  from dba_tab_privs
  3  where table_name = 'CTX_DDL';

PRIVILEGE                                GRANTEE
---------------------------------------- ------------------------------
EXECUTE                                  XDB
EXECUTE                                  CTXAPP
EXECUTE                                  MDSYS

SQL>
SQL> select *
  2  from session_roles
  3  order by 1;

ROLE
----------------------------------------
ACCHK_READ
AQ_ADMINISTRATOR_ROLE
CAPTURE_ADMIN
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
DBA
DGPDB_ROLE
EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_SELECT_ROLE
IMP_FULL_DATABASE
JAVA_ADMIN
OLAP_DBA
OLAP_XS_ADMIN
OPTIMIZER_PROCESSING_RATE
PPLB_ROLE
SCHEDULER_ADMIN
SELECT_CATALOG_ROLE
WM_ADMIN_ROLE
XDBADMIN
XDB_SET_INVOKER

23 rows selected.

SQL>
SQL> select *
  2  from session_privs
  3  where privilege like 'EXEC%';

PRIVILEGE
----------------------------------------
EXECUTE ANY PROCEDURE
EXECUTE ANY TYPE
EXECUTE ANY LIBRARY
EXECUTE ANY OPERATOR
EXECUTE ANY INDEXTYPE
EXECUTE ANY EVALUATION CONTEXT
EXECUTE ANY RULE SET
EXECUTE ANY RULE
EXECUTE ANY PROGRAM
EXECUTE ANY CLASS
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
EXECUTE DYNAMIC MLE
EXECUTE ANY DOMAIN

14 rows selected.

SQL>
SQL> begin
  2   ctxsys.ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
  3   ctxsys.ctx_ddl.set_attribute('my_lexer', 'INDEX_TEXT', 'YES');
  4   ctxsys.ctx_ddl.set_attribute('my_lexer', 'INDEX_THEMES', 'NO');
  5   ctxsys.ctx_ddl.drop_preference('my_lexer');
  6  end;
  7  /
 ctxsys.ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER');
 *
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
ORA-06550: line 3, column 2:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 3, column 2:
PL/SQL: Statement ignored
ORA-06550: line 4, column 2:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 4, column 2:
PL/SQL: Statement ignored
ORA-06550: line 5, column 2:
PLS-00201: identifier 'CTXSYS.CTX_DDL' must be declared
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored

I’m still digging around internally to understand the rationale here, but in the interim, please add the associated grants in 23ai if you need them.

4 responses to “Oracle Text in 23ai”

Rajeshwaran Jeyabal

September 18, 2024 at 4:13 pm

Perhaps a fix available in 23.6

demo@ATP23ai> begin
2 ctxsys.ctx_ddl.create_preference(‘my_lexer’, ‘BASIC_LEXER’);
3 ctxsys.ctx_ddl.set_attribute(‘my_lexer’, ‘INDEX_TEXT’, ‘YES’);
4 ctxsys.ctx_ddl.set_attribute(‘my_lexer’, ‘INDEX_THEMES’, ‘NO’);
5 ctxsys.ctx_ddl.drop_preference(‘my_lexer’);
6 end;
7 /

PL/SQL procedure successfully completed.

demo@ATP23ai> select banner_full from v$version; BANNER_FULL

Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 – Production
Version 23.6.0.24.07

1. Connor McDonald
  
  September 18, 2024 at 5:20 pm
  
  ATP is its own special case
  
Raghavendra

January 23, 2025 at 3:27 pm

Hi Conner we have oracle text indexes which makes use of auto_lexer preference of oracle text when we try to create these indexes in 23ai free we are getting an error “missing language support”. Any help is highly appreciated.

1. Connor McDonald
  
  January 23, 2025 at 5:37 pm
  
  Yeah, that doesn’t look right. I’ll pass it on to the right people