Setting-up Full Text Search on the Escenic Schema

A set of scripts provided by Escenic are used to setup full text search on the Escenic Schema. These scripts are required to enable text search via Escenic Content Studio (ECS)

Common problems faced while setting up and executing the Escenic provided scripts are described below.

Problem 1
Executing the following script to setup required indexing jobs

alter session set nls_language=american;
VARIABLE jobno number;
exec DBMS_JOB.SUBMIT(:jobno,'STO_ECE_SI1.ctx_ddl.sync_index(''STO_ECE_SI1.XML_INDEX'');',SYSDATE, 'SYSDATE + 1/1440');
exec DBMS_JOB.SUBMIT(:jobno,'STO_ECE_SI1.ctx_ddl.sync_index(''STO_ECE_SI1.XMLENTITY_CONTENT'');',SYSDATE, 'SYSDATE + 1/1440');
exec DBMS_JOB.SUBMIT(:jobno,'CTX_DDL.OPTIMIZE_INDEX(''STO_ECE_SI1.XML_INDEX'',CTX_DDL.OPTLEVEL_FAST);',SYSDATE, 'trunc(sysdate+1)+2/24');
exec DBMS_JOB.SUBMIT(:jobno,'CTX_DDL.OPTIMIZE_INDEX(''STO_ECE_SI1.XMLENTITY_CONTENT'',CTX_DDL.OPTLEVEL_FAST);',SYSDATE, 'trunc(sysdate+1)+2/24');

Results in the following error

Error starting at line 3 in command:
exec DBMS_JOB.SUBMIT(:jobno,'STO_ECE_SI1.ctx_ddl.sync_index(''STO_ECE_SI1.XML_INDEX'');',SYSDATE, 'SYSDATE + 1/1440');
Error report:
ORA-06550: line 1, column 93:
PLS-00201: identifier 'STO_ECE_SI1.CTX_DDL' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 1
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Explanation

The scripts(provided by Escenic) specify [Escenic_Schema_User].ctx_ddl which does not exists.

Resolution

Simply remove the [Escenic_Schema_User] prefix and run the scripts to setup indexing jobs. Verification that the jobs are setup correctly can be done by executing the following scripts:

• select job, what from user_jobs;

Sample Output
Job What
13 CTX_DDL.OPTIMIZE_INDEX('XML_INDEX',CTX_DDL.OPTLEVEL_FAST);
14 CTX_DDL.OPTIMIZE_INDEX('XMLENTITY_CONTENT', CTX_DDL.OPTLEVEL_FAST);
21 ctxsys.ctx_ddl.sync_index('XMLENTITY_CONTENT');
22 ctxsys.ctx_ddl.sync_index('XML_INDEX');

• select job, last_sec, last_date, next_sec, next_date,failures, broken from user_jobs;

Sample Output
Job LAST_SEC LAST_DATE NEXT_SEC NEXT_DATE FAILURES BROKEN
13 02:00:00 23-JUN-09 02:00:00 24-JUN-09 0 N
14 02:00:01 23-JUN-09 02:00:00 24-JUN-09 0 N
21 10:03:04 23-JUN-09 13:03:00 23-JUN-09 0 N
22 10:00:04 23-JUN-09 13:00:00 23-JUN-09 0 N

Problem 2
Executing the following script

alter session set nls_language=american;
BEGIN
ctx_ddl.create_section_group('sto_ece_si1.IO_ARTICLE','AUTO_SECTION_GROUP');
END;
/

Results in the following error

alter session set succeeded.

Error starting at line 2 in command:

BEGIN
ctx_ddl.create_section_group('sto_ece_si1.IO_ARTICLE','AUTO_SECTION_GROUP');
END;
Error report:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Explanation

The CTXSYS user needs to grant the CTXAPP role to the user running the script. A DBA can do this and this is normally done while installaing the DB.

Resolution

This was reported to the DBA and after he resolved the permission issues, the script ran successfully.