Rem cwm2utl.pls Rem Rem Copyright (c) 1900, 2004, Oracle Corporation. All rights reserved. Rem Rem NAME Rem cwm2urtl.pls - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem awesley 04/15/04 - remove Check_Parameter_Character_Set, i can not find a way to make this procedure Rem work correctly when the national character set is not ASCII Rem awesley 03/09/04 - rework get access rights, add check parameter, add debug on/off Rem awesley 01/15/04 - add Get_Object_ID to get id and resolve synonym for table owner.name Rem awesley 01/08/04 - add get access rights Rem awesley 11/24/03 - add check parameter length Rem awesley 09/22/03 - 92041 to main Rem dbardwel 09/15/03 - Managing DBMS_OUTPUT Buffer routines Rem awesley 08/06/03 - 92031 to 92041 Rem awesley 06/30/03 - add Drop_Val_FactDimHierTplsDtl Rem awesley 05/20/03 - remove lock table procedures Rem awesley 05/16/03 - correct get_class_entry_irid function Rem awesley 05/13/03 - Rem awesley 04/21/03 - add lock table procedures Rem awesley 03/31/03 - Rem awesley 11/06/01 - awesley_txn100587 Rem awesley 09/11/01 - Created Rem create or replace package cwm2_olap_utility as -- SEE Debug_On/OFF DEBUG_OFF constant boolean := false; DEBUG_ON constant boolean := true; -- debug switches could be defined in an array but that will serverly affect runtime -- 1 2 3 --123456789012345678901234567890 boolean := DEBUG_OFF DEBUG_CWM1VALM_TRACE boolean := DEBUG_OFF; DEBUG_CWM2VALM_TRACE boolean := DEBUG_OFF; DEBUG_CWM2VALU_SELECT boolean := DEBUG_OFF; DEBUG_CWM2EXP_TRACE boolean := DEBUG_OFF; -- SEE function Get_Access_Rights ACCESS_RIGHTS_NONE constant varchar2(30) := 'NONE'; ACCESS_RIGHTS_PRIVILEGE constant varchar2(30) := 'PRIVILEGE'; ACCESS_RIGHTS_OWNER constant varchar2(30) := 'OWNER'; ACCESS_RIGHTS_DBA constant varchar2(30) := 'DBA'; --FactDimHierTplsDtl type t_DH_Record is record (v_Dimension number ,v_Dimension_IRID number ,v_Dimension_Name varchar2(30) ,v_Hierarchy number ,v_Hierarchy_IRID number ,v_Hierarchy_Name varchar2(30) ); type t_DH_Table is table of t_DH_Record; v_DH t_DH_Table := t_DH_Table(); --Object ID procedure Get_Object_ID(p_Source_Owner IN varchar2 ,p_Source_Name IN varchar2 ,p_Source_Id OUT number ,p_Source_Type OUT number ,p_Object_Owner OUT varchar2 ,p_Object_Name OUT varchar2 ,p_Object_Id OUT number ,p_Object_Type OUT number ); --Access rights function Get_Access_Rights(p_Model varchar2 -- 'CWM1' or 'CWM2' ,p_Entity_Type varchar2 -- 'DIMENSION' or 'CUBE' ,p_Entity_Owner varchar2 ,p_Entity_Name varchar2 ,p_Entity_IRID number ) return varchar2; procedure Drop_Val_FactDimHierTplsDtl(p_Cube IN OLAPSYS.CwM2$Cube%rowtype ,p_Entity_Name IN OUT OLAPSYS.CwM2$OLAPValidateTable%rowtype ,p_Count IN OUT number ,p_Invalid IN OUT number ,p_Comment IN OUT varchar2 ,p_Command IN varchar2 default 'VALIDATE' ,p_Dimension_IRID IN number default null ,p_Dimension_Number IN number default 1 ,p_DH IN t_DH_Table default null ,p_Select IN varchar2 default null ,p_From IN varchar2 default null ,p_Where IN varchar2 default null ,p_Message IN varchar2 default null ); --Table function Get_Table_Name(p_Table_ID number) return varchar2; --User function Get_User_Name return varchar2; --Measure function Get_Measure_Name(p_Measure_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Measure_IRID(p_Cube_IRID number ,p_Measure_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Measure(p_Measure_IRID number); procedure Drop_Measure_C_IRID(p_Cube_IRID number); --HierLevelRel function Get_HierLevelRel_C_Name(p_HierLevelRel_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_HierLevelRel_P_Name(p_HierLevelRel_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_HierLevelRel_IRID_C_IRID(p_Dimension_IRID number ,p_Hierarchy_IRID number ,p_Child_Level_IRID number ,p_Log_Exception boolean default true) return number; function Get_HierLevelRel_IRID_P_IRID(p_Dimension_IRID number ,p_Hierarchy_IRID number ,p_Parent_Level_IRID number ,p_Log_Exception boolean default true) return number; procedure Drop_HierLevelRel(p_HierLevelRel_IRID number); procedure Drop_HierLevelRel_D_IRID(p_Dimension_IRID number); procedure Drop_HierLevelRel_H_IRID(p_Hierarchy_IRID number); procedure Drop_HierLevelRel_L_IRID(p_Level_IRID number); --LevelAttribute function Get_LevelAttribute_Name(p_LevelAttribute_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_LevelAttribute_IRID(p_Dimension_IRID number ,P_DimensionAttribute_IRID number ,P_Level_IRID number ,p_LevelAttribute_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_LevelAttribute(p_LevelAttribute_IRID number); procedure Drop_LevelAttribute_D_IRID(p_Dimension_IRID number); procedure Drop_LevelAttribute_L_IRID(p_Level_IRID number); procedure Drop_LevelAttribute_DA_IRID(p_DimensionAttribute_IRID number); --Level function Get_Level_Name(p_Level_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Level_IRID(p_Dimension_IRID number ,p_Level_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Level(p_Level_IRID number); procedure Drop_Level_D_IRID(p_Dimension_IRID number); --Hierarchy function Get_Hierarchy_Name(p_Hierarchy_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Hierarchy_IRID(p_Dimension_IRID number ,p_Hierarchy_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Hierarchy(p_Hierarchy_IRID number); procedure Drop_Hierarchy_D_IRID(p_Dimension_IRID number); --CubeDimensionUse function Get_CubeDimensionUse_IRID(p_Cube_IRID number ,p_Dimension_IRID number ,p_Log_Exception boolean default true) return number; procedure Drop_CubeDimensionUse(p_CubeDimensionUse_IRID number); procedure Drop_CubeDimensionUse_C_IRID(p_Cube_IRID number); procedure Drop_CubeDimensionUse_D_IRID(p_Dimension_IRID number); --DimensionAttribute function Get_DimensionAttribute_Name(p_DimensionAttribute_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_DimensionAttribute_IRID(p_Dimension_IRID number ,p_DimensionAttribute_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_DimensionAttribute(p_DimensionAttribute_IRID number); procedure Drop_DimensionAttr_D_IRID(p_Dimension_IRID number); --Dimension function Get_Dimension_Name(p_Dimension_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Dimension_IRID(p_Dimension_Owner varchar2 ,p_Dimension_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Dimension(p_Dimension_IRID number); --Cube function Get_Cube_Name(p_Cube_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Cube_IRID(p_Cube_Owner varchar2 ,p_Cube_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Cube(p_Cube_IRID number); --Invalid procedure Set_Cube_Invalid(p_Cube_IRID number); procedure Set_Cube_Invalid_D_IRID(p_Dimension_IRID number); procedure Set_Cube_Invalid_M_IRID(p_Measure_IRID number); procedure Set_Dimension_Invalid(p_Dimension_IRID number); procedure Set_Dimension_Invalid_H_IRID(p_Hierarchy_IRID number); procedure Set_Dimension_Invalid_L_IRID(p_Level_IRID number); --Class Entry i.e. Classification Entry -- get class entry irid given element irid and class irid function Get_Class_Entry_IRID_EC_IRID(p_Element_IRID number ,p_Class_IRID number ,p_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; -- get class entry irid given element irid function Get_Class_Entry_IRID_E_IRID(p_Element_IRID number ,p_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Class_Entry_Element(p_IRID number ,p_Name varchar2); procedure Drop_Class_Entry_Class(p_IRID number ,p_Name varchar2); procedure Drop_Class_Entry(p_IRID number ,p_Name varchar2); --Class Type i.e. Classification Type function Get_Class_Type_IRID(p_Log_Exception boolean default true ,p_For_Update boolean default false) return number; --Class i.e. Classification function Get_Class_Name(p_Class_IRID number ,p_Log_Exception boolean default true) return varchar2; function Get_Class_IRID(p_Class_Name varchar2 ,p_Log_Exception boolean default true ,p_For_Update boolean default false ,p_Wait_For_Lock boolean default false) return number; procedure Drop_Class(p_Class_IRID number); --Project function Get_Project_IRID(p_Log_Exception boolean default true ,p_For_Update boolean default false) return number; -- DBMS_OUTPUT mgmt procedures procedure clear_dbmsoutput_buffer; procedure dump_dbmsoutput_buffer(p_directory_alias varchar2, p_file_name varchar2); -- check parameter max length procedure Check_Parameter_Max_Length(p_Parameter_Name varchar2 ,p_Parameter_Value varchar2 ,p_Parameter_Max_Length number ,p_Wildcard_Allowed varchar2); -- check parameter of type name function Check_Name_Type_Parameter(p_Parameter_Name varchar2 ,p_Parameter_Value varchar2 ,p_Parameter_Wildcard_Allowed varchar2 default 'NO' ) return varchar2; -- turn off debug switch (production mode). if a name is not provided all switches are set off. procedure Set_Debug_Off(p_Switch_Name varchar2 default null); -- turn on debug switch (debuging mode). if a name is not provided all switches are set on. procedure Set_Debug_On(p_Switch_Name varchar2 default null); end cwm2_olap_utility; / show errors;