Creating an Oracle Text User

2.2 Creating an Oracle Text User

Before you can create Oracle Text indexes and use Oracle Text PL/SQL packages, you need to create a user with the CTXAPP role. This role enables you to do the following:

Create and delete Oracle Text indexing preferences
Use the Oracle Text PL/SQL packages

To create an Oracle Text application developer user, perform the following steps as the system administrator user:

Create the user.
The following SQL statement creates a user called MYUSER with a password of password:
```
CREATE USER myuser IDENTIFIED BY password;
```
Grant roles to the user.
The following SQL statement grants the required roles of RESOURCE, CONNECT, and CTXAPP to MYUSER:
```
GRANT RESOURCE, CONNECT, CTXAPP TO MYUSER;
```
Grant EXECUTE privileges on the CTX PL/SQL package.
Oracle Text includes several packages that let you perform actions ranging from synchronizing an Oracle Text index to highlighting documents. For example, the CTX_DDL package includes the SYNC_INDEX procedure, which enables you to synchronize your index. The Oracle Text Reference describes these packages.

To call any of these procedures from a stored procedure, your application requires execute privileges on the packages. For example, to grant execute privileges to MYUSER on all Oracle Text packages, enter the following SQL statements:
```
GRANT EXECUTE ON CTXSYS.CTX_CLS TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_THES TO myuser;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO myuser;
```
Note:

These permissions are granted to the CTXAPP role. However, because role permissions do not always work in PL/SQL procedures, it is safest to explicitly grant these permissions to the user who already has the CTXAPP role.