Rem Rem $Header: ctx_src_2/src/dr/admin/u1102030.sql /st_ctx_11.2.0/6 2011/07/13 04:20:57 rkadwe Exp $ Rem Rem u1200000.sql Rem Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem u1200000.sql - Rem Rem DESCRIPTION Rem upgrade from 11.2.0.2 to 11.2.0.3 Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rkadwe 07/12/11 - Forward index upgrade bug Rem rkadwe 06/21/11 - Backport rkadwe_bug-12659548 from main Rem rkadwe 06/07/11 - Backport section attribute framework Rem gauryada 05/31/11 - Backport snippet support for result set interface Rem rpalakod 02/08/11 - upgrade to 11.2.0.3 Rem rpalakod 02/08/11 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 REM =================================================================== REM Bug 9950719: fuzzy_score has a mininum value of 1 REM =================================================================== update dr$object_attribute set oat_val_min = 1 where oat_id = 70103; REM ======================================================================== REM Enable query_filter_cache_size attribute for basic_storage REM ======================================================================== begin insert into dr$object_attribute values (90118, 9, 1, 18, 'QUERY_FILTER_CACHE_SIZE', null, 'N', 'N', 'Y', 'I', '0', 0, null, 'N'); commit; exception when dup_val_on_index then null; end; / REM ======================================================================== REM filter cache stats view stub REM ======================================================================== REM this is a dummy until the types and packages we need get created create or replace view ctx_filter_cache_statistics as select 0 fcs_index_owner, 0 fcs_index_name, 0 fcs_partition_name, 0 fcs_size, 0 fcs_entries, 0 fcs_requests, 0 fcs_hits from dual where 1 = 2; create or replace public synonym ctx_filter_cache_statistics for ctxsys.ctx_filter_cache_statistics; grant select on ctx_filter_cache_statistics to public; ------------------------------------------------------------------------- --- dr$stopword ------------------------------------------------------------------------- declare errnum number; begin execute immediate(' alter table dr$stopword add spw_pattern varchar2(512) default NULL'); exception when others then errnum := SQLCODE; if (errnum = -01430) then null; else raise; end if; end; / ------------------------------------------------------------------------- --- ctx_stopwords ------------------------------------------------------------------------- create or replace view ctx_stopwords as select u.name spw_owner, spl_name spw_stoplist, decode(spw_type, 1, 'STOP_CLASS', 2, 'STOP_WORD', 3, 'STOP_THEME', null) spw_type, spw_word spw_word, decode(spw_language, 'ALL', null, spw_language) spw_language, spw_lang_dependent spw_lang_dependent, spw_pattern spw_pattern from dr$stoplist, dr$stopword, sys.user$ u where spl_id = spw_spl_id and spl_owner# = u.user# / create or replace public synonym ctx_stopwords for CTXSYS.CTX_STOPWORDS; grant select on CTX_STOPWORDS to PUBLIC; ------------------------------------------------------------------------- --- ctx_user_stopwords ------------------------------------------------------------------------- create or replace view CTX_USER_STOPWORDS as select spl_name spw_stoplist, decode(spw_type, 1, 'STOP_CLASS', 2, 'STOP_WORD', 3, 'STOP_THEME', null) spw_type, spw_word spw_word, decode(spw_language, 'ALL', null, spw_language) spw_language, spw_lang_dependent spw_lang_dependent, spw_pattern spw_pattern from dr$stoplist, dr$stopword where spl_id = spw_spl_id and spl_owner# = userenv('SCHEMAID') / create or replace public synonym CTX_USER_STOPWORDS for CTXSYS.CTX_USER_STOPWORDS; grant select on CTX_USER_STOPWORDS to PUBLIC; REM ======================================================================== REM Add FORWARD_INDEX, O_TABLE_CLAUSE, O_INDEX_CLAUSE, D_TABLE_CLAUSE Rem and D_INDEX_CLAUSE REM ======================================================================== BEGIN insert into dr$object_attribute values (90119, 9, 1, 19, 'FORWARD_INDEX', '', 'N', 'N', 'Y', 'B', 'FALSE', null, null, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90120, 9, 1, 20, 'O_TABLE_CLAUSE', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90121, 9, 1, 21, 'O_INDEX_CLAUSE', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90122, 9, 1, 22, 'SAVE_COPY', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90123, 9, 1, 23, 'D_TABLE_CLAUSE', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90124, 9, 1, 24, 'SAVE_COPY_MAX_SIZE', '', 'N', 'N', 'Y', 'I', '0', null, null, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90125, 9, 1, 25, 'SN_TABLE_CLAUSE', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (90126, 9, 1, 26, 'SN_INDEX_CLAUSE', '', 'N', 'N', 'Y', 'S', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / REM ======================================================================== REM Added configuration column REM ======================================================================== declare errnum number; begin execute immediate(' alter table dr$index add idx_config_column VARCHAR2(256) default NULL'); exception when others then errnum := SQLCODE; if (errnum = -01430) then null; else raise; end if; end; / REM =================================================================== REM Section specific attributes REM =================================================================== rem This table holds section specific attribute values declare errnum number; begin execute immediate(' alter table dr$class add constraint drc$cla_key PRIMARY KEY (cla_id) USING INDEX STORAGE (INITIAL 5K NEXT 5K)'); exception when others then errnum := SQLCODE; if (errnum = -02260) then null; else raise; end if; end; / PROMPT ... creating table dr$section_attribute declare errnum number; begin execute immediate(' create table dr$section_attribute ( sca_sgp_id number, sca_sec_id number, sca_sat_id number, sca_value varchar2(500), primary key(sca_sgp_id, sca_sec_id, sca_sat_id) ) organization index'); exception when others then errnum := SQLCODE; if (errnum = -00955) then null; else raise; end if; end; / update dr$object_attribute set oat_datatype = 'I' where oat_cla_id = 5; BEGIN insert into dr$object_attribute values (50113, 5, 1, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50213, 5, 2, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50313, 5, 3, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50513, 5, 5, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50613, 5, 6, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50713, 5, 7, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); insert into dr$object_attribute values (50813, 5, 8, 13, 'SECTION_ATTRIBUTE', '', 'N', 'N', 'Y', 'I', 'NONE', null, 500, 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$class values (24, 'SECTION', 'Section', 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object values (24, 1, 'BASIC_SECTION', 'basic section', 'N'); EXCEPTION when dup_val_on_index then null; END; / BEGIN insert into dr$object_attribute values (240101, 24, 1, 1, 'SECTION_ID', 'unique identifier for this section in index', 'N', 'N', 'Y', 'I', 'NONE', null, null, 'N'); insert into dr$object_attribute values (240102, 24, 1, 2, 'SECTION_NAME', 'section name', 'N', 'N', 'Y', 'S', 'NONE', null, null, 'N'); insert into dr$object_attribute values (240103, 24, 1, 3, 'TAG', 'tag mapping to this section', 'N', 'N', 'Y', 'S', 'NONE', null, null, 'N'); insert into dr$object_attribute values (240104, 24, 1, 4, 'TOKEN_TYPE', 'token_type for this section', 'N', 'N', 'Y', 'I', 'NONE', null, null, 'N'); insert into dr$object_attribute values (240105, 24, 1, 5, 'VISIBLE', 'Is this section visible to body', 'N', 'N', 'Y', 'B', 'FALSE', null, null, 'N'); insert into dr$object_attribute values (240106, 24, 1, 6, 'READ_ONLY', 'Is this section read_only (non-updateable)', 'N', 'N', 'Y', 'B', 'TRUE', null, null, 'N'); insert into dr$object_attribute values (240107, 24, 1, 7, 'DATATYPE', 'section datatype', 'N', 'N', 'Y', 'I', 'NONE', null, null, 'N'); insert into dr$object_attribute values (240110, 24, 1, 10, 'SEARCHABLE', 'SDATA values stored in the $Sn tables', 'N', 'N', 'Y', 'B', 'FALSE', null, null, 'N'); insert into dr$object_attribute values (240111, 24, 1, 11, 'SORTABLE', 'SDATA values stored in the $S table', 'N', 'N', 'Y', 'B', 'TRUE', null, null, 'N'); EXCEPTION when dup_val_on_index then null; END; /