11 Migrating Oracle Text Applications

11.1.1.1 Migrating to the AUTO_FILTER Filter Type

Most applications will not have to be modified to reflect the change to the AUTO_FILTER type. The following migration steps will be performed automatically when you upgrade to this release:

• If an existing index uses the INSO_FILTER filter type, then it will be migrated to the AUTO_FILTER filter type.

• If an existing index uses the MAIL_FILTER filter type, then the INSO_TIMEOUT and INSO_OUTPUT_FORMATTING attributes will be migrated to the AUTO_FILTER_TIMEOUT nd AUTO_FILTER_OUTPUT_FORMATTING attributes, respectively.

• If the default values of the system parameters DEFAULT_FILTER_FILE and DEFAULT_FILTER_BINARY have not already been modified, then they will be migrated from CTXSYS.INSO_FILTER to CTXSYS.AUTO_FILTER.

Although no actions are required to migrate to the new filter system, Oracle Text users upgrading their databases to this release should take the following steps to migrate away from using APIs deprecated by this release:

• If an index uses the MAIL_FILTER filter type with a nondefault mail configuration file, edit the file, replacing all occurrences of the INSOFILTER directive with the AUTO_FILTER directive.

• Replace the use of the PL/SQL constant TRACE_IDX_INSO_FILTER in your application with the TRACE_IDX_AUTO_FILTER constant.

• Replace the use of the system-defined preference CTXSYS.INSO_FILTER in your applications with CTXSYS.AUTO_FILTER.

11.2.1.1 CTXSYS No Longer Has DBA Permissions

CTXSYS no longer has DBA permissions. This may affect indexes using USER_DATASTORE, PROCEDURE_FILTER, or USER_LEXER objects. For example, suppose that you have an index using a USER_DATASTORE whose procedure is CTXSYS.PROC, and that procedure refers to other schemas' objects:

create procedure proc(r in rowid, d in out nocopy clob)
is
begin
  select text into l_data from scott.example ...

Previously, this user datastore would have worked properly because CTXSYS was able to select from any table—namely, SCOTT.EXAMPLE. However, in Oracle Database 10g, CTXSYS does not have DBA privileges and is not allowed to select from SCOTT.EXAMPLE. This makes the procedure PROC invalid, which leads to errors when indexing or sync is done for this index.

To resolve this problem, Oracle recommends migrating all user datastores, procedure filters, and user lexers from CTXSYS-owned procedures to index-owner-owned procedures (see "Migrating CTXSYS-Owned Procedures").

11.2.1.2 Migrating CTXSYS-Owned Procedures

Here are the steps to migrate an index using a CTXSYS-owned procedure to use an index-owner-owned procedure:

1. Create a procedure owned by the index owner that is equivalent to the CTXSYS-owned procedure. If your application's CTXSYS-owned procedure simply calls another procedure owned by the index owner, use that procedure for step 2. Otherwise, copy the code from the CTXSYS-owned procedure into a new procedure owned by the index owner, making any needed changes for the change in schema.

2. Create a new user datastore, procedure filter, or user lexer preference that uses the index-owner-owned procedure. Alternatively, you can modify the existing preference using CTX_DDL.SET_ATTRIBUTE, if the preference used to create the index still exists.

3. Replace the existing datastore or filter or lexer with the new, updated preference using the new REPLACE METADATA command. For instance, to replace a user datastore:

   alter index <myindex> rebuild parameters ('replace metadata datastore <new_datastore_preference>');
   
   

   REPLACE METADATA does not rebuild the index, so this command will not affect existing index data.

11.2.1.3 Effective User During Indexing

In previous releases of Oracle Text, the effective user during indexing or sync was CTXSYS. As a result, CTXSYS required execute permission on all BFILE directories, execute permission on any procedures called from user datastores, procedure filters, or user lexers, and the CTXSYS user's TEMP tablespace was used during indexing. In Oracle Database 10g, the effective user during indexing is the index owner, which eliminates these caveats.

11.2.1.4 Procedures Do Not Need to Be Owned by CTXSYS

Previously, procedures used in user datastores, procedure filters, and user lexers had to be owned by CTXSYS. In Oracle Database 10g, these procedures can be owned by any schema, so long as the index owner has execute privileges on them.

This principally affects creation of preferences. In previous releases of Oracle Text, a user datastore created with:

beginctx_ddl.create_preference('example','user_datastore');ctx_ddl.set_attribute('example','procedure','proc');end;

would have used the procedure CTXSYS.PROC. However, in Oracle Database 10g, standard Oracle Database rules are applied to the input "PROC," and this resolves to USER.PROC. Any application code that creates user datastores, procedure filters, or user lexers should either create the preferences as the owner of the procedure, or prepend the correct owner name to the procedure name. For example:

ctx_ddl.set_attribute('example','procedure','user.proc');

11.2.1.5 Synching and Optimizing of Other Users' Indexes

In previous releasess of Oracle Text, only the owner of the index and CTXSYS were allowed to sync or optimize an index through CTX_DDL.SYNC_INDEX and CTX_DDL.OPTIMIZE_INDEX. In Oracle Database 10g, any user with the ALTER ANY INDEX system privilege is also allowed to sync or optimize any index.

11.2.1.6 CTX Packages and Invoker's Rights

Most public CTX packages, such as CTX_DDL, CTX_QUERY, and CTX_REPORT, are now invoker's rights packages.

11.2.1.7 CREATE TABLE Permissions

In Oracle Database 10g, if a text index is created by one user for another user, or if the create index statement is issued from a PL/SQL block, the index owner must be granted the CREATE TABLE privilege in order for the indexing to succeed. Even if the index owner has the RESOURCE role, CREATE TABLE must be specifically granted.