-- -- $Header: cwmlite/admin/oneputl.pls /main/16 2008/07/28 13:25:50 glyon Exp $ -- -- oneputl.pls -- -- Copyright (c) Oracle Corporation 2000, 2001. All Rights Reserved. -- -- NAME -- oneputl.pls - cwm$utility -- -- DESCRIPTION -- Public utility methods. -- -- RETURNS -- -- NOTES -- -- -- MODIFIED (MM/DD/YY) -- glyon 06/26/08 - bug 7204558: eliminate references to sys.user$ -- cdalessi 10/21/03 - change refs to cwmlite -- dthompso 04/11/01 - Add is_olap_dba(varchar2) function -- dthompso 10/26/00 - update dimension validation constants -- -- dallan 08/18/00 - Add validate dimension routine for querying state. -- dallan 08/16/00 - Add invalid constraint error code. -- dallan 07/31/00 - Add is_olap_dba function. -- dthompso 07/07/00 - Pass export lob back as parameter -- dthompso 06/22/00 - get_object_in_error extra parameter. -- dthompso 06/01/00 - add 'INSTANCE' as object type. -- dallan 05/18/00 - Change exception name to number.. -- dthompso 05/01/00 - Added get_version function -- dthompso 05/01/00 - Add cube validation error methods -- dthompso 04/27/00 - Initial Version -- dthompso 01/00/00 - Creation -- create or replace package cwm$utility as -- identifier for entity type values CUBE_TYPE constant varchar2(30) := 'CUBE'; MEASURE_TYPE constant varchar2(30) := 'MEASURE'; DIMENSION_TYPE constant varchar2(30) := 'DIMENSION'; HIERARCHY_TYPE constant varchar2(30) := 'HIERARCHY'; LEVEL_TYPE constant varchar2(30) := 'LEVEL'; DIMENSION_ATTRIBUTE_TYPE constant varchar2(30) := 'DIMENSION ATTRIBUTE'; LEVEL_ATTRIBUTE_TYPE constant varchar2(30) := 'LEVEL ATTRIBUTE'; TABLE_TYPE constant varchar2(30) := 'TABLE'; COLUMN_TYPE constant varchar2(30) := 'COLUMN'; FOREIGN_KEY_TYPE constant varchar2(30) := 'FOREIGN KEY'; FUNCTION_TYPE constant varchar2(30) := 'FUNCTION'; PARAMETER_TYPE constant varchar2(30) := 'PARAMETER'; CATALOG_TYPE constant varchar2(30) := 'CATALOG'; DESCRIPTOR_TYPE constant varchar2(30) := 'DESCRIPTOR'; INSTANCE_TYPE CONSTANT VARCHAR2(30) := 'INSTANCE'; CUBE_NO_DIMENSIONS constant INT := 1; -- cube must have at least 1 measure CUBE_NO_MEASURES constant INT := 2; -- cube contains an invalid dimension CUBE_INVALID_DIMENSION constant INT := 3; -- cube contains a measure that is not mapped to a valid column CUBE_INVALID_MEASURE_MAPPING constant INT := 4; -- fact and dimension tables must be mapped by a valid foreign key CUBE_INVALID_FACT_DIM_MAPPING constant INT := 5; -- cube has not been mapped to a fact table CUBE_NO_FACT constant INT := 6; -- the cube is mapped to a fact table that has been deleted CUBE_INVALID_FACT constant INT := 7; -- the cube is mapped to a fact table using an invalid constraint(foreign key) CUBE_INVALID_CONSTRAINT constant INT := 8; -- dimension must have a name DIMENSION_NO_NAME constant INT := 9; -- invalid dimension DIMENSION_INVALID constant INT := 10; -- dimension must have at least 1 level DIMENSION_NEEDS_LEVELS constant INT := 11; -- Return the internal version identifier string -- return varchar2 the version identifier function get_version return varchar2; -- get the identifier for the named function -- param function_name name of the function -- return number the id of the function -- -- raise function_not_found if function does not exist function get_function_id(function_name varchar2) return number; -- validate dimension, and initialize details for client to investigate -- param dim_owner owner of the dimension -- param dim name of the dimension -- param output_on summarize results on output stream -- -- raise dimension_not_found if dimension does not exist procedure validate_dimension(dim_owner in varchar2 ,dim_name in varchar2 ,output_on in boolean := false); -- validate cube, and initialize details for client to investigate -- param cube_owner owner of the cube -- param cube_name name of the cube -- param output_on summarize results on output stream -- -- raise cube_not_found if cube does not exist procedure validate_cube(cube_owner in varchar2 ,cube_name in varchar2 ,output_on in boolean := false); -- get first record in error -- param object_owner owner of the object -- param object_name name of the object -- param exception_number number of the error -- return varchar2 'Y' if record returned, 'N' no errors function get_first_validate_error(object_owner out VARCHAR2 ,object_name out VARCHAR2 ,exception_number out INT) return VARCHAR2; -- get next record in error -- param object_owner owner of the object -- param object_name name of the object -- param exception_number number of the error -- return varchar2 'Y' if record returned, 'N' no more records function get_next_validate_error(object_owner out VARCHAR2 ,object_name out VARCHAR2 ,exception_number out INT) return VARCHAR2; -- Remove redundant data from the olap repository either for -- a named user or by default for the whole database. -- -- Redundant data in CWM catalog includes -- dimensions dropped from catalog -- levels dropped from dimension in catalog -- hierarchies dropped from dimension in catalog -- level attributes dropped from dimension in catalog -- arguments referencing columns which have been dropped -- -- Note this does not tidy up dimension attributes, these are managed -- by the api, and under user control. The views provide a consistent -- view of the dimension attribute / level attribute relationship. -- -- param user_name the name of the user to collect (optional) procedure collect_garbage( user_name in varchar2 := null); -- Return details of the last object to cause an exception. -- param object_type the type of database object in error -- param owner the owner of the object in error -- param object_name the name of the object in error -- param secondary_name the optional secondary object name in error -- param tertiary_name the optional tertiary object name in error procedure get_object_in_error( object_type out varchar2, owner out varchar2, object_name out varchar2, secondary_name out VARCHAR2, tertiary_name OUT varchar2 ); -- Dump error details to to pl/sql server output buffer procedure dump_error; -- Internal procedure to set the object to be in error -- param object_type the type of database object in error -- param description internal descriptive text of error -- param owner the owner of the object in error -- param object_name the name of the object in error -- param secondary_name the optional secondary object name in error -- param tertiary_name the optional tertiary object name in error procedure set_object_in_error( object_type varchar2, description varchar2, owner varchar2, object_name varchar2, secondary_name varchar2 := NULL, tertiary_name VARCHAR2 := null ); -- Create a function usage against a function -- param function_name the name of the function -- return number the id of the function usage -- -- raise function_not_found if function does not exist function create_function_usage( function_name in varchar2) return number; -- Delete a function usage -- param function_name the name of the function -- return number the id of the function usage -- -- raise function_usage_not_found if function does not exist procedure drop_function_usage( function_use_id in number); -- Add an argument (column) to a parameter and function usage -- param function_use_id id of function use -- param parameter_name name of function parameter -- param object_type type of argument mapped (COLUMN) -- param object_owner table owner -- param object_name table name -- param secondary_name column name -- -- raise function_not found if function does not exist -- raise column_not_found if column does not exist -- raise invalid_object_type only COLUMN supported procedure add_argument( function_use_id in number, parameter_name in varchar2, object_type in varchar2, object_owner in varchar2, object_name in varchar2, secondary_name in varchar2); -- Set an argument (column) to a parameter and function usage -- param function_use_id id of function use -- param parameter_name name of function parameter -- param object_type type of argument mapped (COLUMN) -- param object_owner table owner -- param object_name table name -- param secondary_name column name -- -- raise function_not found if function does not exist -- raise column_not_found if column does not exist -- raise invalid_object_type only COLUMN supported procedure set_argument( function_use_id in number, parameter_name in varchar2, object_type in varchar2, object_owner in varchar2, object_name in varchar2, secondary_name in varchar2); -- Generate a script of API calls to recreate the contents of the repository. -- -- param script handle to database clob containing script -- raise export_not_allowed if user is not an olap_dba procedure export_all(script out nocopy clob); -- Generate a script of API calls to recreate the contents of a catalog -- -- param catalog_id the id of the catalog to export -- param script handle to database clob containing script -- raise export_not_allowed if user is not an olap_dba procedure export_catalog(catalog_id number, script out nocopy clob); -- Remove _ALL_ metadata from olap catalog. -- -- raise no_access_privileges if user is not an olap_dba procedure empty_all_metadata; -- Return last error description, diagnostics. function get_last_error_description return varchar2; -- Return indication whether connected user is an OLAP DBA. -- -- return varchar2 'Y' if username is an OLAP DBA,'N' otherwise function is_olap_dba return varchar2; -- return Y|N whether specified user is an OLAP DBA -- -- param user_name name of user to check for -- return varchar2 'Y' if user_name is an OLAP DBA,'N' otherwise -- raise no_access_privileges if connected user is not an olap_dba -- raise user_not_found if username not a valid oracle user name function is_olap_dba(username varchar2) return varchar2; end; /