1 package cwm$utility as
2
3 -- identifier for entity type values
4 CUBE_TYPE constant varchar2(30) := 'CUBE';
5 MEASURE_TYPE constant varchar2(30) := 'MEASURE';
6 DIMENSION_TYPE constant varchar2(30) := 'DIMENSION';
7 HIERARCHY_TYPE constant varchar2(30) := 'HIERARCHY';
8 LEVEL_TYPE constant varchar2(30) := 'LEVEL';
9 DIMENSION_ATTRIBUTE_TYPE constant varchar2(30) := 'DIMENSION ATTRIBUTE';
10 LEVEL_ATTRIBUTE_TYPE constant varchar2(30) := 'LEVEL ATTRIBUTE';
11 TABLE_TYPE constant varchar2(30) := 'TABLE';
12 COLUMN_TYPE constant varchar2(30) := 'COLUMN';
13 FOREIGN_KEY_TYPE constant varchar2(30) := 'FOREIGN KEY';
14 FUNCTION_TYPE constant varchar2(30) := 'FUNCTION';
15 PARAMETER_TYPE constant varchar2(30) := 'PARAMETER';
16 CATALOG_TYPE constant varchar2(30) := 'CATALOG';
17 DESCRIPTOR_TYPE constant varchar2(30) := 'DESCRIPTOR';
18 INSTANCE_TYPE CONSTANT VARCHAR2(30) := 'INSTANCE';
19
20 CUBE_NO_DIMENSIONS constant INT := 1;
21 -- cube must have at least 1 measure
22 CUBE_NO_MEASURES constant INT := 2;
23 -- cube contains an invalid dimension
24 CUBE_INVALID_DIMENSION constant INT := 3;
25 -- cube contains a measure that is not mapped to a valid column
26 CUBE_INVALID_MEASURE_MAPPING constant INT := 4;
27 -- fact and dimension tables must be mapped by a valid foreign key
28 CUBE_INVALID_FACT_DIM_MAPPING constant INT := 5;
29 -- cube has not been mapped to a fact table
30 CUBE_NO_FACT constant INT := 6;
31 -- the cube is mapped to a fact table that has been deleted
32 CUBE_INVALID_FACT constant INT := 7;
33 -- the cube is mapped to a fact table using an invalid constraint(foreign key)
34 CUBE_INVALID_CONSTRAINT constant INT := 8;
35
36 -- dimension must have a name
37 DIMENSION_NO_NAME constant INT := 9;
38 -- invalid dimension
39 DIMENSION_INVALID constant INT := 10;
40 -- dimension must have at least 1 level
41 DIMENSION_NEEDS_LEVELS constant INT := 11;
42
43 -- Return the internal version identifier string
44 -- return varchar2 the version identifier
45 function get_version return varchar2;
46
47 -- get the identifier for the named function
48 -- param function_name name of the function
49 -- return number the id of the function
50 --
51 -- raise function_not_found if function does not exist
52 function get_function_id(function_name varchar2) return number;
53
54 -- validate dimension, and initialize details for client to investigate
55 -- param dim_owner owner of the dimension
56 -- param dim name of the dimension
57 -- param output_on summarize results on output stream
58 --
59 -- raise dimension_not_found if dimension does not exist
60 procedure validate_dimension(dim_owner in varchar2
61 ,dim_name in varchar2
62 ,output_on in boolean := false);
63
64 -- validate cube, and initialize details for client to investigate
65 -- param cube_owner owner of the cube
66 -- param cube_name name of the cube
67 -- param output_on summarize results on output stream
68 --
69 -- raise cube_not_found if cube does not exist
70 procedure validate_cube(cube_owner in varchar2
71 ,cube_name in varchar2
72 ,output_on in boolean := false);
73 -- get first record in error
74 -- param object_owner owner of the object
75 -- param object_name name of the object
76 -- param exception_number number of the error
77 -- return varchar2 'Y' if record returned, 'N' no errors
78 function get_first_validate_error(object_owner out VARCHAR2
79 ,object_name out VARCHAR2
80 ,exception_number out INT) return VARCHAR2;
81 -- get next record in error
82 -- param object_owner owner of the object
83 -- param object_name name of the object
84 -- param exception_number number of the error
85 -- return varchar2 'Y' if record returned, 'N' no more records
86 function get_next_validate_error(object_owner out VARCHAR2
87 ,object_name out VARCHAR2
88 ,exception_number out INT) return VARCHAR2;
89
90
91 -- Remove redundant data from the olap repository either for
92 -- a named user or by default for the whole database.
93 --
94 -- Redundant data in CWM catalog includes
95 -- dimensions dropped from catalog
96 -- levels dropped from dimension in catalog
97 -- hierarchies dropped from dimension in catalog
98 -- level attributes dropped from dimension in catalog
99 -- arguments referencing columns which have been dropped
100 --
101 -- Note this does not tidy up dimension attributes, these are managed
102 -- by the api, and under user control. The views provide a consistent
103 -- view of the dimension attribute / level attribute relationship.
104 --
105 -- param user_name the name of the user to collect (optional)
106 procedure collect_garbage(
107 user_name in varchar2 := null);
108
109 -- Return details of the last object to cause an exception.
110 -- param object_type the type of database object in error
111 -- param owner the owner of the object in error
112 -- param object_name the name of the object in error
113 -- param secondary_name the optional secondary object name in error
114 -- param tertiary_name the optional tertiary object name in error
115 procedure get_object_in_error(
116 object_type out varchar2,
117 owner out varchar2,
118 object_name out varchar2,
119 secondary_name out VARCHAR2,
120 tertiary_name OUT varchar2
121 );
122
123 -- Dump error details to to pl/sql server output buffer
124 procedure dump_error;
125
126 -- Internal procedure to set the object to be in error
127 -- param object_type the type of database object in error
128 -- param description internal descriptive text of error
129 -- param owner the owner of the object in error
130 -- param object_name the name of the object in error
131 -- param secondary_name the optional secondary object name in error
132 -- param tertiary_name the optional tertiary object name in error
133 procedure set_object_in_error(
134 object_type varchar2,
135 description varchar2,
136 owner varchar2,
137 object_name varchar2,
138 secondary_name varchar2 := NULL,
139 tertiary_name VARCHAR2 := null
140 );
141
142 -- Create a function usage against a function
143 -- param function_name the name of the function
144 -- return number the id of the function usage
145 --
146 -- raise function_not_found if function does not exist
147 function create_function_usage(
148 function_name in varchar2) return number;
149
150 -- Delete a function usage
151 -- param function_name the name of the function
152 -- return number the id of the function usage
153 --
154 -- raise function_usage_not_found if function does not exist
155 procedure drop_function_usage(
156 function_use_id in number);
157
158
159 -- Add an argument (column) to a parameter and function usage
160 -- param function_use_id id of function use
164 -- param object_name table name
161 -- param parameter_name name of function parameter
162 -- param object_type type of argument mapped (COLUMN)
163 -- param object_owner table owner
165 -- param secondary_name column name
166 --
167 -- raise function_not found if function does not exist
168 -- raise column_not_found if column does not exist
169 -- raise invalid_object_type only COLUMN supported
170 procedure add_argument(
171 function_use_id in number,
172 parameter_name in varchar2,
173 object_type in varchar2,
174 object_owner in varchar2,
175 object_name in varchar2,
176 secondary_name in varchar2);
177
178 -- Set an argument (column) to a parameter and function usage
179 -- param function_use_id id of function use
180 -- param parameter_name name of function parameter
181 -- param object_type type of argument mapped (COLUMN)
182 -- param object_owner table owner
183 -- param object_name table name
184 -- param secondary_name column name
185 --
186 -- raise function_not found if function does not exist
187 -- raise column_not_found if column does not exist
188 -- raise invalid_object_type only COLUMN supported
189 procedure set_argument(
190 function_use_id in number,
191 parameter_name in varchar2,
192 object_type in varchar2,
193 object_owner in varchar2,
194 object_name in varchar2,
195 secondary_name in varchar2);
196
197 -- Generate a script of API calls to recreate the contents of the repository.
198 --
199 -- param script handle to database clob containing script
200 -- raise export_not_allowed if user is not an olap_dba
201 procedure export_all(script out nocopy clob);
202
203 -- Generate a script of API calls to recreate the contents of a catalog
204 --
205 -- param catalog_id the id of the catalog to export
206 -- param script handle to database clob containing script
207 -- raise export_not_allowed if user is not an olap_dba
208 procedure export_catalog(catalog_id number, script out nocopy clob);
209
210 -- Remove _ALL_ metadata from olap catalog.
211 --
212 -- raise no_access_privileges if user is not an olap_dba
213 procedure empty_all_metadata;
214
215 -- Return last error description, diagnostics.
216 function get_last_error_description return varchar2;
217
218 -- Return indication whether connected user is an OLAP DBA.
219 --
220 -- return varchar2 'Y' if username is an OLAP DBA,'N' otherwise
221 function is_olap_dba return varchar2;
222
223 -- return Y|N whether specified user is an OLAP DBA
224 --
225 -- param user_name name of user to check for
226 -- return varchar2 'Y' if user_name is an OLAP DBA,'N' otherwise
227 -- raise no_access_privileges if connected user is not an olap_dba
228 -- raise user_not_found if username not a valid oracle user name
229 function is_olap_dba(username varchar2) return varchar2;
230
231 end;