Preparing Oracle to store XML columns for the BIS workspace (Bathymetry Solution)

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 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. This will allow you 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 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.

Attention :

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 in others.

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';

Etapes :

1. 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>;
   

2. Query the ctx_parameters view to determine what the default settings currently are for your Oracle installation.

   SELECT * FROM ctx_parameters;
   

3. 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;
   

4. 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;
   

5. 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.
6. 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.
7. Add the custom text preferences to the DBTUNE parameter XML_IDX_INDEX_TEXT before creating your XML column.
8. 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;
   

9. 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');
   

10. 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
    

11. Modify the value of the XML_IDX_INDEX_TEXT parameter to “LEXER sde.WORLDLEXER” for the DEFAULTS keyword in the SDE_dbtune table.

    sdedbtune -o alter -k DEFAULTS -P XML_IDX_INDEX_TEXT -v “LEXER sde.WORLDLEXER” -i sde:oracle11g:bis -s <server_name> -u sde -p sde -N
    

12. 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.

13. Modify the value of the XML_COLUMN_STORAGE parameter to DB_XML for the DEFAULTS keyword in the SDE_dbtune table.

    sdedbtune -o alter -k DEFAULTS -P XML_COLUMN_STORAGE -v DB_XML -i sde:oracle11g:bis -s <server_name> -u sde -p sde -N
    

14. 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;