-- -- $Header: cwmlite/admin/oneputlp.pls /main/9 2008/07/28 13:25:50 glyon Exp $ -- -- oneputlp.pls -- -- Copyright (c) Oracle Corporation 1900, 2000, 2001. All Rights Reserved. -- -- NAME -- oneputlp.pls - cwm$util -- -- DESCRIPTION -- Private utility methods. -- -- RETURNS -- -- NOTES -- -- -- MODIFIED (MM/DD/YY) -- glyon 06/26/08 - bug 7204558: eliminate references to sys.user$ -- dthompso 02/27/01 - add deterministic parallel_enable to view functio -- dthompso 10/26/00 - Change dimension valid signature -- pramarao 08/24/00 - Added the function spec is_sysdba. -- dallan 08/18/00 - Chnage validation for dimension signature. -- dallan 08/15/00 - Add dim_valid function.. -- dthompso 05/30/00 - Correct Exceptions raised -- dthompso 05/03/00 - Level attributes must be qualified by level names -- dthompso 04/27/00 - Initial Version -- dthompso 01/00/00 - Creation -- create or replace package cwm$util as -- Private internal utility functions -- Compute OLAP dimension validity flag used in views -- -- param dim_id_owner the id of the dimension -- return varchar2 'Y' if dimension valid, 'N' otherwise function dim_valid(dim_id number) return VARCHAR2 deterministic parallel_enable; -- Compute cube validity flag used in views -- -- param cube_id the id of the cube -- return varchar2 'N' if cube valid, 'Y' otherwise function cube_invalid(cube_id number) return VARCHAR2 deterministic parallel_enable; -- Compute if the tables for a dimension are selectable by the -- connected user. Used by all_olap views filter. -- -- param dim_id the id of the dimension -- return varchar2 'Y' if all tables visible, 'N' otherwise function dimension_tables_visible(dim_id number) return VARCHAR2 deterministic parallel_enable; -- Compute if the fact tables for a cube are selectable by the -- connected user. Used by all_olap views filter. -- -- param cube_id the id of the cube -- return varchar2 'Y' if all tables visible, 'N' otherwise function fact_table_visible(cube_id number) return VARCHAR2 deterministic parallel_enable; -- Empty repository contents -- -- param full true means also delete seed data. procedure reset_catalog(full boolean := false); -- Generate new identifier -- -- return number new identifier for repository function get_id return number; -- Return the identity of a table -- -- param owner owner of table -- param table_name name of table -- -- raise table_not_found if table does not exist function get_table_id(owner varchar2 , table_name varchar2) return number; -- Return the identity of a dimension -- -- param owner owner of dimension -- param dimension_name name of dimension -- -- raise dimension_not_found if dimension does not exist function get_dimension_id(owner varchar2 , dimension_name varchar2) return number; -- Return the identity of a foreign key -- -- param owner owner of key -- param foreign_key_name name of key -- -- raise foreign_key_not_found if foreign key does not exist function get_foreign_key_id(owner varchar2 , foreign_key_name varchar2) return number; -- Return the id of the default project -- -- return number the project id function get_project_id return number; -- Return the id of the specified user -- -- param user_name name of the user -- return number internal id of the user -- -- raise user_not_found if user does not exist function get_user_id(user_name varchar2) return number; -- check user has privileges to create / update / delete a cube -- either through owning it or by being an OLAP_DBA -- -- param cube_owner user owning the cube -- param cube_name name of the cube -- -- raise no_access_privileges if no privileges on cube procedure check_cube_privileges(cube_owner varchar2, cube_name varchar2); -- check user has privileges to update dimension metadata -- either through owning it or by being an OLAP_DBA -- -- param dimension_owner user owning the dimension -- param dimension_name name of the dimension -- -- raise no_access_privileges if no privileges on dimension procedure check_dimension_privileges(dimension_owner varchar2 , dimension_name varchar2); -- check user has visibility of a cube. -- -- param owner owner of the cube -- param cube_name name of the cube -- -- raise cube_not_found if no privileges on cube procedure check_cube_visible(owner varchar2 , cube_name varchar2); -- check user has select privilege on a view or table and -- can thus create mappings to the table. User has privileges -- either through owning the table, granted select access to it -- or by having system privileges such as SELECT ANY TABLE. -- -- param table_owner user owning the table -- param table_name name of the table -- -- raise table_not_found if no privileges on table procedure check_table_visible(table_owner varchar2 , table_name varchar2); -- check user has select privilege on a dimension and -- can thus create mappings to the dimension. User has privileges -- either through owning the dimension, granted select access to its -- dimension tablesor by having system privileges such as -- CREATE ANY DIMENSION. -- -- param dimension_owner user owning the dimension -- param dimension_name name of the dimension -- -- raise dimension_not_found if no privileges on dimension procedure check_dimension_visible(dimension_owner varchar2 , dimension_name varchar2); -- check that the table has the named column -- -- param table_owner user owning the table -- param table_name name of the table -- param column_name name of the column -- -- raise table_not_found if no privileges on table -- raise column_not_found if column does not exist procedure check_column_exists(table_owner varchar2 , table_name varchar2 , column_name varchar2); -- check that the dimension has the named level -- -- param dimension_owner user owning the dimension -- param dimension_name name of the dimension -- param level_name name of the level to check for -- -- raise dimension_not_found if no privileges on dimension -- raise level_not_found if level does not exist procedure check_level_exists(dimension_owner varchar2 , dimension_name varchar2 , level_name varchar2); -- get the dimension level id within lite -- -- param dimension_id id of the dimension -- param user_id user owning the dimension -- param level_name name of the level to check for -- -- raise dimension_not_found if no privileges on dimension function get_lite_level_id(dimension_id number , user_id number , level_name varchar2) return number; -- check that the dimension has the named hierarchy -- -- param dimension_owner user owning the dimension -- param dimension_name name of the dimension -- param hierarchy_name name of the hierarchy to check for -- raise dimension_not_found if no privileges on dimension -- raise hierarchy_not_found if hierachy does not exist procedure check_hierarchy_exists(dimension_owner varchar2 , dimension_name varchar2 , hierarchy_name varchar2); -- Does the connected user have the OLAP_DBA role -- -- return boolean if connected user has role 'OLAP_DBA' function is_olap_dba return boolean; -- Does the connected user have the SYSDBA role -- -- return boolean if connected user has role 'SYSDBA' function is_sysdba return boolean; -- Diagnostic procedure to dump row count of internal tables procedure dump_table_info; end; /