Rem
Rem $Header: sdo/admin/semvalidate.sql /st_sdo_11.2.0/1 2011/04/29 12:19:52 sdas Exp $
Rem
Rem semvalidate.sql
Rem
Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      semvalidate.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    sdas        04/27/11 - update sem_version
Rem    matperry    03/10/11 - Created
Rem

----------------------------------------------------------------------
-- Called at the end of install/upgrade/downgrade to set approriate --
-- values in RDF_PARAMETER                                          --
----------------------------------------------------------------------

alter session set current_schema=MDSYS;

-- 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
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semvalidate: compile: '||SQLERRM);
    end;
    commit;
  end;
  end loop;
  close query_crs;


  -- now do check for validity --
  -- see if there are any invalid sem objects --
  -- RDFOLS_SECUREACCESS trigger will be invalid if OLS is disabled
  -- this should not affect overall validity of the installation
  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_%'    
         )
         and object_name <> 'RDFOLS_SECUREACCESS';

  -- 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 IN (''FLOAT_DOUBLE_DECIMAL'', ''NULL_LITERAL'', 
                             ''XSD_TIME'', ''XSD_BOOLEAN'') )
             or
             ( namespace = ''MDSYS'' and 
                   value IN (''FAILED_UNABLE_TO_LOCK_APPLICATION_TABLES'',
                             ''FAILED_INSUFFICIENT_WORKSPACE_PRIVILEGES'',
                             ''FAILED_OLS_POLICIES_ARE_ENABLED'') and
	       attribute = ''DATA_CONVERSION_CHECK'' )'
    into invalid_data;
  exception when others then
    if (sqlcode = -942) then null;
    else 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	'SEM_INSTALL: semvalidate: check data validity: '||SQLERRM);
    end if;
  end;

  -- set sem_version in the rdf_paramter table --

  -- see if we already have a row for sem_version --
  begin 
 
    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 = '11203', description = 'INVALID'
	where namespace = 'MDSYS' and attribute = 'SEM_VERSION';
      else
	insert into mdsys.rdf_parameter (namespace, attribute, value, description)
	values ('MDSYS', 'SEM_VERSION', '11203', 'INVALID');
      end if;     
    else
      if (sem_version_exists > 0) then
	update mdsys.rdf_parameter
	set value = '11203', description = 'VALID'
	where namespace = 'MDSYS' and attribute = 'SEM_VERSION';
      else
	insert into mdsys.rdf_parameter (namespace, attribute, value, description)
	values ('MDSYS', 'SEM_VERSION', '11203', 'VALID');
      end if;
    end if;

  end;

  commit;
  
end;
/

alter session set current_schema=SYS;