Enabling Indexing of Online Help in Oracle Database 11gR2 and Higher

The ability to search Oracle Application Express online Help is accomplished through Oracle Text and a URL datastore. There is a change in the default behavior and permissions to use an Oracle Text URL datastore in database 11gR2 and higher.

If users attempt to search Oracle Application Express online Help in Oracle database 11gR2 and encounter the following error, then the permission to use an Oracle Text URL datastore has not been granted to database user APEX_040000.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine 
ORA-20000: Oracle Text error: 
DRG-10758: index owner does not have the privilege to use file or URL datastore

To enable the indexing of online Help in Oracle Application Express, the permission to use an Oracle Text URL datastore must be granted to the APEX_040000 database user. This is accomplished by assigning this specific privilege to a database role and then granting this role to the APEX_040000 database user.

To determine if the ability to use an Oracle Text URL datastore is already granted to a database role:

Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

On Windows:

SYSTEM_DRIVE:\ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

On UNIX and Linux:

$ sqlplus /nolog
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password

Run the following command:
```
SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
```
This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
If no value is returned by step 2, then create a new database role as shown in the following example:
```
CREATE ROLE APEX_URL_DATASTORE_ROLE;
```
Grant this role to the database user APEX_040000 with the following statement:
```
GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
```
If step 2 returned a value, use this database role name instead of the example APEX_URL_DATASTORE_ROLE.
Lastly, if step 2 did not return a value, then use the Oracle Text API to grant permission to the newly created database role with the following statement:
```
EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
```