DROP TYPE SYS.OlapLevelTuples
/
DROP TABLE SYS.OlapTabLevelTuples
/
DROP TYPE SYS.OlapLevelTuple
/
DROP TYPE SYS.OlapLevels
/
DROP TABLE SYS.OlapTabLevels
/
DROP TYPE SYS.OlapLevel
/
CREATE OR REPLACE TYPE SYS.OlapLevel AS OBJECT
    (schema_name    varchar2(30),
     dimension_name  varchar2(30),
     dimension_owner varchar2(30),
     cube_name      varchar2(30),
     level_name varchar2(30),
     selected  NUMBER)
/
CREATE OR REPLACE TYPE SYS.OlapLevels AS TABLE of SYS.OlapLevel
/
CREATE global temporary TABLE SYS.OlapTabLevels
    (schema_name    varchar2(30),
     dimension_name  varchar2(30),
     dimension_owner varchar2(30),
     cube_name      varchar2(30),
     level_name varchar2(30),
     selected  NUMBER) 
on commit preserve rows
/

CREATE OR REPLACE TYPE SYS.OlapLevelTuple AS OBJECT
    (id NUMBER,
     row_count NUMBER,
     pct_of_total NUMBER,
     schema_name varchar2(30),
     cube_name varchar2(30),
     dimension_name varchar2(30),
     dimension_owner varchar2(30),
     level_name varchar2(30),
     selected NUMBER)
/

CREATE OR REPLACE TYPE SYS.OlapLevelTuples AS TABLE OF SYS.OlapLevelTuple
/

CREATE global temporary TABLE SYS.OlapTabLevelTuples
    (id NUMBER,
     row_count NUMBER,
     pct_of_total NUMBER,
     schema_name varchar2(30),
     cube_name varchar2(30),
     dimension_name varchar2(30),
     dimension_owner varchar2(30),
     level_name varchar2(30),
     selected NUMBER)
on commit preserve rows
/

CREATE index OlapTabLevelsID ON SYS.OlapTabLevelTuples
    (id)
/

CREATE index OlapTabLevelsSel ON SYS.OlapTabLevelTuples
    (selected,
     dimension_owner,
     dimension_name,
     level_name)
/

GRANT SELECT,INSERT,UPDATE,DELETE ON SYS.OlapTabLevels TO PUBLIC
/
GRANT SELECT,INSERT,UPDATE,DELETE ON SYS.OlapTabLevelTuples TO PUBLIC
/
GRANT EXECUTE ON SYS.OLAPLEVEL TO PUBLIC
/
GRANT EXECUTE ON SYS.OLAPLEVELS TO PUBLIC
/
GRANT EXECUTE ON SYS.OLAPLEVELTUPLE TO PUBLIC
/
GRANT EXECUTE ON SYS.OLAPLEVELTUPLES TO PUBLIC
/