Preparing Oracle to store XML columns for the BIS workspace—Help

Geodatabase 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 must be properly configured and maintained to optimize search results and performance, particularly for searching XML columns written in languages other than English.

The BIS queries tables 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. This will allow you to correctly index and search your XML documents and reference those preferences using the geodatabase DBTUNE parameter XML_IDX_INDEX_TEXT before creating your XML column.

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

You will need to have the Oracle Text component installed. The Text component is installed by default in Oracle 10g and 11g; however, if you did not do a default installation, the Text component may not have been installed. To see if it is installed, execute the following SQL statement while logged in to the database as SYSTEM or a user with DBA privileges. If no record is returned, the Text component is not installed. Run the Oracle installation to install the Text component.

SELECT owner, object_name
FROM all_objects
WHERE object_type = 'PACKAGE'
AND object_name = 'CTX_DDL';

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:
<mdUsername> is the user who will own the XML column. In this example, it can be BIS.
```
GRANT EXECUTE ON ctx_ddl TO <mdUsername>;
```
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. For example, this shows which lexer is being used by the CTXSYS.DEFAULT_LEXER preference. Any custom preferences created by other users are also described.
```
SELECT * FROM ctx_preferences;
```
Query the ctx_preferences_values view to ensure 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:
```
connect sde/sde@bis

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. For more information, see Alter configuration keywords 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
```
Modify the value of the XML_IDX_INDEX_TEXT parameter to “LEXER sde.WORLDLEXER” for the DEFAULTS keyword in the SDE_dbtune table. For more information, see Alter configuration keywords.
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 geodatabase DBTUNE XML_IDX_INDEX_TEXT parameter before creating your XML column.
Modify the value of the XML_COLUMN_STORAGE parameter to DB_XML for the DEFAULTS keyword in the SDE_dbtune table. For more information, see Alter configuration keywords

Verify the XML parameters for the DEFAULTS keyword in the SDE_dbtune table.

SELECT * FROM sde.dbtune ORDER BY 1,2;

SELECT * FROM sde.dbtune 
WHERE keyword = 'DEFAULTS' 
AND parameter_name LIKE 'XML%' 
ORDER BY 1,2;

Feedback on this topic?