Rem
Rem $Header: ctx_src_2/src/dr/admin/bug24899895_apply.sql /st_ctx_12.2.0.1.0dbbp/2 2017/05/04 19:21:46 nspancha Exp $
Rem
Rem bug24899895_apply.sql
Rem
Rem Copyright (c) 2016, 2017, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      bug24899895_apply.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    BEGIN SQL_FILE_METADATA
Rem    SQL_SOURCE_FILE: ctx_src_2/src/dr/admin/bug24899895_apply.sql
Rem    SQL_SHIPPED_FILE: ctx/admin/bug24899895_apply.sql
Rem    SQL_PHASE: CTXPLB
Rem    SQL_STARTUP_MODE: NORMAL
Rem    SQL_IGNORABLE_ERRORS: NONE
Rem    SQL_CALLING_FILE: NONE
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nspancha    05/04/17 - Changing SQL_PHASE to use CTXSYS
Rem    nspancha    04/17/17 - Backport
Rem                           nspancha_backport21668421_12.2.0.1
Rem                           from st_ctx_12.2.0.1.0
Rem    nspancha    10/27/16 - Created. CI Backport inclusion for bug 24899895
Rem

@@?/rdbms/admin/sqlsessstart.sql

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET SERVEROUTPUT ON

ALTER SESSION SET CURRENT_SCHEMA = SYS;

drop procedure DEFAULT_ADD_STOPWORD;

CREATE OR REPLACE PROCEDURE exec_alter_sql(stm   IN VARCHAR2,
                         owner IN VARCHAR2)
IS
  cur     NUMBER;
  rc      NUMBER;
  uid     NUMBER;
  err     varchar2(500);
  synced  NUMBER := 1;
BEGIN
  cur := dbms_sql.open_cursor;

  select user_id into uid from all_users where username = owner;
  sys.dbms_sys_sql.parse_as_user(cur, stm, dbms_sql.v7, uid);

  rc := dbms_sql.execute(cur);
  dbms_sql.close_cursor(cur);
exception 
  when others then
    err := dbms_utility.format_error_stack;
    dbms_output.put_line(err);
END exec_alter_sql;
/
show err

declare
  replacement       BOOLEAN;
  ixpidtab          ctxsys.dr_def.id_tab;
  stm               VARCHAR2(1000);  
BEGIN
-- Loop over all Text indexes that have idx_type=4 ie. Context v2 and mark 
-- them as UNUSABLE
-- For 12.2.0.1 that refers to only JSON indexes 

  FOR r_indexes IN (SELECT idx_id, idx_option, u.username idx_owner, idx_name, idx_status
                    FROM ctxsys.dr$index i, sys.dba_users u
                    WHERE i.idx_owner# = u.user_id
                    AND idx_type = 4
                    AND idx_status NOT LIKE 'UNUSABLE'
                    ORDER BY idx_id) LOOP
    
    dbms_output.put(
      'Index ' || r_indexes.idx_owner || '.' || r_indexes.idx_name || ': ');

    BEGIN
          stm :=
            'alter index ' || 
            dbms_assert.enquote_name(r_indexes.idx_owner, FALSE) ||
            '.' || dbms_assert.enquote_name(r_indexes.idx_name, FALSE) ||
            ' unusable';
          sys.exec_alter_sql(stm, r_indexes.idx_owner);

        EXCEPTION
          WHEN OTHERS THEN            
            dbms_output.put_line(
              'Following error raised when marking index as unusable due to empty stoplist issue.');
            dbms_output.put_line(dbms_utility.format_error_stack);
    END;
    
   END LOOP;
END;
/
show errors

drop procedure exec_alter_sql;

ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;

--Drop all the default stoplists
BEGIN
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_DANISH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_FINNISH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_DUTCH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_PORTUGUESE_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_GENERIC_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_GERMAN_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_SCHINESE_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_TCHINESE_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_SWEDISH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_GERMAN_DIN_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_CATALAN_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_FRENCH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_SPANISH_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_ITALIAN_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_BRAZ_PORT_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_FR_CAN_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_AM_SP_STOPLIST');
  ctx_ddl.drop_stoplist('CTXSYS.JSONREST_MEX_SP_STOPLIST');
EXCEPTION
   WHEN OTHERS THEN
     null;
END;
/
show errors

@?/rdbms/admin/sqlsessend.sql
 

/**** ReadMe notes for CI Backport bug 24899895
* This script serves as a data patch for the backport. Like the change in the 
* original txn, it drops all default stoplists for JSON search indexes (ie. 
* context v2 indexes) and marks all those context v2 indexes as UNUSABLE. The 
* user must therefore drop and re-create those indexes, as the index's 
* metadata will incorrectly contain the stoplist words untill it is dropped 
* and re-created.
*
* To list those indexes that need to be be dropped and re-created, the DBA  
* user may use the following query from within the SYS schema: 
*
* SELECT idx_id, u.username idx_owner, idx_name
*  FROM ctxsys.dr$index i, sys.dba_users u
*  WHERE i.idx_owner# = u.user_id
*  AND idx_type = 4                    
*  ORDER BY idx_id;
****/
