Rem Rem $Header: sdo/admin/semu112post.sql /st_sdo_11.2.0/5 2011/06/16 14:12:27 matperry Exp $ Rem Rem semu112post.sql Rem Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem semu112post.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem matperry 06/15/11 - do not update rdfctx after upgrade from Rem 11.2.0.2.0 P1 Rem spsundar 05/13/11 - rename sem_contains_score to sem_contains_count Rem matperry 05/04/11 - move view recreations to semrelod b/c they should Rem run on downgrade too Rem matperry 04/01/11 - add recreation of stats views and missing Rem partitions Rem matperry 03/10/11 - Created Rem ---------------------------------------------------------------------- -- Called after upgrade -- -- Makes any calls to the new codebase to finish upgrading the -- -- semantics installation -- ---------------------------------------------------------------------- alter session set current_schema=MDSYS; -- check value$ to see if data conversion is needed -- declare invalid_data pls_integer := 0; check_failed pls_integer := 0; rdf_parameter_exists pls_integer := 0; valid_11202plus pls_integer := 0; begin ------- data check not relevant for 11.2.0.2.0+ -------- -- see what type of install we are dealing with -- select count(*) into rdf_parameter_exists from all_objects where owner = 'MDSYS' and object_name = 'RDF_PARAMETER'; if (rdf_parameter_exists > 0) then -- look for a valid 11.2.0.2.0 or later install -- execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = :1 and attribute = :2 and description in (:3,:4) and value in (:5,:6,:7)' into valid_11202plus using 'MDSYS','SEM_VERSION','UPGRADING_VALID','VALID','11202','11202_P1','11203'; -- only do literal conversion for pre 11.2.0.2.0 -- if (valid_11202plus = 0) then -- check data -- begin execute immediate 'begin mdsys.sdo_rdf_internal.convert_old_rdf_data(migrate=>false); end;'; exception when others then SYS.DBMS_SYSTEM.KSDWRT( SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: convert_old_rdf_data: '||SQLERRM); end; -- see if convert_old_rdf_data check failed -- begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''MDSYS'' and attribute = ''DATA_CONVERSION_CHECK'' and value = ''FAILED_UNABLE_TO_LOCK_APPLICATION_TABLES''' into check_failed; exception when others then if (sqlcode = -942) then null; else SYS.DBMS_SYSTEM.KSDWRT( SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: convert_old_rdf_data check failed: '||SQLERRM); end if; end; -- see if semantic data is valid -- begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''MDSYS'' and (attribute = ''FLOAT_DOUBLE_DECIMAL'' or attribute = ''NULL_LITERAL'' or attribute = ''XSD_TIME'' or attribute = ''XSD_BOOLEAN'') and value = ''INVALID''' into invalid_data; exception when others then if (sqlcode = -942) then null; else SYS.DBMS_SYSTEM.KSDWRT( SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: convert_old_rdf_data check results: '||SQLERRM); end if; end; -- if invalid data exists then set rdf_match to a dummy implementation -- if (invalid_data > 0 or check_failed > 0) then execute immediate 'CREATE OR REPLACE FUNCTION RDF_MATCH(query varchar2, models MDSYS.RDF_Models, rulebases MDSYS.RDF_Rulebases, aliases MDSYS.RDF_Aliases, filter varchar2, index_status varchar2 default NULL, options varchar2 default NULL ) RETURN SYS.ODCINumberList AS BEGIN raise_application_error(-20000, ''Invalid semantic data exists. Please run sdo_rdf_internal.convert_old_rdf_data '' || ''as sysdba to convert invalid data and enable semantic querying.''); END;'; -- give log message -- SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: Oracle Semantic Technologies Release 11.2 is invalid because data migration may be needed. ' || 'Please run sdo_rdf_internal.convert_old_rdf_data as sysdba to convert invalid data and then ' || 'run the semrelod.sql script to enable semantic querying.'); end if; end if; end if; end; / show errors; -- Need to update rdfctx operator definitions -- declare cnt number; validNG number; begin -- first see if rdfctx is installed cnt := 0; begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''COMPONENT'' and attribute = ''RDFCTX''' into cnt; exception when others then null; end; -- look for a valid post NG installation -- validNG := 0; begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = :1 and attribute = :2 and description in (:3,:4) and value in (:5,:6)' into validNG using 'MDSYS','SEM_VERSION','UPGRADING_VALID','VALID','11202_P1','11203'; exception when others then null; end; -- if rdfctx is installed and there is no valid NG installation then keep going if (cnt > 0 and validNG = 0) then -- sem_contains operator definitions -- begin execute immediate ' alter operator sem_contains add binding (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains add binding (VC,VC,VC,INT,RDF_ALIASES): '||SQLERRM); end; begin execute immediate ' alter operator sem_contains add binding (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains add binding (CL,VC,VC,INT,RDF_ALIASES): '||SQLERRM); end; begin -- sem_contains_select -- -- for sem_contains_select, we only have one binding because bindings are -- keyed by the argument signature. We can't drop the only binding for an -- operator, so we have to (1) add a dummy one, (2) drop the old one, -- (3) add the new one, (4) drop the dummy one execute immediate ' alter operator sem_contains_select add binding (VARCHAR2, VARCHAR2) return NUMBER WITH INDEX CONTEXT, SCAN CONTEXT SemContextIndexMethods COMPUTE ANCILLARY DATA USING mdsys.sem_rdfctx_oper.sem_contains'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains_select add dummy binding: '||SQLERRM); end; begin execute immediate ' alter operator sem_contains_select drop binding (NUMBER)'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains_select drop old binding: '||SQLERRM); end; begin execute immediate ' alter operator sem_contains_select add binding (NUMBER) return CLOB ANCILLARY TO mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) USING mdsys.sem_rdfctx_oper.sem_rdfctx_select'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains_select add new binding: '||SQLERRM); end; begin execute immediate ' alter operator sem_contains_select drop binding (VARCHAR2, VARCHAR2)'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter sem_contains_select drop dummy binding: '||SQLERRM); end; begin -- sem_contains_count ... new operator in 11.2.0.3.0 -- execute immediate ' create or replace operator sem_contains_count binding (NUMBER) return NUMBER ANCILLARY TO mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, mdsys.RDF_ALIASES), mdsys.SEM_CONTAINS (CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) USING mdsys.sem_rdfctx_oper.sem_rdfctx_count'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: add sem_contains_count: '||SQLERRM); end; begin execute immediate ' grant execute on sem_contains_count to public'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: grant sem_contains_count: '||SQLERRM); end; begin execute immediate ' create or replace public synonym sem_contains_count for mdsys.sem_contains_count'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: create sem_contains_count synonym: '||SQLERRM); end; begin -- alter indextype to support new bindings -- execute immediate ' alter indextype SemContext add SEM_CONTAINS(VARCHAR2, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES), add SEM_CONTAINS(CLOB, VARCHAR2, VARCHAR2, INTEGER, mdsys.RDF_ALIASES) using SemContextIndexMethods with local range partition'; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: alter SemContext indextype: '||SQLERRM); end; begin sem_rdfctx_ir.create_all_staging_tables; exception when others then SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'SEM_INSTALL: semu112post: create temporary staging tables: '||SQLERRM); end; end if; end; / alter session set current_schema=SYS;