ArcGIS Help 10.1 - Configuring an Oracle database to support ArcSDE XML columns

ArcSDE XML columns use the Oracle Text components, which are installed with Oracle by default. To use them, you must grant privileges to users for these components. Once this is done, the database and ArcSDE must be properly configured and maintained to optimize search results and performance, particularly for searching XML columns written in languages other than English if you are using XML documents that store languages other than English.

If you plan to query a table using the contents of an XML column, check the following information for the database:

The default lexer used for linguistic analysis with text indexes
The default word list settings
The system-provided text objects
The default stop list for the database

If the current default settings are not correct for the language of your XML documents and existing custom text preferences are not available, you must create custom text preferences to correctly index and search your XML documents and reference those preferences using the ArcSDE DBTUNE parameter XML_IDX_INDEX_TEXT before creating your XML column.

You can also customize the settings that are used to index your XML documents. This requires creating the appropriate preferences in Oracle and referencing those parameters with the ArcSDE DBTUNE parameter XML_IDX_INDEX_TEXT. Then, use the DBTUNE keyword containing the custom XML_IDX_INDEX_TEXT parameter when creating an XML column.

Caution:

Use care when changing how punctuation is indexed. The same preference is used when indexing all text in your XML documents. Values in one XML element may use punctuation differently than in others.

Steps:

Log in to SQL*Plus as the ctxsys user to grant privileges to the user who will own the XML column by issuing the following command:
```
GRANT EXECUTE ON ctx_ddl TO <mdUsername>;
```
<mdUsername> is the user who will own the XML column.
Query the ctx_parameters view to determine what the default settings currently are for your Oracle installation.
```
SELECT * FROM ctx_parameters;
```
Query the CTX_PREFERENCES view to learn about the available preferences. This is how you can learn which lexer is being used by the CTXSYS.DEFAULT_LEXER preference, for example. Any custom preferences created by other users are also described.
```
SELECT * FROM ctx_preferences;
```
Query the ctx_preferences_values view to ensure that the system-defined preferences are correctly set for the language of your XML documents.
```
SELECT * FROM ctx_preference_values;
```
Compare the results from the above queries to the allowed settings for lexers, word lists, and other text objects documented in the Oracle Text Reference Guide for your version of the database.
If the current default settings are not correct for the language of your XML documents and existing custom text preferences are not available, create custom text preferences to correctly index and search your XML documents.
Add the custom text preferences to the DBTUNE parameter XML_IDX_INDEX_TEXT before creating your XML column.
Review the system-provided text objects before creating custom preferences to be sure that the lexer you want to use is available with your Oracle installation.
```
SELECT * FROM ctx_objects;
```
You can create a custom text preference referencing a lexer that was returned from the previous step.
For example, if WORLD_LEXER was returned in the ctx_objects list, you can create a custom text preference referencing this lexer as follows:
```
EXEC ctx_ddl.create_preference('WORLDLEXER', 'WORLD_LEXER');
```
Set the XML_IDX_INDEX_TEXT DBTUNE parameter to use the preference. Alter the value of the XML_IDX_INDEX_TEXT parameter under the DEFAULTS keyword using the sdedbtune command. See Altering the contents of the DBTUNE table for details.

Always preface the preference name with the user who created the preference. For example, if the geodatabase administrator logged in as the user sde when the preference was created, the XML_IDX_INDEX_TEXT parameter should set the preference as follows:
```
LEXER sde.WORLDLEXER
```
Make sure the word list and stop list preferences are set correctly for your language. If they are not, do one of the following:
- Use one of the values for these settings for your language as defined in the Oracle Text Reference Guide.
- If the setting you need is not defined in the Oracle Text Reference Guide, create a custom preference and add it to the value of the XML_IDX_INDEX_TEXT parameter in the DBTUNE table.

If the current default settings are not correct for the language of your XML documents and existing custom text preferences are not available, you must create custom text preferences to correctly index and search your XML documents and reference those preferences using the ArcSDE DBTUNE XML_IDX_INDEX_TEXT parameter before creating your XML column.

4/2/2015