Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      xoqu111.sql - migration script for upgrading olap api component
Rem
Rem    DESCRIPTION
Rem      Upgrade OLAP API from 11.1.0
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       11/02/09 - Confine fixed table statistics to development
Rem    wechen      07/29/09 - Backport wechen_bug-8688621 from
Rem                           st_rdbms_11.2.0.1.0
Rem    dbardwel    08/19/08 - Updates for olap data security for 11.2
Rem    jhartsin    02/21/08 - Creation

execute sys.dbms_registry.upgrading('XOQ');

-- drop ODBO support
DROP PACKAGE DBMS_XSOQ_ODBO;
DROP VIEW olap_oledb_measdims_pvt;
DROP VIEW olap_oledb_listdims_pvt;
DROP VIEW olap_oledb_stdhiers_pvt;
DROP VIEW olap_oledb_stdlevels_pvt;
DROP VIEW olap_oledb_cubedims_pvt;
DROP VIEW olap_oledb_custprops_pvt;
DROP VIEW olap_oledb_level_type_pvt;
DROP VIEW olap_oledb_cardinality_pvt;
DROP VIEW olap_oledb_dflt_mbr_pvt;
DROP VIEW olap_oledb_reg_attrs_pvt;
DROP VIEW olap_oledb_cubes;
DROP VIEW olap_oledb_dimensions;
DROP VIEW olap_oledb_hierarchies;
DROP VIEW olap_oledb_levels;
DROP VIEW olap_oledb_properties;
DROP VIEW olap_oledb_measures;
DROP VIEW olap_oledb_actions;
DROP VIEW olap_oledb_functions;
DROP VIEW olap_oledb_sets;
DROP TYPE Olapi_MDX_Rowset_Impl_t FORCE;
DROP FUNCTION Olapi_MDX_Rowset_Table;
DROP VIEW olap_oledb_mdproperties;
DROP TABLE olap_oledb_mdprops;
DROP TABLE olap_oledb_mdpropvals;
DROP TABLE olap_oledb_keywords;
DROP TABLE olap_oledb_functions_pvt;

-- data security for 11.2
@@awmxsrol.sql
@@awmcrxdb.plb

-- drop snapi types. Depending on where the upgrade comes from,
-- snapi type may or may not be present. Guard the drops with
-- exception handling.
begin
  execute immediate 'DROP TYPE GenExtraStruct FORCE';
  execute immediate 'DROP TYPE GenPKTStruct FORCE';
  execute immediate 'DROP PACKAGE GenSnapiInterface';
  exception when others then null;
end;
/

-- all DROPs from olapimov.sql
DROP PUBLIC SYNONYM GV_Olapi_Session_History FORCE;
DROP PUBLIC SYNONYM V_Olapi_Session_History FORCE;
DROP PUBLIC SYNONYM GV_Olapi_Iface_Object_History FORCE;
DROP PUBLIC SYNONYM V_Olapi_Iface_Object_History FORCE;
DROP PUBLIC SYNONYM GV_Olapi_Iface_Op_History FORCE;
DROP PUBLIC SYNONYM V_Olapi_Iface_Op_History FORCE;
DROP PUBLIC SYNONYM GV_Olapi_Memory_Op_History FORCE;
DROP PUBLIC SYNONYM V_Olapi_Memory_Op_History FORCE;
DROP PUBLIC SYNONYM GV_Ksmhp FORCE;
DROP PUBLIC SYNONYM Olapi_History_Seq FORCE;
DROP PUBLIC SYNONYM Olapi_History FORCE;
DROP PUBLIC SYNONYM Olapi_Session_History FORCE;
DROP PUBLIC SYNONYM Olapi_Iface_Object_History FORCE;
DROP PUBLIC SYNONYM Olapi_Iface_Op_History FORCE;
DROP PUBLIC SYNONYM Olapi_Memory_Op_History FORCE;
DROP PUBLIC SYNONYM Olapi_Memory_Heap_History FORCE;
DROP PUBLIC SYNONYM DBMS_XSOQ FORCE;
DROP PUBLIC SYNONYM DBMS_XSOQ_UTIL FORCE;

DROP VIEW GV_Olapi_Session_History;
DROP VIEW V_Olapi_Session_History;
DROP VIEW GV_Olapi_Iface_Object_History;
DROP VIEW V_Olapi_Iface_Object_History;
DROP VIEW GV_Olapi_Iface_Op_History;
DROP VIEW V_Olapi_Iface_Op_History;
DROP VIEW GV_Olapi_Memory_Op_History;
DROP VIEW V_Olapi_Memory_Op_History;
DROP VIEW GV_Ksmhp;
DROP SEQUENCE Olapi_History_Seq;
DROP TABLE Olapi_History CASCADE CONSTRAINTS;
DROP TABLE Olapi_Session_History CASCADE CONSTRAINTS;
DROP TABLE Olapi_Iface_Object_History CASCADE CONSTRAINTS;
DROP TABLE Olapi_Iface_Op_History CASCADE CONSTRAINTS;
DROP TABLE Olapi_Memory_Op_History CASCADE CONSTRAINTS;
DROP TABLE Olapi_Memory_Heap_History CASCADE CONSTRAINTS;
DROP PROCEDURE OlapiHistoryRetention;
DROP TRIGGER OlapiStartupTrigger;
DROP TRIGGER OlapiShutdownTrigger;
DROP PACKAGE DBMS_XSOQ;
DROP PACKAGE DBMS_XSOQ_UTIL;

-- drop OLAPI_TRACE_USER role, it it exists
declare
  cursor xoqrole is select role from dba_roles where role = 'OLAPI_TRACE_USER';
  xoqrolename varchar2(30);
begin
  if not xoqrole%isopen then
    open xoqrole;
    fetch xoqrole into xoqrolename;
    if xoqrole%found then
      execute immediate 'DROP ROLE OLAPI_TRACE_USER';
    end if;
    close xoqrole;
  end if;
end;
/

execute sys.dbms_registry.upgraded('XOQ');