Rem
Rem $Header: sdo/admin/semu112.sql /st_sdo_11.2.0/4 2011/06/01 07:31:16 spsundar Exp $
Rem
Rem semu112.sql
Rem
Rem Copyright (c) 2010, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      semu112.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        05/22/11 - move uniq and pkey indexes on IOTs to proper
Rem                           tablespace
Rem    sdas        05/20/11 - interface change for prllidx_create subprograms
Rem    spsundar    05/13/11 - update extractor interfaces
Rem    matperry    03/31/11 - update link_id column
Rem    matperry    03/10/11 - refactor: move view recreation to post semrelod
Rem                           processing using sdo_rdf_internal
Rem    matperry    03/10/11 - add named graph migration
Rem    matperry    10/15/10 - update datatype indexing for orageo:WKTLiteral
Rem    matperry    04/02/10 - Created
Rem

------------------------------------------------------------------------------------------
-- The "u" script for each prior release performs the following actions to upgrade      --
-- from release NNN to NNN+1. If NNN+1 is not the current release, then the "u" script  --
-- invokes the script for release NNN+1, so that the upgrade is "stacked.".             --
------------------------------------------------------------------------------------------

set echo off;
alter session set current_schema=MDSYS;
set define on;

-- make sure we do the upgrade without errors --
whenever sqlerror continue;

-- First mark that we are upgrading --
declare
  valid_exists NUMBER;
  upg_desc     VARCHAR2(100);
begin

  EXECUTE IMMEDIATE '
    SELECT COUNT(*) 
    FROM MDSYS.RDF_PARAMETER
    WHERE namespace = :1 and
          attribute = :2 and
          description = :3'
  INTO valid_exists
  USING 'MDSYS','SEM_VERSION','VALID';

  if (valid_exists > 0) then
    upg_desc := 'UPGRADING_VALID';
  else
    upg_desc := 'UPGRADING_INVALID';
  end if;

  EXECUTE IMMEDIATE '
    UPDATE MDSYS.RDF_PARAMETER
    SET description = :1
    WHERE namespace = :2 and
	  attribute = :3'
  USING upg_desc,'MDSYS','SEM_VERSION';

  EXCEPTION WHEN OTHERS THEN 
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
      'SEM_INSTALL: semu112: set upgrade in rdf_parameter: '||SQLERRM);
end;
/

prompt preparing semantic network for named graph support ...

--///////////////////////// Upgrade SEM for Named Graph Support \\\\\\\\\\\\\\\\\\\\\\\\\\--

--//////////// Drop obsolete objects (views, packages, etc.) from prior release \\\\\\\\\\--

-- drop sdo_sem_magic package ... not used anywhere
-- also cleanup sem_hash_internal
declare
  stmt varchar2(1000);
begin

  begin
    stmt := 'drop package mdsys.sdo_sem_magic'; execute immediate stmt;
    exception when others then 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
        'SEM_INSTALL: semu112: drop sdo_sem_magic: '||SQLERRM);
  end;

  begin
    stmt := 'drop package mdsys.sem_hash_internal'; execute immediate stmt;
    exception when others then 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
        'SEM_INSTALL: semu112: drop sem_hash_internal: '||SQLERRM);
  end;

  begin
    stmt := 'revoke execute on mdsys.ordmd_sem_libs from public'; execute immediate stmt;
    exception when others then 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
        'SEM_INSTALL: semu112: revoke ordmd_sem_libs: '||SQLERRM);
  end;

end;
/

--/////////// ALTER tables to add/change columns and constraints for the new release \\\\\\\\\\--

-- Need to backup and truncate existing RDFCTX Indexes before we can update RDF_LINK$ for Named Graph --
prompt backing up existing SemContext indexes ...

declare
  rdfctx_exists    number;
  rdfctx_ids       SYS.ODCINumberList;
  idx_names        SYS.ODCIVarchar2List;
  idx_owners       SYS.ODCIVarchar2List;
  stmt             varchar2(1000);
  tblspc_clause    varchar2(2000);
  tblspc_name      varchar2(100);
  backup_failed    boolean := false;
begin
  -- see if rdfctx indexes may be present in DB
  SELECT count(*) INTO rdfctx_exists FROM ALL_TABLES
  WHERE table_name='RDFCTX_INDEX_POLICIES$' AND owner='MDSYS';

  if (rdfctx_exists > 0) then

    -- get the rdfctx model ids
    -- skip virtual models
    execute immediate 
      'select distinct link_part_id
       from mdsys.rdfctx_index_policies$
       where link_part_id > 0'
    bulk collect into rdfctx_ids;

    -- if we have some indexes, back them up
    if (rdfctx_ids.count > 0) then

      -- get the tablespace for the network
      tblspc_clause := '';
      begin
	execute immediate '
	  select tablespace_name
	  from   all_tables
	  where  owner=''MDSYS'' and table_name=''RDF_VALUE$'''
	into tblspc_name;

	if (tblspc_name is not null) then
	  tblspc_clause := ' tablespace ' || 
	    sys.dbms_assert.enquote_name(tblspc_name, false);
	end if;

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: tablespace for rdf_link_png$ tables: '||SQLERRM);
      end;

      -- create table to hold existing index data --
      begin
        execute immediate 
	  'CREATE TABLE mdsys.rdf_link_png$ (
	     p_value_id            NUMBER NOT NULL
	   , start_node_id         NUMBER
	   , canon_end_node_id     NUMBER
	   , end_node_id           NUMBER
	   , model_id              NUMBER
	   , cost                  NUMBER
	   , ctxt1                 NUMBER
	   , ctxt2                 VARCHAR2(4000)
	   , distance              NUMBER
	   , explain               VARCHAR2(4000)
	   , path                  VARCHAR2(4000)
	   ) 
           PARTITION BY LIST (model_id) (PARTITION MODEL_0 VALUES (0)) ' ||
	   tblspc_clause || 
	   ' COMPRESS';

	exception when others then
          if (sqlcode = -955) then
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: semu112: not creating rdf_link_png$ ... it already exists');
          else
	    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	      'SEM_INSTALL: semu112: create rdf_link_png$: '||SQLERRM);
          end if;
      end;

      -- pre-create partitions in rdf_link_png$ --
      begin
        for i in rdfctx_ids.first .. rdfctx_ids.last 
        loop
          begin

            execute immediate
              'ALTER TABLE  mdsys.rdf_link_png$ 
               ADD PARTITION MODEL_' || to_char(rdfctx_ids(i),'TM9') || ' 
               values(' || to_char(rdfctx_ids(i),'TM9') || ')';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: add rdf_link_png$ partition: '||SQLERRM);
          end;
        end loop;

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: loop through add rdf_link_png$ partitions: '||SQLERRM);
      end;

      -- insert existing index data into rdf_link_png$ --
      begin
        execute immediate
          'insert /*+ APPEND PARALLEL */ into mdsys.rdf_link_png$
             (p_value_id, start_node_id, canon_end_node_id, model_id, 
              cost, ctxt1, ctxt2, distance, explain, path)
           select /*+ PARALLEL */
              p_value_id, start_node_id, canon_end_node_id, model_id, 
              cost, ctxt1, ctxt2, distance, explain, path
           from mdsys.rdf_link$
           where model_id in (select * from table(cast(:1 as sys.ODCINumberList)))'
        using rdfctx_ids; 

        commit; 

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: insert into rdf_link_png$ '||SQLERRM);
          backup_failed := true;
      end;

      -- mark existing indexes as invalid
      begin
	execute immediate 
	  'select index_owner, index_name
	   from mdsys.rdfctx_index_policies$'
	bulk collect into idx_owners, idx_names;

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: get index names and owners '||SQLERRM);
      end;

      begin
        for i in idx_owners.first .. idx_owners.last
        loop
          begin
            execute immediate
              'ALTER INDEX ' || sys.dbms_assert.enquote_name(idx_owners(i),false) || 
                '.' || sys.dbms_assert.enquote_name(idx_names(i),false) ||
              ' UNUSABLE';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: alter index '||idx_owners(i)||
                  '.'||idx_names(i)||': '||SQLERRM);
          end;
        end loop; 

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: loop through index names and owners '||SQLERRM);
      end;

      -- truncate link$ partitions for old indexes
      if (not backup_failed) then
	begin
	  for i in rdfctx_ids.first .. rdfctx_ids.last
	  loop
            begin
	      execute immediate
		'ALTER TABLE MDSYS.RDF_LINK$
		 TRUNCATE PARTITION (MODEL_'||to_char(rdfctx_ids(i),'TM9')||')';
	      commit;

	      exception when others then
		SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		  'SEM_INSTALL: semu112: truncate partition model_'||rdfctx_ids(i)||
		    ': '||SQLERRM);
            end;
	  end loop;
	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: error truncating index partitions: '||SQLERRM);
	end;
      else
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu112: not truncating index partitions ... backup failed');
      end if;

    end if;

  end if;
end;
/

prompt ... finished backing up SemContext indexes

------------------------- Update RDF_LINK$ for Named Graph -----------------------
-- First add new column to RDF_LINK$ 
prompt adding new columns to semantic network tables ...

declare
  network_exists number;
  stmt           varchar2(1000);
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
    -- add g_id --
    begin
      stmt := 'alter table mdsys.rdf_link$ add (g_id number default null)';
      execute immediate stmt;
      exception when others then 
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: add g_id to link$: '||SQLERRM);
    end;

    -- drop link_id --
    begin
      stmt := 'alter table mdsys.rdf_link$ drop (link_id)';
      execute immediate stmt;
      exception when others then 
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: drop link_id from link$: '||SQLERRM);
    end;

    -- add new link_id --
    begin
      stmt := 
	'alter table rdf_link$ add
	  (link_id as (
	    to_char(model_id,         ''FMXXXXXXXXXXXXXXXX'') ||
	    (CASE when g_id IS NOT NULL then
		       ''g''|| to_char(g_id,  ''FMXXXXXXXXXXXXXXXX'')
		  else NULL
	     END)                                             ||''_''||
	    to_char(start_node_id,    ''FMXXXXXXXXXXXXXXXX'') ||''_''||
	    to_char(p_value_id,       ''FMXXXXXXXXXXXXXXXX'') ||''_''||
	    to_char(canon_end_node_id,''FMXXXXXXXXXXXXXXXX'')))
	';
      execute immediate stmt;
      exception when others then 
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: add link_id to link$: '||SQLERRM);
    end;
  end if;
end;
/

prompt finished adding new columns to semantic network tables ...

-- Next modify unique index
prompt creating new indexes ...

declare
  network_exists number;
  numIdxCols     pls_integer := 0;
  optCol         varchar2(30);
  optComp        varchar2(30);
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
    -- See if existing index is 4 or 5 columns. 
    -- If 5 columns, then it has been modified for optional components
    begin
      execute immediate '
	select count(*)
	from all_ind_columns
	where index_name = ''RDF_LNK_PVIDCENSNMID_IDX''
	  and index_owner = ''MDSYS'''
      into numIdxCols;

      exception when others then 
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: get num idx columns$: '||SQLERRM);
    end;

    -- if only 4 columns then create plain PCSGM unique index
    if (numIdxCols = 4) then
      begin
	execute immediate '
	  create unique index mdsys.rdf_lnk_pcsgm_idx 
	  on mdsys.rdf_link$ (
	    p_value_id, canon_end_node_id, start_node_id, g_id, model_id)
	  compress 2 nologging local parallel';
	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: create 4-column index: '||SQLERRM);
      end;

    -- if 5 columns then create modified g_id based index 
    elsif (numIdxCols = 5) then
      begin
	-- see what modified index we have
	execute immediate '
	  select attribute
	  from mdsys.rdf_parameter
	  where value = ''ENABLED''
	    and attribute in (''RDFCTX'',''RDFOLS'',''RDFOWM'')'
	into optComp;

	if (optComp = 'RDFOLS') then
	  optCol := ', CTXT1';
	elsif (optComp = 'RDFOWM') then
	  optCol := ', SUBSTR(CTXT2,1,600)';
        else 
          -- RDFCTX uses GID now, not CTXT2
          optCol := '';
	end if;

	-- create new PCSGM index --
	execute immediate '
	  create unique index mdsys.rdf_lnk_pcsgm_idx 
	  on mdsys.rdf_link$ (
	    p_value_id, canon_end_node_id, start_node_id, g_id, model_id'||optCol||')
	  compress 2 nologging local parallel';

	exception when others then 
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: create 5-column index: '||SQLERRM);
      end;
    -- unique index is screwed up
    else
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	'SEM_INSTALL: semu112: Unexpected number of columns in RDF_LNK_PVIDCENSNMID_IDX index');
    end if;

    -- now drop old unique index --
    begin
      execute immediate '
	drop index mdsys.rdf_lnk_pvidcensnmid_idx';
      exception when others then
	SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	  'SEM_INSTALL: semu112: drop old unique index: '||SQLERRM);
    end;

  end if;

end;
/

prompt ... finished creating new indexes

--/////////////////////// Modify static tables for the new release \\\\\\\\\\\\\\\\\\\\\\--


--//////////// UPDATE existing columns as needed to reflect new algorithms, etc. \\\\\\\\--

    
--////////////////////// Create new types and tables for the release \\\\\\\\\\\\\\\\\\\\--

prompt creating new tables and views ...

-- Create new SEM_PRIV$ view
declare
  network_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 RDF_PRIV$ view if absent
    begin
      EXECUTE IMMEDIATE '
        CREATE OR REPLACE VIEW MDSYS.rdf_priv$ AS
        select model_id dummy_column 
          from rdf_link$ partition (model_0) 
         where 1=0
      ';
      EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.rdf_priv$ TO PUBLIC';
      EXCEPTION WHEN OTHERS THEN 
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rdf_priv$: '||SQLERRM);
    end;

  end if;
end;
/

-- create new view for datatype indexing support 
-- only create view if an rdf network exists
declare
  network_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 

    begin

      execute immediate
       'CREATE OR REPLACE VIEW RDF_DTYPE_INDEX_INFO (
	  DATATYPE, INDEX_NAME, STATUS, TABLESPACE_NAME)
	AS
	  SELECT idx_map.dtype DATATYPE, a.index_name INDEX_NAME, a.status STATUS, a.tablespace_name TABLESPACE_NAME
	  FROM all_indexes a,
	  (
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#decimal'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#double'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#float'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#integer'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#int'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#nonPositiveInteger'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#negativeInteger'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#long'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#short'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#byte'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#nonNegativeInteger'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#unsignedLong'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#unsignedInt'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#unsignedShort'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#unsignedByte'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$NUMERIC_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#positiveInteger'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$BOOL_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#boolean'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$STR_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#string'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$DATE_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#date'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$TIME_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#time'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$DATETIME_IDX'' index_name, ''http://www.w3.org/2001/XMLSchema#dateTime'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$GEO_IDX'' index_name, ''http://xmlns.oracle.com/rdf/geo/WKTLiteral'' dtype from dual)
	    UNION ALL
	   (select ''RDF_V$TEXT_IDX'' index_name, ''http://xmlns.oracle.com/rdf/text'' dtype from dual)
	  ) idx_map
	  WHERE owner=''MDSYS'' AND table_name=''RDF_VALUE$'' AND
	        a.index_name = idx_map.index_name';

      execute immediate 'CREATE OR REPLACE VIEW SEM_DTYPE_INDEX_INFO AS SELECT * FROM MDSYS.RDF_DTYPE_INDEX_INFO';

      execute immediate 'GRANT SELECT ON SEM_DTYPE_INDEX_INFO     TO PUBLIC';
      execute immediate 'GRANT SELECT ON RDF_DTYPE_INDEX_INFO     TO PUBLIC';
  
      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: sem_dtype_index_info: '||SQLERRM);
    end;

  end if;
end;
/

-- create stats tables and views
-- only create view if an rdf network exists
declare
  network_exists NUMBER;
  tblspc_name VARCHAR2(30);
  tblspc_clause VARCHAR2(300);
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 

    -- get the tablespace 
    tblspc_clause := '';
    begin
      execute immediate '
        select tablespace_name
        from   all_tables
        where  owner=''MDSYS'' and table_name=''RDF_VALUE$'''
      into tblspc_name;

      if (tblspc_name is not null) then
        tblspc_clause := ' tablespace ' || 
          sys.dbms_assert.enquote_name(tblspc_name, false);
      end if;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: tablespace for stats tables: '||SQLERRM);
    end;

    begin
      execute immediate '
      CREATE TABLE rdf_pred_stats$ (
	  value_id              NUMBER 
	, instances             NUMBER
	, start_node_fanout     NUMBER
	, end_node_fanout       NUMBER
	, model_id              NUMBER
	, g_id                  NUMBER
	)
	PARTITION BY LIST (model_id) (PARTITION MODEL_0 VALUES (0)) ' ||
	tblspc_clause ||
        ' COMPRESS NOLOGGING';

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: tablespace rdf_pred_stats$: '||SQLERRM);
    end;

    begin
      -- add local partition index
      execute immediate '
      ALTER TABLE    rdf_pred_stats$
      ADD CONSTRAINT rdf_pred_stats$pk PRIMARY KEY(model_id,value_id)
      USING INDEX (
        CREATE UNIQUE INDEX rdf_pred_stats$localpk 
        ON                  rdf_pred_stats$(model_id,value_id) 
        COMPRESS NOLOGGING LOCAL
      )' ;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rdf_pred_stats$pk: '||SQLERRM);
    end;

    begin
      execute immediate '
      CREATE TABLE rdf_term_stats$ (
	  value_id              NUMBER 
	, start_node_instances  NUMBER
	, end_node_instances    NUMBER
	, model_id              NUMBER
	, g_id                  NUMBER
	)
	PARTITION BY LIST (model_id) (PARTITION MODEL_0 VALUES (0)) ' ||
	tblspc_clause ||
        ' COMPRESS NOLOGGING';

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rdf_term_stats$: '||SQLERRM);
    end;

    begin
      -- add local partition index
      execute immediate '
      ALTER TABLE    rdf_term_stats$
      ADD CONSTRAINT rdf_term_stats$pk PRIMARY KEY(model_id,value_id)
      USING INDEX (
        CREATE UNIQUE INDEX rdf_term_stats$localpk 
        ON                  rdf_term_stats$(model_id,value_id) 
        COMPRESS NOLOGGING LOCAL
      )' ;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rdf_term_stats$pk: '||SQLERRM);
    end;

  end if;
end;
/

-- move rdf_network_index_internal$ from sysaux to rdf tablepsce
declare
  idx_tab_exists NUMBER;
  tblspc_name VARCHAR2(30);
  tblspc_clause VARCHAR2(300);
begin

  -- see if index table is present in DB
  SELECT count(*) INTO idx_tab_exists FROM ALL_TABLES
  WHERE table_name='RDF_NETWORK_INDEX_INTERNAL$' AND owner='MDSYS';

  if (idx_tab_exists > 0) then

    -- get the tablespace 
    tblspc_clause := '';
    begin
      execute immediate '
        select tablespace_name
        from   all_tables
        where  owner=''MDSYS'' and table_name=''RDF_VALUE$'''
      into tblspc_name;

      if (tblspc_name is not null) then
        tblspc_clause := ' tablespace ' || 
          sys.dbms_assert.enquote_name(tblspc_name, false);
      end if;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: tablespace for rdf_network_index_internal$: '||SQLERRM);
    end;

    -- move the table
    begin
      execute immediate ' 
        alter table mdsys.rdf_network_index_internal$
        move ' || tblspc_clause;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: move rdf_network_index_internal$: '||SQLERRM);
    end;

    -- move the pkey and uniq indexes on IOT metadata tables
    begin
      execute immediate ' 
        alter table mdsys.sem_indextype_metadata$
        move ' || tblspc_clause;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: move mdsys.sem_indextype_metadata$: '||SQLERRM);
    end;

    begin
      execute immediate ' 
        alter index mdsys.SEM_INDEXTYPE_METADATA_UBT
        rebuild ' || tblspc_clause;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rebuild index mdsys.SEM_INDEXTYPE_METADATA_UBT: '||SQLERRM);
    end;

    begin
      execute immediate ' 
        alter table mdsys.sem_sim_indextype_metadata$
        move ' || tblspc_clause;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: move mdsys.sem_sim_indextype_metadata$: '||SQLERRM);
    end;

    begin
      execute immediate ' 
        alter index mdsys.SEM_SIM_INDEXTYPE_METADATA_UBT
        rebuild ' || tblspc_clause;

      exception when others then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'SEM_INSTALL: semu112: rebuild index mdsys.SEM_SIM_INDEXTYPE_METADATA_UBT: '||SQLERRM);
    end;
  end if;

end;
/

prompt ... finished creating new tables and views


prompt creating new types ...

----------------- SEM_GRAPHS type ----------------
begin
  execute immediate
    'create or replace type RDF_Graphs as table of varchar2(4000)';
  exception when others then
    SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
       'SEM_INSTALL: semu112: rdf_graphs: '||SQLERRM);
end;
/

----------------- RDFCTX Types -------------------
-- Type creation is conditional so that if types already
-- exist we will not try to create them.
-- Note: the types below do not exist in 11.2.0.1.0 but
-- do exist in 11.2.0.2.0, so they may already be present
-- 1) DOUBLENUMLIST
-- 2) FN_EXTARR
-- 3) FN_XMLPREF
-- 4) PRLLIDX_CREATE
declare
  cnt          number;
  mdata_exists 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;

  -- if rdfctx is installed then keep going
  if (cnt > 0) then
    
    -- 11.2.0.1.0 to 11.2.0.2.0 migration 
    -- create each new type that does not exist
    -- doublenumlist
    cnt := 0;
    begin
      execute immediate
	'select count(*) 
	 from all_objects
	 where owner = ''MDSYS'' 
	   and object_name = ''DOUBLENUMLIST'''
      into cnt;
      exception when others then null;
    end;

    if (cnt = 0) then
      begin
        execute immediate 
          'create or replace type doublenumlist as table of sys.odcinumberlist';
        exception when others then
          if (sqlcode = -2303) then null; else raise; end if;
      end;
    end if;

    -- fn_extarr
    cnt := 0;
    begin
      execute immediate
	'select count(*) 
	 from all_objects
	 where owner = ''MDSYS'' 
	   and object_name = ''FN_EXTARR'''
      into cnt;
      exception when others then null;
    end;

    if (cnt = 0) then
      begin
        execute immediate 
          'create or replace type fn_extarr is varray(5) of mdsys.rdfctx_extractor';
        exception when others then
          if (sqlcode = -2303) then null; else raise; end if;
      end;
    end if;

    -- fn_xmlpref
    cnt := 0;
    begin
      execute immediate
	'select count(*) 
	 from all_objects
	 where owner = ''MDSYS'' 
	   and object_name = ''FN_XMLPREF'''
      into cnt;
      exception when others then null;
    end;

    if (cnt = 0) then
      begin
        execute immediate 
          'create or replace type fn_xmlpref is varray(5) of sys.xmltype';
        exception when others then
          if (sqlcode = -2303) then null; else raise; end if;
      end;
    end if;

    -- prllidx_create
    cnt := 0;
    begin
      execute immediate
	'select count(*) 
	 from all_objects
	 where owner = ''MDSYS'' 
	   and object_name = ''PRLLIDX_CREATE'''
      into cnt;
      exception when others then null;
    end;

    if (cnt > 0) then
      begin
        execute immediate 
	 'create or replace type prllidx_create authid current_user as object (
	    c1 number,
	    static function index_merge( 
		     idxschema IN varchar2, 
		     idxname   IN varchar2, 
		     moderr    IN sys.odcinumberlist, 
		     c1        IN SYS_REFCURSOR) return sys.odcinumberlist
	       parallel_enable (partition c1 by any),
	    static function index_load(
		  filedir   varchar2, 
		  filterpl  varchar2, 
		  basepol   sys.ODCIVarchar2List,
		  ctxpref   fn_xmlpref, 
		  extarr    fn_extarr,   
		  modarr    sys.ODCINumberList, 
		  polrids   sys.ODCIvarchar2List, 
                  loadMethod varchar2,
		  cur       SYS_REFCURSOR) return  doublenumlist
	      parallel_enable (partition cur by any) pipelined
	  )';

        execute immediate 
          'grant execute on prllidx_create to public';

        exception when others then
          if (sqlcode = -2303) then null; else raise; end if;
      end;
    end if;


    -- ll.2.0.2.0 to 11.2.0.3.0 RDFCTX migration --
    begin 

      begin
        -- sem_rdfctx has changed 
        execute immediate '
	  create or replace package sem_rdfctx authid current_user as

	    procedure create_policy (
			policy_name        VARCHAR2, 
			extractor          mdsys.rdfctx_extractor, 
			preferences        sys.XMLType default null); 

	    procedure create_policy (
			policy_name        VARCHAR2, 
			base_policy        VARCHAR2); 

	    procedure create_policy (
			policy_name        VARCHAR2, 
			base_policy        VARCHAR2, 
			user_models        mdsys.rdf_models, 
			rulebases          mdsys.rdf_rulebases default null); 

	    procedure create_policy (
			policy_name        VARCHAR2, 
			base_policy        VARCHAR2, 
			user_models        mdsys.rdf_models, 
			user_entailments   mdsys.rdf_models,
			rulebases          mdsys.rdf_rulebases default null);

	    procedure drop_policy (
			policy_name        VARCHAR2); 

	    procedure set_default_policy (
			index_name         VARCHAR2, 
			policy_name        VARCHAR2); 

	    procedure add_dependent_policy (
			index_name           VARCHAR2,
			policy_name          VARCHAR2,
			partition_name       VARCHAR2 default NULL);

	    function extract_rdfxml (
			doc                CLOB,
			ext_type           mdsys.rdfctx_extractor) return CLOB;

	    procedure maintain_triples (
			index_name         VARCHAR2, 
			where_clause       VARCHAR2,
			rdfxml_content     sys.XMLType,
			policy_name        VARCHAR2 default NULL, 
			action             VARCHAR2 default ''ADD''); 

	    procedure set_extractor_param (
			param_key         VARCHAR2, 
			param_value       VARCHAR2, 
			param_desc        VARCHAR2); 

	  end sem_rdfctx;'; 

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: replace package sem_rdfctx: '||SQLERRM);
      end;

      begin
        execute immediate '
          create or replace public synonym sem_rdfctx for mdsys.sem_rdfctx';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: replace synonym sem_rdfctx: '||SQLERRM);
      end;

      begin
        execute immediate ' 
          grant execute on sem_rdfctx to public'; 

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: grant sem_rdfctx: '||SQLERRM);
      end;

      -- alter prllidx_create type
      begin
        execute immediate
         'alter type prllidx_create
            drop static function index_merge(
                  idxschema IN varchar2, 
                  idxname   IN varchar2, 
                  moderr    IN sys.odcinumberlist, 
                  c1        IN SYS_REFCURSOR) return sys.odcinumberlist
              parallel_enable (partition c1 by any)';

 	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: drop index_merge: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type prllidx_create
            add static function index_merge(
                  idxschema IN varchar2, 
                  idxname   IN varchar2, 
                  moderr    IN sys.odcinumberlist, 
                  flags     IN number,
                  c1        IN SYS_REFCURSOR) return sys.odcinumberlist
              parallel_enable (partition c1 by any)';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: add index_merge: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type prllidx_create
            drop static function index_load(
		  filedir   varchar2, 
		  filterpl  varchar2, 
		  basepol   sys.ODCIVarchar2List,
		  ctxpref   fn_xmlpref, 
		  extarr    fn_extarr,   
		  modarr    sys.ODCINumberList, 
		  polrids   sys.ODCIvarchar2List, 
                  loadMethod varchar2,
		  cur       SYS_REFCURSOR) return  doublenumlist
	      parallel_enable (partition cur by any) pipelined';

 	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: drop index_load: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type prllidx_create
            add static function index_load(
		  idxschema IN varchar2, 
		  idxname   IN varchar2,
		  filedir   varchar2, 
		  filterpl  varchar2, 
		  basepol   sys.ODCIVarchar2List,
		  extrParams sys.ODCIVarchar2List,
		  ctxpref   fn_xmlpref, 
		  extarr    fn_extarr,   
		  modarr    sys.ODCINumberList, 
		  polrids   sys.ODCIvarchar2List, 
		  loadMethod varchar2,
		  stagtab_name   varchar2,
		  docUri2Rid_name varchar2,
		  docVid2Rid_name varchar2,
                  is_batchpol     sys.ODCINumberList,
                  flags           number,
		  cur       SYS_REFCURSOR) return  doublenumlist
	      parallel_enable (partition cur by any) pipelined';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: add index_load: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type prllidx_create
            add static function build_index_partition( 
		  idxinfo sys.ODCIIndexInfo, 
		  l_modarr sys.ODCINumberList,
		  filedir VARCHAR2, 
		  filterpl varchar2, 
		  l_basepol sys.ODCIVarchar2List,
		  l_extrParams sys.ODCIVarchar2List,
		  l_ctxpref fn_xmlpref, 
		  t_extarr fn_extarr, 
		  l_polrids sys.ODCIVarchar2List,
		  loadMethod   varchar2,
		  stagtab_name varchar2,
		  docUri2Rid_name varchar2,
		  docVid2Rid_name varchar2,
                  xinfotab_name   varchar2,
                  flags           number) return number';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: add build_index_partition: '||SQLERRM);

      end;

      begin
        execute immediate
         'alter type prllidx_create
            add static function build_index_batch_extr(
                  idxinfo        sys.ODCIIndexInfo,
                  l_modarr       sys.ODCINumberList,
                  filedir        varchar2,
                  filterpl       varchar2,
                  l_basepol      sys.ODCIVarchar2List,
                  l_batchpol     sys.ODCINumberList,
                  l_extrParams   sys.ODCIVarchar2List,
                  l_ctxpref      fn_xmlpref,
                  t_extarr       fn_extarr,
                  l_polrids      sys.ODCIVarchar2List,
                  l_errcnt in out sys.ODCINumberList,
                  l_docUriPfx     varchar2,
                  l_docUriShortPfx  varchar2,
                  loadMethod     varchar2,
                  stagtab_name    varchar2,
                  l_stagtab_suffixes  sys.ODCIvarchar2List,
                  docUri2Rid_name  varchar2,
                  docVid2Rid_name  varchar2,
                  xinfotab_name   varchar2,
                  l_sel_basetab  varchar2, 
                  flags          number) return number';

        exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter prllidx_create: add build_index_batch_extr: '||SQLERRM);

      end; 

      -- add new methods to rdfctx_extractor
      begin
	execute immediate
	 'alter type rdfctx_extractor 
	  add member function getContext(attribute VARCHAR2) return VARCHAR2
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter rdfctx_extractor add getContext: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type rdfctx_extractor
          add member function extractRdf(document CLOB,
                                         docId    VARCHAR2,
                                         params   VARCHAR2,
                                         options  VARCHAR2 default NULL) return CLOB
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter rdfctx_extractor add extractRdf: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type rdfctx_extractor
          add member function batchExtractRdf(docCursor        SYS_REFCURSOR,
                                              extracted_info_table  VARCHAR2,
                                              params                VARCHAR2,
                                              partition_name        VARCHAR2 default NULL,
                                              docId                 VARCHAR2 default NULL,
                                              preferences           SYS.XMLType default NULL,
                                              options               VARCHAR2 default NULL) return CLOB
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter rdfctx_extractor add batchExtractRdf: '||SQLERRM);
      end;

      -- should be done by cascading, but for some reason this doesn't work ..
      -- alter ctx_entity_extractor directly as a workaround --
      begin
        execute immediate
         'alter type ctx_entity_extractor
          add overriding member function extractRdf(document CLOB,
                                                    docId    VARCHAR2,
                                                    params   VARCHAR2,
                                                    options  VARCHAR2 default NULL) return CLOB
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter ctx_entity_extractor add extractRdf: '||SQLERRM);
      end;

      -- alter SemContextIndexMethods --
      begin
        execute immediate
         'alter type SemContextIndexMethods
          add static function ODCIIndexMergePartition (
           idxinfo     sys.ODCIIndexInfo,
           part1_name  sys.ODCIPartInfo,
           part2_name  sys.ODCIPartInfo,
           idxparms    VARCHAR2,
           idxenv      SYS.ODCIEnv) return NUMBER
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter SemContextIndexMethods add ODCIIndexMergePartition: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type SemContextIndexMethods
          add static function ODCIIndexSplitPartition (
           idxinfo     sys.ODCIIndexInfo,
           part1_name  sys.ODCIPartInfo,
           part2_name  sys.ODCIPartInfo,
           idxparms    VARCHAR2,
           idxenv      SYS.ODCIEnv) return NUMBER
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter SemContextIndexMethods add ODCIIndexSplitPartition: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type SemContextIndexMethods
          add static function ODCIIndexExchangePartition (
           pa_idxinfo  sys.ODCIIndexInfo,
           idxinfo     sys.ODCIIndexInfo,
           idxenv      SYS.ODCIEnv) return NUMBER
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter SemContextIndexMethods add ODCIIndexExchangePartition: '||SQLERRM);
      end;

      begin
        execute immediate
         'alter type SemContextIndexMethods
          add static function ODCIIndexStart (
           ictx        IN OUT SemContextIndexMethods,
           idxinfo     sys.ODCIIndexInfo,
           opi         sys.ODCIPredInfo,
           oqi         sys.ODCIQueryInfo,
           strt        NUMBER,
           stop        NUMBER,
           sparql      VARCHAR2,
           policy      VARCHAR2,
           idx_status  INTEGER,
           aliases     mdsys.RDF_ALIASES,
           idxenv      sys.ODCIEnv) return NUMBER
          cascade';

	exception when others then
	  SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	    'SEM_INSTALL: semu112: alter SemContextIndexMethods add ODCIIndexStart: '||SQLERRM);
      end;

      -- alter metadata tables --
      begin
        -- make sure they're there first --
        execute immediate
         'select count(*) 
          from all_tables
          where owner = ''MDSYS'' and
                table_name = ''RDFCTX_INDEX_POLICIES$'''
        into mdata_exists;

        if (mdata_exists > 0) then

          -- add new columns --
	  begin
	    execute immediate
	     'alter table mdsys.rdfctx_index_policies$
	       add (index_partition varchar2(32) default ''MY_PARTITION'')
	       add (extr_params varchar2(100))';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: alter rdfctx_index_policies$ add columns: '||SQLERRM);
          end;

          -- initialize new column values --
	  begin
	    execute immediate
	     'update mdsys.rdfctx_index_policies$
	       set index_partition = index_name';

            commit;

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: update rdfctx_index_policies$: '||SQLERRM);
          end;

          -- update with new constraint --
	  begin
	    execute immediate
	     'alter table mdsys.rdfctx_index_policies$
	      drop constraint rdfctx_ipol_pkey cascade';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: alter rdfctx_index_policies$ drop constraint: '||SQLERRM);
          end;

	  begin
	    execute immediate
	     'alter table mdsys.rdfctx_index_policies$
	      add constraint rdfctx_ipol_pkey primary key (
                     index_owner, index_name, index_partition, policy_rid)';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: alter rdfctx_index_policies$ add constraint: '||SQLERRM);
          end;

          -- recreate metadata view --
	  begin
	    execute immediate
	     'create or replace view mdsys.rdfctx_index_policies
		   (index_owner, index_name, index_partition, policy_name, extr_parameters, is_default, status,
		    rdf_model) as
	      (select cip.index_owner, cip.index_name, 
		      (case when (cip.index_name=cip.index_partition) then null else cip.index_partition end),
		      cp.policy_name, cip.extr_params,
		      decode(bitand(flags,1), 1, ''YES'',''NO''), 
		      decode(bitand(flags,6), 2, ''INPROGRESS'',
					      4, ''FAILED'', ''VALID''), 
		      ''RDFCTX_MOD_''||abs(link_part_id)
	       from mdsys.rdfctx_index_policies$ cip, all_indexes ai, 
		    mdsys.rdfctx_policies$ cp 
	       where ai.owner = cip.index_owner and ai.index_name = cip.index_name
		     and cip.policy_rid = cp.rowid)';

	    exception when others then
	      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
		'SEM_INSTALL: semu112: alter rdfctx_index_policies$ add constraint: '||SQLERRM);
          end;

        end if;

      end;

    end;

  end if;
     
end;
/

prompt ... finished creating new types

--//////// ALTER existing types to add/change attributes and methods for the new release \\\\\\\\--

prompt altering existing types ...

declare
  stmt varchar2(1000);
begin

  -- add get_model to sdo_rdf_triple_s --
  -- drop get_model first for attempt at repeatability --
  begin
    stmt := 'ALTER TYPE sdo_rdf_triple_s
             DROP MEMBER FUNCTION get_model RETURN VARCHAR2 
             DETERMINISTIC CASCADE';
    execute immediate stmt;
    exception when others then 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	 'SEM_INSTALL: semu112: sdo_rdf_triple_s: '||SQLERRM);    
  end;

  -- now add get_model --
  begin
    stmt := 'ALTER TYPE sdo_rdf_triple_s
             ADD MEMBER FUNCTION get_model RETURN VARCHAR2 
             DETERMINISTIC CASCADE'; 
    execute immediate stmt;
    exception when others then 
      SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
	 'SEM_INSTALL: semu112: sdo_rdf_triple_s: '||SQLERRM);
  end;

end;
/

prompt ... finshed altering existing types

--/////////// GRANT any additional privileges required by MDSYS for the new release \\\\\\\\\\\\\--

-- grant execute on Oracle Text APIS 
GRANT CTXAPP TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_CLS TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_THES TO MDSYS;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MDSYS;

prompt ... finished preparing semantic network for named graph support

alter session set current_schema=SYS;