EMP Text Index snippets

-- Exact interface is required by Oracle Text
create or replace procedure employee_search_datastore(
  rid  in rowid,
  tlob in out nocopy varchar2
)
is
  l_row emp%rowtype;
begin
  select *
  into l_row
  from emp
  where rowid = rid;

  tlob :=
 '<EMPNO>'|| l_row.EMPNO || '</EMPNO>'||
 '<ENAME>'|| l_row.ENAME || '</ENAME>'||
 '<JOB>'|| l_row.JOB || '</JOB>'||
 '<MGR>'|| l_row.MGR || '</MGR>'||
 '<HIREDATE>'|| l_row.HIREDATE || '</HIREDATE>'||
 '<SAL>'|| l_row.SAL || '</SAL>'||
 '<COMM>'|| l_row.COMM || '</COMM>'||
 '<DEPTNO>'|| l_row.DEPTNO || '</DEPTNO>';

end employee_search_datastore;

-- create configureation settings for text index in data dictionary
begin

  ctx_ddl.create_preference(
    preference_name => 'employee_data_store',
    object_name     => 'user_datastore'
  );
  
  ctx_ddl.set_attribute(
    preference_name => 'employee_data_store',
    attribute_name  => 'procedure',
    attribute_value => 'tyson_jouglet.employee_search_datastore' -- fully qualified procedure name
  );

  -- allow us to search in specific sections
  ctx_ddl.create_section_group(
    group_name => 'employee_section_group',
    group_type => 'XML_SECTION_GROUP'
  );
  
  ctx_ddl.add_field_section('employee_section_group','EMPNO',    'EMPNO',    true);
  ctx_ddl.add_field_section('employee_section_group','ENAME',    'ENAME',    true);
  ctx_ddl.add_field_section('employee_section_group','JOB',      'JOB',      true);
  ctx_ddl.add_field_section('employee_section_group','MGR',      'MGR',      true);
  ctx_ddl.add_field_section('employee_section_group','HIREDATE', 'HIREDATE', true);
  ctx_ddl.add_field_section('employee_section_group','SAL',      'SAL',      true);
  ctx_ddl.add_field_section('employee_section_group','COMM',     'COMM',     true);
  ctx_ddl.add_field_section('employee_section_group','DEPTNO',   'DEPTNO',   true);

  ctx_ddl.create_preference('employee_word_list', 'BASIC_WORDLIST');
  ctx_ddl.set_attribute('employee_word_list', 'NDATA_ALTERNATE_SPELLING', 'TRUE'); 
  ctx_ddl.set_attribute('employee_word_list', 'NDATA_BASE_LETTER',        'TRUE');   

  ctx_ddl.create_preference('employee_lexer', 'BASIC_LEXER');
  ctx_ddl.set_attribute('employee_lexer', 'MIXED_CASE',       'NO');   -- A => a
  ctx_ddl.set_attribute('employee_lexer', 'BASE_LETTER',      'YES'); -- Å => A => a
  ctx_ddl.set_attribute('employee_lexer', 'BASE_LETTER_TYPE', 'GENERIC'); -- all languages 

end;

-- create text index using preferences we just created
-- specified column is not important since we are using a user_datastore
create index ft_employee on emp(ename)
indextype is ctxsys.context
parameters('
  datastore     employee_data_store
  section group employee_section_group
  wordlist      employee_word_list
  lexer         employee_lexer
  stoplist      ctxsys.empty_stoplist
  memory 500M
');

-- common search string manipulation function
create or replace function convert_end_user_search (
    p_search in varchar2 )
    return varchar2 
is
    c_xml constant varchar2(32767) := '<query><textquery><progression>' ||
                                        '<seq>  #SEARCH#  </seq>' ||
                                        '<seq> ?#SEARCH#  </seq>' ||
                                        '<seq>  #SEARCH#% </seq>' ||
                                        '<seq> %#SEARCH#% </seq>' ||
                                      '</progression></textquery></query>';
    l_search varchar2(32767) := p_search;
begin
    -- remove special characters; irrelevant for full text search
    l_search := regexp_replace( l_search, '[<>{}/()*%&!$?.:,;\+#]', '' );

    return replace( c_xml, '#SEARCH#', l_search );
end;