ArcGIS Server .NET Help - 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.

It is also important to manage the text indexes properly to maintain good performance. ArcSDE for Oracle includes two scripts to help you configure your database to store XML data. See Scripts to tune an Oracle instance for ArcSDE XML storage for information on these scripts.

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 XML_IDX_INDEX_TEXT parameter 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. For an ArcIMS Metadata Service, you can reference a DBTUNE parameter in its ArcXML configuration file.

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. For ArcIMS Metadata Services, <mdUsername> is the user specified in the service's ArcXML configuration file.
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 is also described.
```
SELECT * FROM ctx_preferences;
```
Query the CTX_PREFERENCE_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, wordlists, 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 the WORLD_LEXER was returned in the list of ctx_objects, 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. If you have not yet created your geodatabase, you can alter the dbtune.sde file to include the settings you want. See Altering the dbtune file before creating the geodatabase for details. If the geodatabase already exists, you can 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 after it is created 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 wordlist and stoplist 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.

11/18/2013