Rem
Rem $Header: oraolap/src/sql/dbmscbu.sql /st_rdbms_11.2.0/1 2011/05/03 13:53:57 ckearney Exp $
Rem
Rem dbmscbu.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      dbmscbu.sql - DBMS_CUBE_UTIL declarations
Rem
Rem    DESCRIPTION
Rem      Provides interfaces for dbms_cube_util functions
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ckearney    04/21/11 - add get_ext_metadata
Rem    csperry     06/29/09 - add get hierarchy special member method
Rem    smierau     03/23/09 - Created
Rem

drop type dbms_cube_util_ext_md_t;
drop type dbms_cube_util_ext_md_r;

create type dbms_cube_util_ext_md_r
  as object (owner             varchar2(128),
             dimension_name    varchar2(128),
             hierarchy_name    varchar2(128),
             default_member    varchar2(4000),
             depth_count       number,
             depth             number,
             depth_cardinality number)
/

create type dbms_cube_util_ext_md_t
  as table of dbms_cube_util_ext_md_r
/

grant execute on dbms_cube_util_ext_md_r to public;
grant execute on dbms_cube_util_ext_md_t to public;


CREATE OR REPLACE PACKAGE dbms_cube_util AUTHID CURRENT_USER AS

  ---------------------
  --  OVERVIEW
  --
  --  This package is the interface to cube utility functions
  --
  ---------------------
  --  Visibility        
  --   All users
  --

  ---------------------
  --  CONSTANTS

  ---------------------
  --  EXCEPTIONS

  ---------------------
  --  PROCEDURES

  -- Create a report filter
  PROCEDURE create_rpt_filter(p_owner       IN VARCHAR2,
                              p_dimension   IN VARCHAR2,
                              p_rfname      IN VARCHAR2,
                              p_member_list IN VARCHAR2);

  -- Drop a report filter
  PROCEDURE drop_rpt_filter(p_owner       IN VARCHAR2,
                            p_dimension   IN VARCHAR2,
                            p_rfname      IN VARCHAR2);

  -- Drop a branch
  PROCEDURE drop_branch(p_owner       IN VARCHAR2,
                        p_dimension   IN VARCHAR2);

  -- Get HIERARCHY ALL or DEFAULT member for a cube dimension hierarchy
  -- Valid specialMemberTypes: 
  -- HIERARCHY_ALL, DEFAULT_FIRST, DEFAULT_LAST or DEFAULT 
  -- DEFAULT and DEFAULT_LAST are the same.
  -- When a hierarchy name is not specified the dimension is treated
  -- as a LIST dimension for the purpose of getting a DEFAULT member.
  FUNCTION get_dimension_special_member
    (qualifiedHierarchy IN VARCHAR2, -- "OWNER"."DIMENSION"[."HIERARCHY"]
     specialMemberType  IN VARCHAR2 DEFAULT 'DEFAULT')  
    RETURN VARCHAR2;

  -- return extended metadata
  FUNCTION get_ext_metadata(owner          IN VARCHAR2 DEFAULT NULL,
                            dimension_name IN VARCHAR2 DEFAULT NULL)
  return sys.dbms_cube_util_ext_md_t
  pipelined;


END dbms_cube_UTIL; 
/
show errors;

-- Give execute privileges
CREATE OR REPLACE PUBLIC SYNONYM dbms_cube_util FOR sys.dbms_cube_util
/
GRANT EXECUTE ON dbms_cube_util TO PUBLIC
/