OracleTextSearch Thesaurus Search

Configure OracleTextSearch for UCM

SearchIndexerEngineName=OracleTextSearch
IndexerDatabaseProviderName=SystemDatabase
AdditionalEscapeChars=-:#

Place the thesaurus file in a proper location, and then execute the below command in the terminal

ctxload -user USERNAME/PASSWORD -thes -name cbi_thesaurus -file NAME_OF_FILE

where
     USERNAME: username of the database
     PASSWORD: password for the database
     NAME_OF_FILE: name of the thesaurus file

In order to check whether the contents of the file are properly loaded or not, execute the queries in SQL Developer:

1. The following query will display the names of the thesaurus (in this case, only CBI_THESAURUS)

select * from CTX_THESAURI

2. The following query will display the list of the words that were loaded in the thesaurus:

select * from CTX_THES_PHRASES

This is the Java method:

    public void cbiThesaurusSearch() throws DataException, ServiceException, IOException {
        SystemUtils.trace(COMPONENT_DEBUG, "=====CBIThesaurusSearch STARTS=====");

        String searchKeyword = "";
        String searchKeyword2 = "";
        String queryText = "";

        SystemUtils.trace(COMPONENT_DEBUG, "Original QueryText: " + m_binder.getLocal("QueryText"));
        try {
            if (m_binder.getLocal(SEARCH_KEY) != null) {
                searchKeyword = m_binder.getLocal(SEARCH_KEY).toUpperCase();
                SystemUtils.trace(COMPONENT_DEBUG, "Search Keyword:" + searchKeyword);
                
                

                if (m_binder.getLocal(SEARCH_KEY_WITHIN) != null && (m_binder.getLocal(SEARCH_KEY_WITHIN).length() > 0)) {
                    searchKeyword2 = m_binder.getLocal(SEARCH_KEY_WITHIN).toUpperCase();
                    SystemUtils.trace(COMPONENT_DEBUG, "Search Keyword Within:" + searchKeyword2);
                }

                int index = m_binder.getLocal("QueryText").indexOf("(");
                queryText = m_binder.getLocal("QueryText").substring(0, index);
                SystemUtils.trace(COMPONENT_DEBUG, "Altered QueryText: " + queryText);

                DataBinder db = new DataBinder();
                db.putLocal("searchKey", searchKeyword);
                db.putLocal("thName", THESAURUS_NAME);
                db.putLocal("level", LEVEL);

                String expandedKeywords = "";

                ResultSet rs = m_workspace.createResultSet("CBIThesaurusSearch", db);
                DataResultSet dataContainer = new DataResultSet();
                dataContainer.copy(rs);
                for (dataContainer.first(); dataContainer.isRowPresent(); dataContainer.next()) {
                    String expandedKeyword = dataContainer.getStringValueByName("THEVALUES");
                    expandedKeywords = expandedKeywords + expandedKeyword + ",";
                }
                if (expandedKeywords.endsWith(",")) {
                    expandedKeywords = expandedKeywords.substring(0, expandedKeywords.length() - 1);
                }
                SystemUtils.trace(COMPONENT_DEBUG, "ExpandedKeywords: " + expandedKeywords);

                if (searchKeyword2 == "") {
                    m_binder.putLocal("QueryText", queryText + "(" + expandedKeywords + ")");
                } else {
                    m_binder.putLocal("QueryText",
                                      queryText + "((" + expandedKeywords + ")  (" + searchKeyword2 + "))");
                }

                SystemUtils.trace(COMPONENT_DEBUG, "Final QueryText: " + m_binder.getLocal("QueryText"));


                //m_binder.putLocal("IdcService", "GET_SEARCH_RESULTS");
                m_binder.putLocal("IdcService", "CBIDispSortSearch");
                executeService(m_binder, "sysadmin", false);
            }
        } catch (ServiceException s) {
            SystemUtils.trace(COMPONENT_DEBUG, "ServiceException " + s.getMessage());
        } catch (DataException d) {
            SystemUtils.trace(COMPONENT_DEBUG, "DataException " + d.getMessage());
        } catch (Exception e) {
            SystemUtils.trace(COMPONENT_DEBUG, "Exception " + e.getMessage());
            e.printStackTrace();
        } finally {
            m_workspace.releaseConnection();
        }
        SystemUtils.trace(COMPONENT_DEBUG, "=====CBIThesaurusSearch ENDS=====");
    }

And this is the query which is being called by the method:

SELECT DISTINCT INITCAP (val) THEVALUES
  FROM (SELECT *
          FROM (    SELECT REGEXP_SUBSTR (
                              (SELECT ctx_thes.syn (?, ?)
                                 FROM DUAL),
                              '[^{|}]+',
                              1,
                              LEVEL,
                              'i')
                              val
                      FROM DUAL
                CONNECT BY LEVEL <=
                              REGEXP_COUNT (
                                 (SELECT ctx_thes.syn (?, ?)
                                    FROM DUAL),
                                 '[^|]+'))
         WHERE val IS NOT NULL
        UNION
        SELECT *
          FROM (    SELECT REGEXP_SUBSTR (
                              (SELECT ctx_thes.bt (?, ?, ?)
                                 FROM DUAL),
                              '[^{|}]+',
                              1,
                              LEVEL,
                              'i')
                              val
                      FROM DUAL
                CONNECT BY LEVEL <=
                              REGEXP_COUNT (
                                 (SELECT ctx_thes.bt (?, ?, ?)
                                    FROM DUAL),
                                 '[^|]+'))
         WHERE val IS NOT NULL
        UNION
        SELECT *
          FROM (    SELECT REGEXP_SUBSTR (
                              (SELECT ctx_thes.nt (?, ?, ?)
                                 FROM DUAL),
                              '[^{|}]+',
                              1,
                              LEVEL,
                              'i')
                              val
                      FROM DUAL
                CONNECT BY LEVEL <=
                              REGEXP_COUNT (
                                 (SELECT ctx_thes.nt (?, ?, ?)
                                    FROM DUAL),
                                 '[^|]+'))
         WHERE val IS NOT NULL)

      searchKey varchar
      thName varchar
      searchKey varchar
      thName varchar
      searchKey varchar
      level int
      thName varchar
      searchKey varchar
      level int
      thName varchar
      searchKey varchar
      level int
      thName varchar
      searchKey varchar
      level int
      thName varchar