Rem
Rem ctxpreup.sql
Rem
Rem Copyright (c) 2002, 2017, Oracle and/or its affiliates. 
Rem All rights reserved.
Rem
Rem    NAME
Rem      ctxpreup.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      This script contains common pre-upgrade steps.  Developers
Rem      should keep this up-to-date so that it is compatible with
Rem      the latest versions of everything.  But, because it runs 
Rem      before any data dictionary changes, be careful that it is 
Rem      also compatible with the lowest supported starting version!
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/ctxpreup.sql
Rem      SQL_SHIPPED_FILE: ctx_src_2/src/dr/admin/ctxpreup.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      08/29/16 - XbranchMerge rkadwe_bug-24427155 from
Rem                           st_ctx_12.2.0.1.0
Rem    rkadwe      10/06/16 - Change default stoplist for JSON
Rem    rkadwe      08/29/16 - XbranchMerge rkadwe_bug-24427155 from
Rem                           st_ctx_12.2.0.1.0
Rem    nspancha    08/08/16 - Changing lexer options for JSON and SODA support
Rem    nspancha    06/15/16 - Reverting range search to include TS, not BD
Rem    nspancha    04/07/16 - 22267587:Fixing range search and timestamp opts
Rem    boxia       01/15/16 - Bug 22226636: replace user$ with _BASE_USER
Rem    surman      01/26/15 - 20411134: Add SQL metadata tags
Rem    thbaby      06/25/14 - 19064729: remove sharing=object from dr$ tables
Rem    rkadwe      01/29/14 - Add dr$temp_setsecgrpatt
Rem    surman      03/15/13 - 16473661: Common start and end scripts
Rem    rpalakod    01/10/08 - add temp version of ddl.set_attribute
Rem    wclin       03/02/07 - lrg 2885465: cleanup dummy impl type and ops
Rem                           first
Rem    gkaminag    10/28/05 - 
Rem    ehuang      08/02/02 - ehuang_component_upgrade_2
Rem    ehuang      07/30/02 - Created
Rem

@@?/rdbms/admin/sqlsessstart.sql

--------------------------------------------------------------------------
-- 19064729: remove sharing=object flag bit on DR$ tables
--------------------------------------------------------------------------
update sys.obj$ o
set o.flags=o.flags-131072
where bitand(o.flags, 131072)=131072 
and o.name in ('DR$CLASS', 'DR$OBJECT', 'DR$OBJECT_ATTRIBUTE', 
               'DR$OBJECT_ATTRIBUTE_LOV')
and o.owner# in (select user# from sys."_BASE_USER" where name='CTXSYS')
/
commit;

REM ========================================================================
REM temporary versions of ctx_ddl routines
REM ========================================================================
create or replace procedure dr$temp_crepref(
  p_pre_name in varchar2,
  p_obj_name in varchar2
) is
 l_owner# number;
 l_pre_id number;
 l_obj_id number;
 l_cla_id number;
begin
 select user# into l_owner# from sys."_BASE_USER" where name = 'CTXSYS';
 select dr_id_seq.nextval into l_pre_id from dual;
 select obj_id, obj_cla_id into l_obj_id, l_cla_id from dr$object
  where obj_name = p_obj_name;
 insert into dr$preference
   (pre_id, pre_name, pre_owner#, pre_obj_id, pre_cla_id, pre_valid)
    values
   (l_pre_id, p_pre_name, l_owner#, l_obj_id, l_cla_id, 'Y');

 commit;
exception
  when dup_val_on_index then
    commit;
  when others then
    raise;
end;
/

create or replace procedure dr$temp_cresg(
  p_pre_name in varchar2,
  p_obj_name in varchar2
) is
 l_owner# number;
 l_pre_id number;
 l_obj_id number;
 l_cla_id number;
begin
 select user# into l_owner# from sys."_BASE_USER" where name = 'CTXSYS';
 select dr_id_seq.nextval into l_pre_id from dual;
 select obj_id into l_obj_id from dr$object where obj_name = p_obj_name;
 insert into dr$section_group(sgp_id, sgp_owner#, sgp_name, sgp_obj_id) 
 values                      (l_pre_id, l_owner#, p_pre_name, l_obj_id);
 commit;
exception
  when dup_val_on_index then
    commit;
  when others then
    raise;
end;
/

/* Simple version of set_attribute - for default preferences ONLY
 * Restrictions:  
   1.) NO ERROR CHECKING!!!
   2.) For boolean values, enter '0' or '1' only
 */

create or replace procedure dr$temp_setatt(
  p_pre_name in varchar2,
  p_att_name in varchar2,
  p_att_val  in varchar2
) is
 l_owner# number;
 l_pre_id number;
 l_obj_id number;
 l_cla_id number;
 l_oat_id number;
 l_datatype char(1);
 l_aval varchar2(500);
begin

 l_aval := upper(p_att_val);

 select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';
 select pre_id, pre_obj_id, pre_cla_id into 
        l_pre_id, l_obj_id, l_cla_id
   from dr$preference where pre_name = p_pre_name and
                            pre_owner# = l_owner#;
 begin
   select oat_id, oat_datatype into 
          l_oat_id, l_datatype
     from dr$object_attribute
    where oat_cla_id = l_cla_id
      and oat_obj_id = l_obj_id
      and oat_name   = p_att_name
      and oat_system = 'N';
 exception
    when no_data_found then
       null;
 end;

 /* insert into dr$preference_value */

 begin
   insert into dr$preference_value(prv_pre_id, prv_oat_id, prv_value)
       values(l_pre_id, l_oat_id, l_aval);
 exception
   when dup_val_on_index then
     update dr$preference_value 
        set prv_value = l_aval
      where prv_oat_id = l_oat_id
        and prv_pre_id = l_pre_id;
 end;

 update dr$preference
    set pre_valid = 'N'
  where pre_id = l_pre_id;

 commit;

exception
  when others then
    null;
end;
/
/* Simple version of set_sec_grp_attr - for default preferences ONLY
 * Restrictions:  
   1.) NO ERROR CHECKING!!!
   2.) For boolean values, enter '0' or '1' only
 */
create or replace procedure dr$temp_setsecgrpatt(
  p_grp_name in varchar2,
  p_att_name in varchar2,
  p_att_val  in varchar2
) is
 l_owner# number;
 l_oat_id number;
 l_sgp_id number;
 l_attr_name   varchar2(30);
 l_aval        varchar2(500);
 l_secid  binary_integer;
 errnum   number;
 stmt_ins_sec  varchar2(1000);
 stmt_ins_attr varchar2(1000);
 isJsonEnable varchar2(1);

begin
  l_attr_name := upper(p_att_name);
  l_aval := upper(p_att_val);

  select user# into l_owner# from sys."_BASE_USER" where name='CTXSYS';

  /* get section group id */
  select sgp_id into l_sgp_id from dr$section_group 
    where sgp_owner# = l_owner# and sgp_name = p_grp_name;

  /* get l_oat_id */
  begin
    select oat_id into l_oat_id from dr$object_attribute
      where oat_cla_id = 5
        and oat_obj_id = 8
        and oat_name   = l_attr_name
        and oat_system = 'N';
  exception
    when no_data_found then
      null;
  end;
     
  if (l_oat_id = 50818 and l_aval = 'ALL') then
    
    begin
      execute immediate 'select SGA_VALUE 
        from dr$section_group_attribute
      where sga_id = '||l_sgp_id||' and
           sga_sgat_id = 50817' into isJsonEnable;
    exception
      when no_data_found then
      	isJsonEnable := 0;
      when others then
       raise;
    end;

    if (isJsonEnable = 1) then
      l_aval := ',N,TS';    
    else 
      l_aval := ',N,B,TS';
    end if;
    
  elsif (l_oat_id = 50818 and l_aval = 'NUMBER') then
    l_aval := ',N';
  end if;

  /* insert into dr$section_group_attribute */
  begin
    execute immediate 'insert into dr$section_group_attribute
      (sga_id, sga_sgat_id, sga_value)
      values('||l_sgp_id||','||l_oat_id||','''||l_aval||''')';
  exception
    when dup_val_on_index then
     null;
    when others then
      errnum := SQLCODE;
      if (errnum = -00492) then
        null;
      else
        raise;
      end if;
  end;

  begin
  if (l_oat_id = 50818) then
    /* add sections for 5 types */
    /*template to insert into dr$section */
    stmt_ins_sec := 'insert into dr$section (' ||
                    'sec_id,' || 
                    'sec_type,' || 
                    'sec_name,' ||
                    'sec_sgp_id,' ||
                    'sec_tag,' ||
                    'sec_fid,' ||
                    'sec_visible,' ||
                    'sec_datatype' ||
                    ') values (';

    /* template to insert into dr$section_attribute */
    stmt_ins_attr := 'insert into dr$section_attribute values(';

    /* 1. number */
    if(INSTR(l_aval, ',N') > 0) then
      select dr_id_seq.nextval into l_secid from dual;
      execute immediate (
        stmt_ins_sec || l_secid || ',10,''' || p_grp_name || 
        'NUM*'',' || l_sgp_id || ',''num*'',101,''N'',2)'
      );

      /* section_name */
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' ||l_secid || 
        ', 240102, ''' || p_grp_name || 'NUM*'')'
      );
      /* tag */
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240103, ''num*'')'
      );
      /* token_type */
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240104, ''101'')'
      );
      /* visible */
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240105, ''0'')'
      );
      /* datatype */
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240107, ''2'')'
      );
      /* optimized_for*/
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240110, ''2'')'
      );
    end if;
    
    -- 2. binary double
    if(INSTR(l_aval, ',B') > 0) then    
      select dr_id_seq.nextval into l_secid from dual;
      execute immediate (
        stmt_ins_sec || l_secid || ',10,''' || p_grp_name ||
        'DB*'',' || l_sgp_id || ',''db*'',102,''N'',101)'
      );
      --section_name
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' ||l_secid ||
        ', 240102, ''' || p_grp_name || 'DB*'')'
      );
      --tag
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240103, ''db*'')'
      );
      --token_type
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240104, ''102'')'
      );
      --visible
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240105, ''0'')'
      );
      --datatype
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240107, ''101'')'
      );
      --optimized_for
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240110, ''2'')'
      );
    end if;

    --3. timestamp with time zone 
    if(INSTR(l_aval, ',TS') > 0) then
      select dr_id_seq.nextval into l_secid from dual;
      execute immediate (
        stmt_ins_sec || l_secid || ',10,''' || p_grp_name ||
        'TS*'',' || l_sgp_id || ',''ts*'',103,''N'',188)'
      );

      --section_name
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' ||l_secid ||
        ', 240102, ''' || p_grp_name || 'TS*'')'
      );
      --tag
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240103, ''ts*'')'
      );
      --token_type
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240104, ''103'')'
      );
      --visible
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240105, ''0'')'
      );
      --datatype
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240107, ''188'')'
      );
      --optimized_for
      execute immediate (
        stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240110, ''2'')'
      );
    end if;
    /*
    --4. interval year to month 
    select dr_id_seq.nextval into l_secid from dual;
    execute immediate (
      stmt_ins_sec || l_secid || ',10,''' || p_grp_name ||
      'IYM*'',' || l_sgp_id || ',''iym*'',104,''N'',189)'
    );

    --section_name
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' ||l_secid ||
      ', 240102, ''' || p_grp_name || 'IYM*'')'
    );
    --tag
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240103, ''iym*'')'
    );
    --token_type
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240104, ''104'')'
    );
    --visible
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240105, ''0'')'
    );
    --datatype
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240107, ''189'')'
    );
    --optimized_for
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240110, ''2'')'
    );

    --5. interval day to second
    select dr_id_seq.nextval into l_secid from dual;
    execute immediate (
      stmt_ins_sec || l_secid || ',10,''' || p_grp_name ||
      'IDS*'',' || l_sgp_id || ',''ids*'',105,''N'',190)'
    );

    --section_name
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' ||l_secid ||
      ', 240102, ''' || p_grp_name || 'IDS*'')'
    );
    --tag 
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240103, ''ids*'')'
    );
    --token_type
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240104, ''105'')'
    );
    --visible
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240105, ''0'')'
    );
    --datatype
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240107, ''190'')'
    );
    --optimized_for
    execute immediate (
      stmt_ins_attr || l_sgp_id || ', ' || l_secid || ', 240110, ''2'')'
    );
  */
 end if;
exception
  when no_data_found then
    null;
  when dup_val_on_index then
    null;
  when others then
    errnum := SQLCODE;
    if (errnum = -00492) then
      null;
    else
      raise;
    end if;
end;
end;
/
show errors;


REM ========================================================================
REM drop DummyIndexMethods, dummyop, and ctx_dummyop
REM ========================================================================
drop type DummyIndexMethods force;
drop operator dummyop force;
drop package ctx_dummyop;


REM ========================================================================
REM dummy index implementation type in case t scripts need it
REM ========================================================================

create or replace type DummyIndexMethods authid definer as object
(
   key          RAW(4),
   objid        RAW(4),
   tmpobjid     RAW(4),

   static function ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
            return number
);
/

PROMPT Create dummy implementation type body ...
PROMPT

create or replace type body DummyIndexMethods is

/* ----------------------- ODCIGetInterfaces ------------------------------ */

static function ODCIGetInterfaces(
  ifclist out    sys.ODCIObjectList
) return number 
is 
begin 
  ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2')); 
  return sys.ODCIConst.Success; 
end ODCIGetInterfaces; 
end;
/
show errors


REM ========================================================================
REM dummy operator in case t scripts need it
REM ========================================================================

create or replace package ctx_dummyop authid definer as
    function dummyop(Colval in varchar2, 
                             Text in varchar2, ia sys.odciindexctx, 
                             sctx IN OUT DummyIndexMethods,
                             cflg number /*, env sys.ODCIEnv*/)
      return number is language C
      name "contains"
      library dr$lib
      with context
      parameters(
        context,
        Colval,
        Colval INDICATOR,
        Text,
        Text INDICATOR,
        Text LENGTH,
        ia,
        ia INDICATOR STRUCT,
        sctx,
        sctx INDICATOR STRUCT,
        cflg,
        cflg INDICATOR,
/*
        env,
        env  INDICATOR STRUCT,
*/
        return OCINumber
      );
end ctx_dummyop;
/

PROMPT Create dummy operator ...
PROMPT

create or replace operator dummyop binding 
  (varchar2, varchar2) return number 
     with index context, scan context DummyIndexMethods
without column data using ctx_dummyop.dummyop;

grant execute on dummyop to public;

@?/rdbms/admin/sqlsessend.sql
