Rem
Rem ctxposup.sql
Rem
Rem Copyright (c) 2002, 2017, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      ctxposup.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This script contains common post-upgrade steps.  Developers
Rem      should keep this up-to-date so that it is compatible with
Rem      the latest versions of everything.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem      SQL_SOURCE_FILE: ctx_src_2/src/dr/admin/ctxposup.sql
Rem      SQL_SHIPPED_FILE: ctx_src_2/src/dr/admin/ctxposup.sql
Rem      SQL_PHASE: UPGRADE
Rem      SQL_STARTUP_MODE: NORMAL 
Rem      SQL_IGNORABLE_ERRORS: NONE 
Rem      SQL_CALLING_FILE: ctx/admin/ctxpatch.sql
Rem    END SQL_FILE_METADATA
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    nspancha    04/17/17 - Backport
Rem                           nspancha_ci_backport_21668421_12.2.0.1.170117opcdbbp
Rem                           from st_ctx_12.2.0.1.0
Rem    rkadwe      10/06/16 - Change default stoplist for JSON
Rem    rkadwe      10/06/16 - Change default stoplist for JSON
Rem    shuroy      09/08/16 - Bug 24616803: Adding drop statement for temp objs
Rem    pkosaraj    05/04/16 - Bug 22069248: upgscript
Rem    aczarlin    04/25/16 - RTI 19240430 handle lowercase owner,idx name
Rem    boxia       01/15/16 - Bug 22226636: replace user$ with _BASE_USER
Rem    surman      11/21/15 - 22097228: Force 30 character index objects
Rem    surman      10/09/15 - 21814723: Handle errors
Rem    aczarlin    09/29/15 - bug 21795261 add auto_opt
Rem    surman      05/05/15 - 20952246: Check constraints
Rem    surman      04/21/15 - 11067010: Fix view compile
Rem    surman      01/26/15 - 20411134: Add SQL metadata tags
Rem    aczarlin    09/29/14 - bug 19706070
Rem    aczarlin    02/26/14 - $R fix ltrim, ctxsys, index type 1
Rem    aczarlin    08/26/13 - bug 17231225 primary key in $R
Rem    surman      03/15/13 - 16473661: Common start and end scripts
Rem    rkadwe      09/21/11 - Bug 12960302
Rem    rkadwe      06/16/11 - Bug 12659548
Rem    ssethuma    03/18/11 - section specific attributes
Rem    rkadwe      08/04/10 - Bug 9964288
Rem    rpalakod    01/11/08 - Drop temp procedure setatt
Rem    gkaminag    10/07/04 - inst val proc 
Rem    ehuang      12/16/02 - 
Rem    ehuang      08/02/02 - ehuang_component_upgrade_2
Rem    ehuang      07/30/02 - Created
Rem

@@?/rdbms/admin/sqlsessstart.sql


REM ========================================================================
REM drop dummy operator and index implementation type
REM ========================================================================

drop operator dummyop;
drop package ctx_dummyop;
drop type DummyIndexMethods;

REM ========================================================================
REM drop temporary versions of ctx_ddl routines
REM ========================================================================
drop procedure dr$temp_crepref;
drop procedure dr$temp_cresg;
drop procedure dr$temp_setatt;
drop procedure dr$temp_setsecgrpatt;

REM ========================================================================
REM Recompile shared library 
REM ========================================================================

PROMPT STARTING DR0ULIB.SQL
@@dr0ulib.sql

REM ========================================================================
REM Attempt to recompile any invalid views
REM ========================================================================

declare
begin
  for c1 in (select object_name 
             from dba_objects 
             where status = 'INVALID'
               and owner = 'CTXSYS'
               and object_type = 'VIEW')
  loop
    begin
      execute immediate 
        'alter view '||dbms_assert.enquote_name(c1.object_name, FALSE)||
        ' compile';
    exception
       when others then null;
    end;
  end loop;
end;
/

REM ========================================================================
REM Recompile Packages
REM ========================================================================

REM Assumption is that the INDEXTYPE HEADERS are VALID at this point

PROMPT STARTING CTXPKH.SQL
@@ctxpkh.sql

PROMPT STARTING CTXPLB.SQL
@@ctxplb.sql

REM ========================================================================
REM Recompile Type Bodies
REM ========================================================================

@@ctxtyb.sql

REM ========================================================================
REM populate database object manifest
REM ========================================================================

@@ctxdbo.sql

REM ========================================================================
REM Attempt to recompile any leftover errant objects
REM ========================================================================

declare
  cbody varchar2(10);
  otype varchar2(30);
begin
  for c1 in (select object_type, object_name 
             from dba_objects 
             where status = 'INVALID'
               and owner = 'CTXSYS'
             order by object_type) 
  loop
    otype := c1.object_type;
    cbody := null;

    if (c1.object_type = 'PACKAGE BODY') then
      otype := 'PACKAGE';
      cbody := 'BODY';
    elsif (c1.object_type = 'TYPE BODY') then
      otype := 'TYPE';
      cbody := 'BODY';
    elsif (c1.object_type = 'VIEW') then
      -- 11067010: No 'BODY' for view compile
      cbody := '';
    end if;
    
    begin
      execute immediate 
        'alter '||otype||' '||dbms_assert.enquote_name(c1.object_name, FALSE)
        ||' compile '||cbody;
    exception
       when others then null;
    end;
  end loop;
end;
/

REM this is not a mistake -- we want to run this twice due to possible
REM cyclical dependencies

/

REM ========================================================================
REM Install latest version of validation procedure
REM ========================================================================
@@ctxval.sql


REM ========================================================================
REM Insert individual section attributes to dr$index_value table
REM ========================================================================
begin
  for c1 in (select ixv_idx_id, ixv_value, ixv_oat_id, oat_name, driv.rowid
               from dr$index_value driv, dr$object_attribute
              where ixv_oat_id = oat_id
                and oat_cla_id = DRIOBJ.CLASS_SECTION_GROUP
                and ixv_value like '%:%:%') loop
    drisgp.csv_to_section_attr(c1.ixv_idx_id, c1.oat_name, c1.ixv_value);
    delete from dr$index_value where rowid = c1.rowid;
  end loop;
commit;
end;
/

REM ========================================================================
REM special case; default policy AUTO_OPT_OBJ
REM ========================================================================

PROMPT creating default policy for auto_opt_obj

declare
  l_owner#  number;
  l_errnum  number;
  l_count   number;

begin

  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select count(*) into l_count from dr$index where
    idx_name = 'AUTO_OPT_OBJ' and
    idx_owner# = l_owner#;

  /* if this object does not exist yet, create it. */
  if (0 = l_count) then
    CTX_DDL.create_policy('CTXSYS.AUTO_OPT_OBJ',
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer         => 'CTXSYS.BASIC_LEXER',
      stoplist      => 'CTXSYS.EMPTY_STOPLIST',
      wordlist      => 'CTXSYS.BASIC_WORDLIST'
  );
  end if;

exception
  when others then
    l_errnum := SQLCODE;
    if (l_errnum = -20000) then
      null;
    else
      raise;
    end if;
end;
/

REM ========================================================================
REM special case; default policy STOP_OPT_LIST
REM ========================================================================

PROMPT creating default policy for STOP_OPT_LIST

declare
  l_owner#  number;
  l_errnum  number;
  l_count   number;
begin

  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select count(*) into l_count from dr$index where
    idx_name = 'STOP_OPT_LIST' and
    idx_owner# = l_owner#;

  /* if this object does not exist yet, create it. */
  if (0 = l_count) then
    CTX_DDL.create_policy('CTXSYS.STOP_OPT_LIST',
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer         => 'CTXSYS.BASIC_LEXER',
      stoplist      => 'CTXSYS.EMPTY_STOPLIST',
      wordlist      => 'CTXSYS.BASIC_WORDLIST'
  );
  end if;

exception
  when others then
    l_errnum := SQLCODE;
    if (l_errnum = -20000) then
      null;
    else
      raise;
    end if;
end;
/

REM ========================================================================
REM special case; default policy ENT_EXT_DICT_OBJ
REM ========================================================================


declare
  l_owner#  number;
  l_errnum  number;
  l_count   number;

begin
  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
  select idx_id into l_count from dr$index where
    idx_name = 'ENT_EXT_DICT_OBJ' and
    idx_owner# = l_owner#;

  /* if this object does not exist yet, create it. */
  if (0 = l_count) then
    CTX_DDL.create_policy('CTXSYS.ENT_EXT_DICT_OBJ',
      filter        => 'CTXSYS.NULL_FILTER',
      section_group => 'CTXSYS.NULL_SECTION_GROUP',
      lexer         => 'CTXSYS.BASIC_LEXER',
      stoplist      => 'CTXSYS.EMPTY_STOPLIST',
      wordlist      => 'CTXSYS.BASIC_WORDLIST'
  );
  end if;
exception
  when others then
    l_errnum := SQLCODE;
    if (l_errnum = -20000) then
      null;
    else
      raise;
    end if;
end;
/
show err

ALTER SESSION SET CURRENT_SCHEMA = SYS;
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

CREATE OR REPLACE PROCEDURE upgscript

-- 22097228: Force the maximum length of index names to 30 characters,
-- as the database is not yet completely upgraded to 12.2 and certain
-- data dictionary tables are still limited to 30 characters.
-- This should be removed post 12.2.
IS
  original_level NUMBER;
  new_level NUMBER;
  l_constraint_name ctxsys.drvutl.dr_id;
  l_table_name      ctxsys.drvutl.dr_id;
  cnt               NUMBER;
  replacement       BOOLEAN;
  ixpidtab          ctxsys.dr_def.id_tab;
  stm               VARCHAR2(1000);
  cons VARCHAR2(1000);
BEGIN
  SELECT ctxsys.drvutl.get_ora_event_level(30580)
    INTO original_level
    FROM dual;

  -- Add 128 to it.  This is a bitwise OR operation.
  new_level := original_level + 128 - bitand(original_level, 128);

  EXECUTE IMMEDIATE
    'alter session set events ''30580 level ' || new_level || '''';

  -- Loop over all Text indexes (not policies) that do not have full index
  -- names enabled.  If they do, we know they have been created since the
  -- constraints were added and thus don't need to be checked.
  -- 21814723: Restrict to indexes with valid state
  FOR r_indexes IN (SELECT idx_id, idx_option, u.username idx_owner, idx_name
                    FROM ctxsys.dr$index i, sys.dba_users u
                    WHERE i.idx_owner# = u.user_id
                    AND idx_option NOT LIKE '%O%'
                    AND idx_option NOT LIKE '%f%'
                    AND (idx_status = 'INDEXED' OR idx_status = 'NO_INDEX')
                    ORDER BY idx_id) LOOP
    dbms_output.put(
      'Index ' || r_indexes.idx_owner || '.' || r_indexes.idx_name || ': ');

    IF r_indexes.idx_option NOT LIKE '%P%' THEN
      -- Index is not partitioned
      l_constraint_name :=
        ctxsys.drvxtab.get_constraint_name(r_indexes.idx_owner, r_indexes.idx_name,
                                           r_indexes.idx_id, null, 'R');

      -- Remove quotes
      l_constraint_name := REPLACE(l_constraint_name, '"');

      l_table_name :=
        ctxsys.drvxtab.get_object_name(r_indexes.idx_owner, r_indexes.idx_name,
                                       r_indexes.idx_id, null, 'R');

      -- Remove quotes and owner name
      l_table_name := REPLACE(l_table_name, '"');
      l_table_name := REPLACE(l_table_name, r_indexes.idx_owner || '.');

      -- 22097228: We may not have a constraint so check dba_indexes
      SELECT COUNT(*)
        INTO cnt
        FROM dba_indexes
        WHERE table_name = l_table_name
          AND index_name = l_constraint_name
          AND owner = r_indexes.idx_owner;

      IF cnt = 0 THEN
        -- Constraint does not exist, rename index to create it.  This will
        -- also convert the index to full length index names if the event
        -- does not disable that.
        dbms_output.put_line('Adding constraint on $R');

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

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

        EXCEPTION
          WHEN OTHERS THEN
            -- 21814723: If something went wrong with the rename then
            -- output the error and continue with other indexes
            dbms_output.put_line(
              'Following error raised when renaming index to add constraint:');
            dbms_output.put_line(dbms_utility.format_error_stack);
        END;

      ELSE
        dbms_output.put_line('No changes needed');
      END IF;

    ELSE
      -- Index is partitioned, check each partition
      ctxsys.drixmd.GetAllPartitionIDs(r_indexes.idx_id, ixpidtab);
      replacement := FALSE;
      FOR i IN 1..ixpidtab.COUNT LOOP
        l_constraint_name :=
          ctxsys.drvxtab.get_constraint_name(
            r_indexes.idx_owner, r_indexes.idx_name,
            r_indexes.idx_id, ixpidtab(i), 'R');

        -- Remove quotes
        l_constraint_name := REPLACE(l_constraint_name, '"');

        l_table_name :=
          ctxsys.drvxtab.get_object_name(
            r_indexes.idx_owner, r_indexes.idx_name,
            r_indexes.idx_id, ixpidtab(i), 'R');

        -- Remove quotes and owner name
        l_table_name := REPLACE(l_table_name, '"');
        l_table_name := REPLACE(l_table_name, r_indexes.idx_owner || '.');

      -- 22097228: We may not have a constraint so check dba_indexes
        SELECT COUNT(*)
          INTO cnt
          FROM dba_indexes
          WHERE table_name = l_table_name
            AND index_name = l_constraint_name
            AND owner = r_indexes.idx_owner;

        IF cnt = 0 THEN
          replacement := TRUE;
        END IF;
      END LOOP;

      IF replacement THEN
        dbms_output.put_line('Adding constraint on $R');

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

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

        EXCEPTION
          WHEN OTHERS THEN
            -- 21814723: If something went wrong with the rename then
            -- output the error and continue with other indexes
            dbms_output.put_line(
              'Following error raised when renaming index to add constraint:');
            dbms_output.put_line(dbms_utility.format_error_stack);
        END;

      ELSE
        dbms_output.put_line('No changes needed');
      END IF;

    END IF;
  END LOOP;

  EXECUTE IMMEDIATE
    'alter session set events ''30580 level ' || original_level || '''';

end upgscript;
/
show err

ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;
set serverout on
exec sys.upgscript;

ALTER SESSION SET CURRENT_SCHEMA = SYS;

drop procedure exec_alter_sql;
drop procedure upgscript;

ALTER SESSION SET CURRENT_SCHEMA = CTXSYS;

REM END OF CTXPOSUP

@?/rdbms/admin/sqlsessend.sql
