/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE
  e_index_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_index_not_exists, -01418);
BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX XXAS_ASG_SNAPSHOT_TXT1';
EXCEPTION
  WHEN e_index_not_exists THEN
    NULL;
END;
/

DECLARE
  e_app_err EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_app_err, -20000);
BEGIN
  --
  -- Create the preferences; do nothing if error 20000 is raised and they already exist
  --
  ctx_ddl.create_preference('xxas_com_lexer_world', 'world_lexer');
  ctx_ddl.create_preference('xxas_com_search_wordlist_pref','BASIC_WORDLIST');
  ctx_ddl.set_attribute('xxas_com_search_wordlist_pref','PREFIX_INDEX','TRUE');
  ctx_ddl.set_attribute('xxas_com_search_wordlist_pref','PREFIX_MIN_LENGTH', 2);
  ctx_ddl.set_attribute('xxas_com_search_wordlist_pref','PREFIX_MAX_LENGTH', 5);
  ctx_ddl.set_attribute('xxas_com_search_wordlist_pref','WILDCARD_MAXTERMS', 15000);
  
EXCEPTION
  WHEN e_app_err THEN
    NULL;
END;
/

CREATE INDEX XXAS_ASG_SNAPSHOT_TXT1
       ON xxas_asg_snapshot(search_content)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS
         ('SYNC (ON COMMIT)
           LEXER xxas_com_lexer_world
           FILTER CTXSYS.NULL_FILTER
           SECTION GROUP CTXSYS.PATH_SECTION_GROUP
           WORDLIST xxas_com_search_wordlist_pref');

COMMIT; 
EXIT;
