Edit D:\app\Administrator\product\11.2.0\dbhome_1\md\admin\semrelod.sql
Rem Rem $Header: sdo/admin/semrelod.sql /main/3 2009/05/28 09:16:02 matperry Exp $ Rem Rem semrelod.sql Rem Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. Rem Rem NAME Rem semrelod.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 MODIFIED (MM/DD/YY) Rem matperry 05/26/09 - add check for rdfctx_admin role before trying to Rem install rdfctx Rem ayalaman 05/19/09 - reload CTX, WM and SA implementations Rem matperry 05/18/09 - set rdf_match to a dummy function if semantic Rem data needs migration from 11.1 to 11.2 Rem matperry 02/11/09 - Created Rem ---------------------------------------------------------------------- -- Loads PL/SQL packages and loads sdordf.jar -- ---------------------------------------------------------------------- set echo off; alter session set current_schema=MDSYS; Rem RDF Match stuff @@sdordfa.sql Rem Load RDF types and packages @@sdordfh.sql @@sdordfxh.sql @@prvrdftp.plb Rem Load SEM context @@sdosemctxh.sql Rem Load SEM log @@sdosemlogh.sql Rem Load SEM utility @@sdosemutlh.sql Rem Load SEM owlfast inference @@sdoseminfh.sql @@sdoseminfhh.sql @@sdoseminfih.sql Rem Load SEM owlfast validation @@sdosemvalh.sql Rem MS related @@sdoseminfmh.plb @@sdordfai.plb Rem MS related @@sdoseminfmr.plb @@sdordfm.plb @@sdordfrb.sql Rem Load RDF package body @@sdordfb.plb @@sdordfxb.plb Rem Add context: CREATE OR REPLACE CONTEXT SDO_SEM_CTX using MDSYS.SDO_SEM_CTX accessed globally; Rem Load context implementations @@sdosemctxb.plb Rem Load Log implementations @@sdosemlogb.plb Rem Load clique building package @@sdosemsam.plb Rem Load SEM implementations @@sdosemutlb.plb @@sdoseminfb.plb @@sdoseminfhb.plb @@sdoseminfib.plb @@sdosemvalb.plb Rem SEM operators @@sdosemopih.plb @@sdosemopib.plb @@sdosemop.plb @@sdosemopst.plb Rem SEM Triggers @@sdosemtrig.plb REM RDF upgrade and data migration @@sdosemdmh.sql @@sdosemdmb.plb REM RDF/OWL downgrade preparation package @@sdosemdgh.sql @@sdosemdgb.plb @@sdosemdgu.plb alter session set current_schema=MDSYS; declare invalid_data pls_integer := 0; begin -- 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'') and value = ''INVALID''' into invalid_data; exception when others then if (sqlcode = -942) then null; else raise; end if; end; -- if invalid data exists then set rdf_match to a dummy implementation -- if (invalid_data > 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, 'Oracle Semantic Technologies Release 11.2 is invalid because data migration is 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; / show errors; alter session set current_schema=SYS; -- load sdordf @@semloadj.sql alter session set current_schema=MDSYS; -- --- reload implementations for optional components (WM, CTX, OLS & VPD) -- COLUMN :script_name1 NEW_VALUE comp_file1 NOPRINT VARIABLE script_name1 VARCHAR2(30) COLUMN :script_name2 NEW_VALUE comp_file2 NOPRINT VARIABLE script_name2 VARCHAR2(30) COLUMN :script_name3 NEW_VALUE comp_file3 NOPRINT VARIABLE script_name3 VARCHAR2(30) declare refcount number := 0; begin refcount := 0; begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''COMPONENT'' and attribute = ''RDFCTX''' into refcount; exception when others then null; end; if (refcount > 0) then :script_name1 := '@prvtrdfctx.plb'; else -- make sure rdfctx_admin role doesn't exist before install -- refcount := 0; begin execute immediate 'select count(*) from dba_roles where role = ''RDFCTX_ADMIN''' into refcount; exception when others then null; end; if (refcount > 0) then -- conflicting RDFCTX_ADMIN role exists: log error and don't do anything -- :script_name1 := dbms_registry.nothing_script; SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 'Not installing RDFCTX: a conflicting RDFCTX_ADMIN role exists'); else :script_name1 := '@sdordfctx.sql'; end if; end if; refcount := 0; begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''COMPONENT'' and attribute = ''RDFOWM''' into refcount; exception when others then null; end; if (refcount > 0) then :script_name2 := '@prvtrdfwm.plb'; else :script_name2 := dbms_registry.nothing_script; end if; refcount := 0; begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''COMPONENT'' and attribute in (''RDFVPD'', ''RDFOLS'')' into refcount; exception when others then null; end; if (refcount > 0) then :script_name3 := '@pvtrdfsa.plb'; else :script_name3 := '@sdordfsa.sql'; end if; end; / show errors; set define on; SELECT :script_name1 FROM DUAL; @&comp_file1 set define on; SELECT :script_name2 FROM DUAL; @&comp_file2 set define on; SELECT :script_name3 FROM DUAL; @&comp_file3 -- recompile any invalid objects -- declare num_invalid number; invalid_data number; sem_version_exists number; obj varchar2(32); otype varchar2(19); type cursor_type is REF CURSOR; query_crs cursor_type ; stmt varchar2(2000); begin -- try to compile any invalid objects -- stmt := 'select OBJECT_NAME, OBJECT_TYPE from all_objects where OWNER = ''MDSYS'' and status=''INVALID'' and ( object_name like ''RDF_%'' or object_name like ''SDO_RDF%'' or object_name like ''SDO_SEM%'' or object_name like ''SEM_%'' or object_name like ''SEMR_%'' or object_name like ''RDFR_%'' )'; open query_crs for stmt; loop begin fetch query_crs into obj, otype; exit when query_crs%NOTFOUND ; begin if (otype = 'PACKAGE BODY') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter package '|| obj ||' compile body'; elsif (otype = 'TYPE BODY') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter type '|| obj ||' compile body'; elsif (otype = 'TRIGGER') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter trigger '|| obj ||' compile'; elsif (otype = 'VIEW') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter view '|| obj ||' compile'; elsif (otype = 'INDEXTYPE') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter indextype '|| obj ||' compile'; elsif (otype = 'OPERATOR') then obj := sys.dbms_assert.enquote_name(obj,FALSE); execute immediate ' alter operator '|| obj ||' compile'; else null; -- no action for now end if; exception when others then null; end; commit; end; end loop; close query_crs; -- now do check for validity -- -- see if there are any invalid sem objects -- select count(*) into num_invalid from all_objects where OWNER = 'MDSYS' and status = 'INVALID' and ( object_name like 'RDF_%' or object_name like 'SDO_RDF%' or object_name like 'SDO_SEM%' or object_name like 'SEM_%' or object_name like 'SEMR_%' or object_name like 'RDFR_%' ); -- validity also depends on data migration -- -- see if semantic data is valid -- begin execute immediate 'select count(*) from mdsys.rdf_parameter where namespace = ''MDSYS'' and value = ''INVALID'' and (attribute = ''FLOAT_DOUBLE_DECIMAL'' or attribute = ''NULL_LITERAL'')' into invalid_data; exception when others then if (sqlcode = -942) then null; else raise; end if; end; -- set sem_version in the rdf_paramter table -- -- see if we already have a row for sem_version -- select count(*) into sem_version_exists from mdsys.rdf_parameter where namespace = 'MDSYS' and attribute = 'SEM_VERSION'; -- if there are any invalid objects or data then set description to invalid -- otherwise it is valid if ((num_invalid > 0) or (invalid_data > 0)) then if (sem_version_exists > 0) then update mdsys.rdf_parameter set value = '112', description = 'INVALID' where namespace = 'MDSYS' and attribute = 'SEM_VERSION'; else insert into mdsys.rdf_parameter (namespace, attribute, value, description) values ('MDSYS', 'SEM_VERSION', '112', 'INVALID'); end if; else if (sem_version_exists > 0) then update mdsys.rdf_parameter set value = '112', description = 'VALID' where namespace = 'MDSYS' and attribute = 'SEM_VERSION'; else insert into mdsys.rdf_parameter (namespace, attribute, value, description) values ('MDSYS', 'SEM_VERSION', '112', 'VALID'); end if; end if; commit; end; / -- add SKOSCORE rulebase if a network is present -- -- We should do this in semu111.sql but create_rulebase could be invalid, -- so we are doing it after loading all 11.2 packages declare network_exists number; skos_exists number; begin -- see if RDF Network is present in DB SELECT count(*) INTO network_exists FROM ALL_TABLES WHERE table_name='RDF_VALUE$' AND owner='MDSYS'; if (network_exists > 0) then -- create SKOS rulebase if it doesn't already exist -- skos_exists := 0; begin execute immediate 'select count(*) from mdsys.rdf_rulebase$ where name=''SKOSCORE''' into skos_exists; exception when others then null; end; if (skos_exists = 0) then mdsys.rdf_apis_internal.create_rulebase('SKOSCORE'); begin execute immediate 'update mdsys.rdf_rulebase$ set owner= (select owner from mdsys.rdf_rulebase$ where name=''RDFS'') where name=''SKOSCORE'''; exception when others then null; end; begin EXECUTE IMMEDIATE 'GRANT SELECT ON "SEMR_SKOSCORE" TO PUBLIC'; exception when others then null; end; begin EXECUTE IMMEDIATE 'GRANT SELECT ON "RDFR_SKOSCORE" TO PUBLIC'; exception when others then null; end; end if; commit; end if; end; / alter session set current_schema=SYS;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de